The IBM Client Access for Windows 95 client includes an ODBC driver that you can use to connect your favorite ODBC-aware applications to your AS/400 data. There are many configuration options that you can choose to optimize your connection capabilities and performance. This article explains how to create an ODBC connection for your ODBC applications to use. It also shows you what some of the options are on the Client Access driver configuration screen. Armed with this information, you can connect your PC applications to your AS/400 in the fastest manner possible.

Basic ODBC

As you may know, ODBC is an acronym that stands for Open Data Base Connectivity. It is a data access standard created by Microsoft and other vendors to allow application programs to access data from any type of database that supports the standard. It is also IBM’s preferred means of client/server connectivity for the AS/400. Combined support from industry giants such as IBM and Microsoft ensures that ODBC will be a perennial standard for database access, so it’s worth your while to spend a little time gaining an understanding of how it works. Figure 1 shows a diagram of the ODBC architecture. ODBC consists of three layers of programs that function together to bring the data to your application: the driver manager, the ODBC driver, and the data source.

The driver manager is a program that manages the individual ODBC drivers installed on your system. It is also referred to as the ODBC administrator program. It provides the interface between the applications and the drivers, allows for the management of data sources, and handles some of the application programming functionality.

ODBC drivers are the programs that perform the translation between the external data source and the ODBC programming interface. There are specific drivers for many different data sources, including local PC database files and remote databases such as the AS/400. You can tell which ODBC drivers are installed on your system by looking at the list of available drivers in the ODBC administrator program.

The final layer of the ODBC architecture is the data source. This is the layer that you’ll deal with the most often when retrieving data from the AS/400. Data sources are basically named connections to a database. They’re created from ODBC drivers to refer to a specific database or library on the AS/400. ODBC drivers can have many different settings, including default libraries and connection methods. When you create a data source with the ODBC administrator program, these settings are stored with that data source so that you don’t have to enter the configuration information into the driver every time you want to access your data.

The ultimate goal of configuring ODBC is to create a data source that you can use when accessing data from your AS/400. Let’s do that now.

Configuring a Win 95 Client Access Data Source

After you install the Client Access ODBC driver using the Client Access setup program, you need to create a data source using the 32-bit ODBC driver manager. This applet is found in the Client Access folder or in the Windows control panel. Start this program and click the Add button to create a new data source. A list of the ODBC drivers installed on your system will be displayed. Select Client Access ODBC Driver (32-bit) from the list and press OK. This brings up the Client Access ODBC driver configuration screen.

Keeping a Tab on Things

Staying consistent with the Windows 95 user interface, IBM organized the various parameters of the ODBC driver into sections in a tabbed dialog box. These sections are displayed by clicking on the tabs at the top of the configuration dialog box. Let’s take a look at each of these tabs. Under the General tab (Figure 2), there are some parameters that are not optional. One is a name for your data source. The name is used to identify the data source when connecting to your AS/400, so make it something that you can remember. It can be up to 32 characters. You won’t usually have to type the name, so be descriptive. In the System field, you are required to enter the name of the AS/400 to which you want to connect. This must be an AS/400 connection that is defined to Client Access. You can also enter a description for the data source. This description is optional. In the User ID field, you can enter a user profile name which will be used to connect to the AS/400. This parameter is optional. If it is not entered, you will be prompted for it every time you access the AS/400 using this data source.

Figure 3 shows the options under the Server tab. Under this tab, you can set two parameters—the default libraries and the commit mode. The Default libraries parameter controls which libraries are used by the data source. This parameter is very flexible—and very important. When ODBC connections are made, lists of available tables (files) are often created by the client programs. The Default libraries parameter controls what is displayed in those lists. You can specify multiple libraries in this field by separating each entry by comma. The first library named in this list is the default library. This library is important because programs like Microsoft Access and others that aren’t aware of AS/400 libraries do all their work in the default library. For example, if you export a table from Access, it will be placed in the default library. Normally, the libraries in this field replace

any libraries in the user library list. You can tell Client Access ODBC to use the user library list by specifying *USRLIBL as an entry. For example, to have QGPL as the default library and add the users library list, you would enter QGPL, *USRLIBL. You should specify as few libraries as possible here, as searching multiple libraries can adversely affect performance.

The Commit mode parameter determines whether on not commitment control is used during database updates. Commitment control determines when database updates are made, and whether or not they can be undone. Using commitment control can adversely affect performance, so if you don’t need it, you should select the default value of *NONE.

The Format tab (Figure 4) is where you control the format of the data sent back and forth to the AS/400. On this screen, one of the things you can set is the naming convention used by the driver. This will affect how SQL statements are formatted. For most PC applications, you should choose the default of *SQL. If you choose *SYS, the format of your SQL statements will have to be changed to the standard AS/400 naming conventions. For example, with *SQL naming, a particular file in a particular library would be identified with LIBRARY.FILENAME. With the *SYS naming convention, the file would be identified with LIBRARY/FILENAME. Most PC applications won’t generate SQL with this syntax, so you should use the *SQL setting. The other parameters on this screen tell how the ODBC driver should communicate certain types of information to your AS/400. The Decimal separator parameter tells the ODBC driver which character your AS/400 uses as a decimal point. For the United States, a period is the default. For other countries, you may need to select the comma as the decimal separator. The Time and Date Format areas are used to control the date and time formats used. The defaults should work in most cases, but if necessary, use the list boxes to choose an alternate format.

Under the Performance tab (Figure 5), the unique innovations of the IBM driver become apparent. Once again, the default values for the parameters under this tab should suffice for most applications, but understanding the parameters and how they work can help you tune the Client Access driver for your environment.

