• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • V5R4 Improvements to the i5/OS OLE DB Providers

    May 3, 2006 Michael Sansoterra

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Krengeltech

    When it comes to consuming web APIs on your IBM i, your options often boil down to one of two things:

    First, you end up having to rely on a variety of open source and non-RPG solutions. This adds developer complexity, taking away time that could have been better spent invested in other projects. Of course, open source software is free, but generally comes at the cost of no professional support, which adds an element of risk in your production environment. RXS is completely professionally supported, and is complemented by a staff of trained IBM i developers who can address your nuanced development challenges, head on.

    Second, if you choose not to pursue an open-source solution, you’re often left having to shake up your current program architecture with proprietary software, external dependencies, and partial RPG implementations – many of which are sub-par compared to RPG-XML Suite’s wide range of features. RXS aims to simplify the efforts of developers with tools like code generators, useful commands, and subprocedures written in 100% RPG – no Java. Because they are entirely RPG, the RXS subprocedures are easy to add to new or existing ILE programs and architecture, helping to cut your development time. RPG-XML Suite offers powerful capabilities in an accessible, easy-to-implement format.

    With RPG-XML Suite, you can accomplish a variety of complex tasks, such as:

    • Calling REST and SOAP web services from your IBM i
    • Offering APIs from your IBM i
    • Creating JSON & XML
    • Parsing JSON & XML
    • Text manipulation, Base64 encoding/decoding, CCSID handling, hashing and encryption functions, and more.

    To try RXS for yourself, we recommend a free proof of concept, which not only gives you access to all of RPG-XML Suite’s subprocedures and utilities but also includes a tailor-made software demonstration that can be used as a starting point for your future API implementations.

    For a free proof of concept, contact us at sales@krengeltech.com, or visit our website for more information.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    nuBridges:  Leading provider of secure FTP on the iSeries
    Bytware:  Network security, anti-virus, monitoring, notification/alerts, file recovery, & compliance
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida

    NetManage’s First Quarter Disappoints IBM Cuts Some System i5 Prices to Boost Sales

    Leave a Reply Cancel reply

Volume 6, Number 18 -- May 3, 2006
THIS ISSUE SPONSORED BY:

iTera
WorksRight Software
Patrick Townsend & Associates

Table of Contents

  • SQL Goodies in DB2 for i5/OS V5R4, Part 2
  • V5R4 Improvements to the i5/OS OLE DB Providers
  • Admin Alert: Building a Better Experimental Automatic Deletion Technique

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The Cloud’s Future Is So Bright, So Why Are You So Glum?
  • Most App Modernization Projects a Struggle, Survey Finds
  • COMMON Launches IBM i Security Conference
  • Four Hundred Monitor, August 17
  • A Slew Of Add-On Services For Power10 Systems
  • Power10 Midrange Machine: The Power E1050
  • IBM Puts The Finishing Touches On PowerHA For IBM i 7.5
  • Guru: Regular Expressions, Part 2
  • Get Your Security Education, And Not From The School Of Hard Knocks
  • IBM i PTF Guide, Volume 24, Number 33

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.