Admin Alert: Three Keys to Better ODBC Library List Management

July 20, 2005 Joe Hertvik

For OS/400 data access, ODBC can be incredibly picky, especially when specifying library lists for ODBC connections. In configuring and reconfiguring many ODBC Data Source Names (DSNs), I have found that there are three items that must be understood and configured correctly or your desktop application programs may not find the data they need. To that end, let’s look at my three keys to ODBC library list success.

Please note that these items were tested by accessing an i5/OS V5R3 machine with the ODBC driver that comes with iSeries Access for Windows V5R3M0 (iSeries Access). While most of these functions are available in earlier versions of iSeries Access and Client Access Express for Windows ODBC, there are some differences between what’s available in the newer drivers and what is available in earlier versions. I’ll point out where these changes affect our discussion and how to modify your drivers to account for them.

Like all OS/400 jobs, each ODBC connection contains a library list that OS/400 uses to find and modify the objects that your Windows application programs reference. This library list consists of four sections:

The System Library List, which should only list out IBM-supplied libraries that are part of the base OS/400 and i5/OS operating system. You can find your system library list entries by looking at the System part of the library list system value (QSYSLIBL)
The Current Library or DEFAULT COLLECTION. For many jobs, the Current Library contains the name of the OS/400 library that is used to create native OS/400 or i5 objects. For ODBC jobs that use a default naming convention of *SQL, objects are created and modified in the library name specified in the DEFAULT COLLECTION rather than in the current library.
The User Library List, which contains the libraries that your application programs use. This list can be set in several places for a job, including the User part of the library list system value (QUSRLIBL), inside job descriptions, and inside your ODBC DSN configuration.
The Product Library, has traditionally been added to a library list by the database host server, in order to specify where database host server jobs reside. Starting in OS/400 V5R1, IBM added operating system changes that made this part of the library list irrelevant.

For ODBC job library lists, the values you enter into your ODBC driver affect the Current Library or DEFAULT COLLECTION and the User Library List portions of the list. Here are three steps you can take to insure these values are set correctly for your connection.

1. Learn to love and understand the DEFAULT COLLECTION: The DEFAULT COLLECTION is also known as the implicit qualifier. This OS/400 library name is used in SQL ODBC statements to locate tables that do not contain a unique library qualification. As such, all unqualified objects that your application may use–with the IBM-specified exceptions of procedures, functions, and types–must reside in the DEFAULT COLLECTION. This DEFAULT COLLECTION requirement holds true regardless of whether your ODBC DSN uses the SQL naming convention (*SQL) or the System naming convention (*SYS). If no DEFAULT COLLECTION is specified and the naming convention is equal to *SYS, then the complete library list becomes the implicit qualifier and it will be searched for unqualified names.

The confusing part of the DEFAULT COLLECTION is that you set it differently depending on which version of the iSeries Access for Windows or Client Access Express for Windows ODBC driver you are using.

In the drivers that preceded the Client Access Express V5R1M0 ODBC driver, the DEFAULT COLLECTION is set as part of the Default Libraries setting in your ODBC DSN. In this parameter, you list out all the libraries that your ODBC job should be using for its library list, and each library is separately by a comma. The first library in that list is specified as the SQL DEFAULT COLLECTION, and the entire list specified in the Default Libraries setting replaces the User Library List portion of the job’s library list. If you wanted to specify that this job has no DEFAULT COLLECTION, you would start the list with a comma (,) and ODBC will not specify a DEFAULT COLLECTION setting.

In Client Access Express for Windows V5R1M0, IBM made a change to its ODBC driver configuration; this change separated the DEFAULT COLLECTION parameter from the Default Libraries setting. In V5R1M0 and above, IBM replaced the Default Libraries setting with the following two parameters: SQL Default Library, which contains the DEFAULT COLLECTION name (the implicit qualifier), and Library List, which contains the rest of the user library list.

If you don’t want to specify a library as the implicit qualifier in these later ODBC versions, you can leave the SQL Default Library setting blank. With a blank SQL Default Library, ODBC will use the job library list as the implicit qualifier when you specify the naming convention as *SYS. With a naming convention of *SQL, ODBC will use a run-time authorization as the name of the DEFAULT COLLECTION, and the DEFAULT COLLECTION name will then be set to the name of the user profile specified on the ODBC connection. So if you’re not specifying a DEFAULT COLLECTION, you need to make that your system contains libraries whose names are equal to the user profile names of every user that will being running ODBC.

2. Set your library list like a pro: In the Library list or Default libraries parameter, you specify the names of each library that you want in the User Library list, separating each value by either a comma or a space. For example, if you wanted to replace the User Library list with three libraries named LIB1, LIB2, and LIB3, you could enter one of the following two literal strings into this parameter:

LIB1, LIB2, LIB3

LIB1 LIB2 LIB3

You can also opt to add libraries to the current User Library list, rather than to replace the list entirely. You do this by adding the *USRLIBL parameter to your settings. So if you wanted to add LIB1, LIB2, and LIB3 to the beginning of your job’s user library list, you would enter the following literal string into this setting:

LIB1, LIB2, LIB3, *USRLIBL

If you want to add these libraries to the end of your current User Library list, you would enter the library names like this:

*USRLIBL, LIB1, LIB2, LIB3

3. Proper library name spelling is the key to ODBC happiness: One of the most common problems I see with ODBC connections is when the user opens a connection where he thinks the library list has been overwritten with his configuration, and the library list is actually equal to the default user library list for the signed-on user. In these situations, chances are good that there is a misspelling in one of the libraries designated in the Default libraries, SQL default library, or the Library list settings. By default, ODBC seems to revert back to the user’s default library list when it encounters a problem. Checking and correcting your settings against your library names usually solves this problem.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search