V5R4 Improvements to the i5/OS OLE DB Providers

The code for this article is available for download.

I’ve used the feature rich and reliable AS/400 ODBC driver for quite a while in my applications. When creating new applications, I would often switch to the alternative OLE DB providers to see if performance and features were comparable. However, I would always run into a limitation of some sort with the OLE DB provider (e.g., not being able to use the system naming convention) that would put me back to using ODBC. I’m happy to report as of V5R4 it looks like many of these limitations are gone.

In case you’re not familiar with ActiveX Data Objects and OLE DB, these terms refer to Microsoft technology designed to allow Windows applications to connect to other systems.

IBM offers the OS/400 community three OLE DB providers: IBMDA400, IBMDASQL, and IBMDARLA. As a review, IBMDA400 is a general purpose, all encompassing provider that supports SQL, command and data queue interfaces. IBMDARLA is the record level access provider and is primarily for applications that work with single records instead of sets of records. The IBMDASQL provider was released with iSeries Access V5R3 and is an SQL only provider.

Two long-awaited features have finally arrived in iSeries Access V5R4: the ability to specify the system naming convention and library list in the connection string.

To specify the library list, specify Naming Convention=x in the connection string. The values for x are 0 for the SQL naming convention (the default) and 1 for the system naming convention. The advantage of the system naming convention is that SQL uses the job library list to resolve unqualified object names and hence reduces hard coding of library (or schema) names.

To specify a job library list, specify a comma delimited list of libraries in the following format Library List=lib1,lib2;. (That is a period after the semicolon in that statement, not a part of the statement.) Note that the special value *USRLIBL is allowed to appear in the list, along with other hard coded library names. When *USRLIBL is specified, the libraries from the connecting user profile’s job description will be substituted in the list. Setting the job library list in the connection allows an easy way to allow traditional RPG and CL programs to execute correctly without making the programs themselves set it.

Here are a few more things to note:

• For the new naming convention and library list connection string settings, the host is not required to be running i5/OS V5R4.
• When using the *USRLIBL special value, do not duplicate libraries. For example, if QTEMP is in your *USRLIBL don’t also place QTEMP in the connection string. Duplicating a library name will cause an error to occur.
• Don’t confuse the catalog library list option with the new library list option. The catalog library list option (introduced in V5R1) is only for searching the list of libraries for certain SQL objects. This older option did not set the job’s library list.

A few other notable enhancements that have occurred since V5R3:

• Ability to specify a package name and lib. This is useful for tracking optimized statements by application. After your application runs, simply issue a PRTSQLINF command against the package name to review information about the application’s prepared SQL statements.
• The ability to retrieve the host job name that is servicing the database request. This is useful for debugging problems because you no longer have to hunt for the host job.
• The option to override the library storing the query options table (QAQQINI). This is useful when overrides to specific optimizer behaviors are needed such as waiting longer than 30 seconds for a user-defined function to complete. See a related story, Adjust Default Query Optimizer Settings with QAQQINI, for more info.
• The option to override the sort sequence and sort language id. This option is useful in that it allows, among other things, queries from the provider to be case insensitive. See a related story, Case-Insensitive Sorting and Record Selection with Query/400, Take Two, for more info.

You can also read an IBM document that has a list of all of the OLE DB provider connection string enhancements since V5R1 along with some sample VBA code. Be sure that you spell the connection property names correctly. Unlike the ODBC driver that is gracious with many connection string errors, the OLE DB providers will choke. However, this does have the benefit of verifying that a connection string is accurate.

The downloadable code for this story contains a sample VBA subroutine that uses ActiveX Data Objects with the IBMDASQL provider to establish a connection. As opposed to IBM’s code example, I prefer to specify the new connection properties within the connection string itself because many third party applications that make use of OLE DB providers (such as SQL Server Linked Servers and DTS) only allow the user to override the connection string.

If you haven’t tried them, try out the maturing OLE DB providers. For applications that use ActiveX Data Objects, the OLE DB providers:

• Require one less programmer layer than ODBC and hence should be more efficient
• Are easier to configure (ODBC requires a DSN setup or a somewhat lengthy connection string when the DSN is omitted)

The next time I have a data integration project involving Windows COM objects, I will definitely give the OLE DB providers a try.

Correction: This story has been corrected since it originally ran. It originally said that for the new naming convention and library list connection string settings, the host must also be running i5/OS V5R4. This is not true. The host is not required to be running i5/OS V5R4. IT Jungle regrets the error. [Corrected 05/03/2006]

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

RELATED STORIES

Adjust Default Query Optimizer Settings with QAQQINI

Case-Insensitive Sorting and Record Selection with Query/400, Take Two