One of the interesting abilities of the IBM ODBC driver and the AS/400 is the ability to cache SQL statements for reuse. Checking the Enable Dynamic Extended Support checkbox allows the usage of cached dynamic SQL statements that you have already created and use them again. This translates into a speed increase, because the SQL statements can be used without having to be recompiled. When you use dynamic SQL, as is the case with almost all ODBC connections, it is a two step process on the AS/400. First the SQL statements are compiled—that is, the AS/400 looks at the database and determines the best way to retrieve the data. This is sometimes called building an access plan. Then the AS/400 uses the plan to retrieve the data. Creating the plan can take time. Selecting this option allows the AS/400 to reuse plans that have already been created, providing performance benefits.

The Enable Lazy Close support checkbox provides another method for speeding up the ODBC session. SQL statements in an ODBC environment are opened when records are accessed and closed when operations are completed. If the Enable Lazy Close option is selected, the Client Access ODBC driver will not explicitly close a statement until the next database operation is performed. This reduces the amount of data that needs to be transmitted between the client and the AS/400, thereby increasing speed. If this option is turned off, a close will be sent immediately instead of with the next database operation.

The record blocking section controls how the Client Access ODBC driver brings database records back from the AS/400. There are three options—two that enable blocking and one that disables it. Blocking allows multiple records to be transferred in a single

request, thereby reducing the number of back-and-forth data transmissions between the client and the AS/400. If blocking is disabled, records are transmitted to the client one at a time. Two selections control how blocking is performed. FOR FETCH ONLY blocks records only if the records are retrieved by an SQL SELECT statement explicitly containing the clause “FOR FETCH ONLY.” Most client programs that generate SQL statements for ODBC probably will not use that clause, so there is another option. The For update of parameter will block all record retrievals except for records returned by an SQL SELECT statement containing “FOR UPDATE OF.” This means that normal SQL requests, such as those generated by Microsoft Access and other programs, will be blocked. This is the default and provides the fastest operation.

The last parameter under the Performance tab is the OS/400 Library view parameter. It controls which tables (the ODBC term for AS/400 data files) are displayed when a request to view all tables on the system. If this parameter is set to Default Library List, only tables from your library list will be retrieved. If it is set to All Libraries on the System, all tables from all libraries will be retrieved. This will take a long time, so you should leave this setting at the default of Default Library List. IBM placed options that didn’t fit in any of the other tabs under the Other tab (Figure 6). There are three main setting under this tab. The Translation option allows you to choose whether or not binary data will be translated from EBCDIC to ASCII. Binary data is identified on the AS/400 with a CCSID (coded character set identifier) of 65535. The default value of this parameter is to not translate binary data, which will leave it in the same format as it is stored on the AS/400. If you elect to translate the data, it will go through an EBCDIC-to-ASCII translation. This could alter the format of the binary data, leaving it unusable. It won’t change the actual data on the AS/400, however. It will just change the way it is delivered to the ODBC data source. Under most circumstances, you’ll want to leave the translation set to the default.

The Object Description Type setting controls which object description is used to describe the objects. Setting it to OS/400 Object Description will use the object description that most AS/400 people should be used to. If you set it to SQL Object Comment, the OS/400 SQL comment will be used. In my experience, most PC programs don’t display the object descriptions when connecting to an ODBC data source, so setting this parameter may not have any effect.

The Scrollable Cursor option could have possibly been placed under the Performance tab. The default setting, Scrollable Unless Rowset Size is 1, provides a significant performance boost over the other setting of Always Scrollable. Making a cursor, which is basically a placeholder in a set of records returned from the AS/400, scrollable adds some overhead, because the system must keep track of where the cursor is. Nonscrollable cursors are usually quicker, so using them whenever possible has performance advantages. If the rowset size is one, meaning there is only one record in the rowset, it wouldn’t make sense to scroll, so it’s not necessary to create a scrollable cursor.

Bits: 16 vs. 32

Windows 95 has the capability to run both 16- and 32-bit applications. There are also 16- and 32-bit ODBC drivers, as well as 16- and 32-bit ODBC driver managers. This can cause confusion. At first, it was not possible to use a 32-bit ODBC driver with a 16-bit application. Fortunately, with the ODBC driver manager that ships with Client Access for Windows 95, it is now possible. With the shipping versions, the Client Access setup program should automatically update your system’s path statement to point to this ODBC driver manager. With the beta versions available on the Web, the path had to be changed

manually. Either way, you should have an entry in your path that points to the shared directory in the Client Access directory. For example, on my system, I use the following path entry:

C:PROGRA~1IBMCLIENT~1SHARED

This entry points to the 32-bit ODBC driver manager shipped with Client Access, which supports using 32-bit ODBC drivers in 16-bit applications. Unfortunately, using 16- bit ODBC drivers with 32-bit applications is not supported.

Create A DSN And You’re Done...

That’s it—for the ODBC configuration that is. Press the OK button and you’ve just created a data source for your AS/400. Now you can use this new data source with your favorite ODBC-enabled applications to retrieve data from your AS/400. There are many parameters for the IBM ODBC driver. Luckily for us, IBM created default settings that provide the best performance in most environments.

Figure 1: ODBC Architecture

Figure 2: The General Tab Figure 3: The Server Tab

Figure 4: The Format Tab Figure 5: The Performance Tab

Figure 6: The Other Tab

Brian Singleton is former editor of Midrange Computing. He has worked in the IBM midrange arena for many years, performing every job from backup operator to programmer to systems analyst to technology analyst for major corporations and IBM Business Partners. He also has an extensive background in the PC world. Brian also developed a line of bestselling Midrange Computing training videos, authored the bestselling i5/OS and Microsoft Office Integration Handbook, and has spoken at many popular seminars and conferences.

---

MC Press books written by Brian Singleton available now on the MC Press Bookstore.

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

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?