17
Sun, Nov
2 New Articles
  1. You are here:  
  2. Home
  3. Programming
  4. Programming - Other
  5. Microsoft
  6. Microsoft Computing: Accessing iSeries Data with ODBC

Microsoft Computing: Accessing iSeries Data with ODBC

Microsoft
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

This month, we take up the Open Database Connectivity (ODBC) technology, the patriarch of Windows data communications.

ODBC has served well as the established means of bringing data from a given type of database into an application of another type. The database and the application do not have knowledge of the other's data representations, nor do they care. ODBC, standing in between the two, will perform all functions required to pass and translate the data in a manner transparent to either.

ODBC is an API developed in the 1990s by Microsoft that implements the specifications for data portability as originally set forth by the SQL Access Group, an organization of major database vendors. Since then, ODBC has become the most popular means of accessing iSeries data with a Windows application.

ODBC is not an application like iSeries Access file transfer. On its own, ODBC doesn't do anything. Rather, it's an API that another application uses to get at iSeries data. ODBC merely acts as the data conduit.

ODBC support on a Windows PC consists of two parts: the Microsoft framework that provides the underlying mechanism for services and the ODBC driver, the database-specific agent responsible for translating requests into native form. The Microsoft framework works with a variety of ODBC drivers, among them the iSeries driver from IBM.





http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210500.png

Figure 1: This illustration represents the ODBC API architecture.

ODBC Requirements

Access to iSeries data through ODBC requires several things:

  • The Microsoft ODBC driver manager--This is the Microsoft software that manages the dynamic link to the ODBC driver. This software is installed as part of Windows.
  • The ODBC driver for iSeries Access--This is normally installed with iSeries Access or Client Access.
  • An ODBC data source--A data source is really a configuration object you create to hold some key information about the database you want to connect to.
  • An application to set up the connection and access the data--This can be a Windows application like MS Access or an application that you write yourself.

The iSeries ODBC driver from IBM is supplied as a free component of iSeries Access. Other ODBC drivers that are designed to interface with other types of databases will likely be installed on your PC as well. Each of these drivers differs slightly in capability, performance, and configuration, but all offer remote database access for ODBC-compliant application programs like Office.

You can tell which ODBC drivers you have installed on your system by looking at the list of available drivers in the ODBC administrator program. To do this, start the Windows ODBC configuration program (Settings > Control Panel > Administrative Tools > Data Sources (ODBC) in Windows XP.) Then click the Drivers tab. If your iSeries Access or Client Access driver is not listed, install the Data Access portion of iSeries Access from your iSeries Access/Client Access media.

An ODBC system requires you to specify a configuration record called a data source. This is the configuration record that you deal with most often when retrieving data from the iSeries. Data sources are named definitions of how ODBC will connect to a database. When they are named, they are given the acronym DSN (data source name). DSNs are created from ODBC drivers to refer to a specific database or library on the iSeries. ODBC drivers can have many different settings, such as default libraries and connection methods. When you create a DSN with the ODBC administrator program, these settings are stored with that data source. Therefore, the next time you want to access your data, you don't have to again enter the configuration information for the driver.

Under ODBC, there are three different types of DSNs in the 32-bit ODBC environment, and each type allows different access:

  • User DSNs--User DSNs are available only to the current user and others who have authorized access to that user's objects.
  • System DSNs--System DSNs are available to the operating system and anyone who is using the system.
  • File DSNs--File DSNs are available to anyone using the system with the proper drivers installed. File DSNs also tend to work better with versions of Office prior to Office 2000.

Creating a DSN

Once the driver is installed, create a data source for that driver by using the ODBC administration program, following these steps:

1. Start the ODBC administration program (Settings > Control Panel > Administrative Tools > Data Sources (ODBC)).

2. Click the User DSN tab on the ODBC Data Source screen. The list box on the left side of the screen contains the names of the user DSNs currently configured on your system.

3. Click the Add button to bring up a screen like that shown in Figure 2. On this screen, you select the ODBC driver you want this DSN to use. If you do not see the iSeries Access or Client Access ODBC driver, it has not been installed on your system.


http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210501.png

Figure 2: Select the driver for the DSN.

4. Select iSeries Access or Client Access ODBC Driver, and click the Finish button to bring up the screen in Figure 3, where you are asked to enter a name for your data source.

http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210502.png

