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.

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.

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.

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.

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.

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.)

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 e-mail address is being protected from spam bots, you need JavaScript enabled to view it .