Figure 3: Name the new data source.

5. Enter a name for your data source. Specify the IP address or host name of your iSeries or AS/400 and click the Connection Options button. A dialog box will allow you to set your options for how your user ID and password are to be associated with new connection requests. In the user ID field, enter the name of the user ID you want to use to log on with this DSN. The user ID can be any valid iSeries logon. If you leave it blank, you will be prompted for an ID and password at runtime, if one is not already cached. Keep in mind that the IBM iSeries Access ODBC driver uses standard iSeries authorities, as defined by this user profile.

6. Click the OK button to return, and click the Server tab (Figure 4.) This panel is used to specify the names of the iSeries libraries that will be used by this DSN. The libraries are searched and displayed like an iSeries library list.


http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210503.png

Figure 4: This DSN will work with these iSeries Access libraries.
 

The library names can be separated by commas or spaces. You can either replace the library list entirely or add libraries to your library list. To replace the list, specify a list of library names. To add to the existing user library list, add *usrlibl to the list of libraries. All libraries listed before *usrlibl will be added to the front of the user library list. All libraries listed after *usrlibl will be added to the end of the user library list.

Keep in mind that you can configure as many DSNs as you need for each driver. Therefore, if you want to access separate libraries at different times from your system, you can have a DSN specifically customized for each setup. Make sure you are authorized to all libraries in the list.

7. Click OK. The rest of the settings are usually correct at their default values.

If you need to create a system DSN, the process is much the same. Instead of clicking on the File DSN tab, click the System DSN tab instead. The screens for the IBM iSeries Access ODBC driver data-source configuration are the same as those just discussed, and the same information and parameters apply.

Using an ODBC DSN in an Application

OK, the next step is to take your ODBC configuration for a test drive to confirm your setup. Perhaps the easiest way is to use Microsoft Access to get at your iSeries data through your new DSN. Start Access and open a new blank database. From the File menu, select Get External Data and then Import.... From the ensuing Import dialog box, specify that you'll be using an ODBC data source to import the data, as shown in Figure 5.

http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210504.png

Figure 5: Specify that an ODBC database will be used to import iSeries data.
Select ODBC Databases and click Import. If all is well, you'll be greeted with a catalog of the files that reside in the libraries you specified in your DSN. Select a small file and click OK. After a bit of churning, Access will show a new table (file) in your Access database. Select the new table and click Open. Since this is an imported file, the data has already been transferred and should be displayed immediately (Figure 6.)

http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBCV3--02210505.png

Figure 6: Your iSeries file data is imported into an Access database through ODBC.

Note that iSeries data may be either copied to your Access database (imported) or linked. If an iSeries table is linked, the data remains on the iSeries, and changes made to the Access table will be reflected in your live iSeries.

Another good application to drive your ODBC configuration is MS Query. Please refer to my December 2004 article for information about using MS Query with iSeries data.

The Future of ODBC

The future of ODBC technology rests with Microsoft. ODBC is a Microsoft technology, and although Microsoft has extended the capabilities of the platform, it does not enjoy the popular support and acceptance of truly open systems. Nevertheless, ODBC is hitched to the Microsoft wagon and enjoys the benefit of a natural monopoly. ODBC will probably be with us for some time to come as the de facto standard unless Microsoft itself determines a reason to bring about its demise.

Next month, I'll present the other side of the ODBC coin: security considerations for iSeries administrators using ODBC services.

Chris Peters has 26 years of experience in the IBM midrange and PC platforms. Chris is president of Evergreen Interactive Systems, a software development firm and creators of the iSeries Report Downloader. Chris is the author of The OS/400 and Microsoft Office 2000 Integration Handbook, The AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400 (MC Press). He is also a nationally recognized seminar instructor. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

Chris Peters has 32 years of experience with IBM midrange and PC platforms. Chris is president of Evergreen Interactive Systems, a software development firm and creators of the iSeries Report Downloader. Chris is the author of i5/OS and Microsoft Office Integration Handbook, AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400. He is also a nationally recognized seminar instructor and a lecturer in the Computer Science department at Eastern Washington University. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

MC Press books written by Chris Peters available now on the MC Press Bookstore.

i5/OS and Microsoft Office Integration Handbook i5/OS and Microsoft Office Integration Handbook
Harness the power of Office while exploiting the i5/iSeries database.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: