Control the Library List from a SQL Server Linked Server Definition

Published: July 23, 2008

Hey, Mike:

We are having more and more users call AS/400 jobs from a SQL Server session on their PCs, and we have trouble giving them the proper library list. Usually, just giving them the libraries in QUSRLIBL works fine. Is there a way to make sure every user, regardless of originating machine or system, gets the library list values of QUSRLIBL?

--Barry

Barry,

I'm guessing that you're using the SQL Server's linked server facility to connect to the AS/400. If so, the first thing you need to do is check the linked server's data provider. SQL Server will use either an ODBC driver or OLE DB provider to talk with DB2 for i (formerly known as DB2 for i5/OS and DB2/400). These providers come with System i Access (formerly iSeries Access) or you can purchase a provider from a third party vendor. Since many companies use the IBM supplied providers, I'm going to stick with examples using the ODBC driver and the IBMDASQL OLE DB provider that come with System i Access V5R3 and higher. (For the record, this tip is not intended to describe how to set up linked servers within SQL Server. Rather, it assumes a developer knows how to configure a linked server.)

Setting the Database Server Job's Library List

When a remote client connects to DB2 for i using ODBC or OLE DB, a database server job is assigned to handle the client's SQL requests. Developers often want this server job's library list to match the library list that a green screen application uses, in order to allow remote client SQL requests to call RPG programs or access tables and views with the required library list intact, which in turn makes all of the required resources available to programs.

A job's library list has two parts: the system library list and the user library list. The system library list is set with the QSYSLIBL system value and cannot currently be overridden by a client such as SQL Server. However, the user library portion of the library list for a database server job can be set in a number of ways from SQL Server (or just about any remote client). They are:

1. Define the library list in the connection string
2. Use the library list defined in the user profile's job description (*JOBD)
3. Use the library list defined in the QUSRLIBL system value

Let's look at each option.

1. Define the Library List in the Connection String

Whether using the ODBC provider or the IBMDASQL provider, a connection string can contain a hard coded library list. If you're using the MSDASQL (ODBC) provider, your provider string should contain a reference to the DBQ keyword followed by the desired library list for the database server job as follows:

ODBC: SYSTEM=as400.i3.local;DBQ=,QTEMP,EJIWDATA,GRABELL,QGPL;NAM=1

As an aside, the DefaultLibraries keyword can be substituted for the DBQ keyword. You may have noticed the initial comma specified before the first library name. This initial comma tells the ODBC driver not to use the first library in the list as the default library. Otherwise, unqualified table and view names would be presumed to exist in this "first" library. The keyword/value combination NAM=1 tells the ODBC driver to use the system naming convention, which is what I usually prefer to use on systems where multiple production library lists are available to an application.

If you're using the IBMDASQL OLE DB provider, here is how to define a library list within the connection string:

IBMDASQL: Library List=QTEMP,I3,WRKDBF,QGPL;Naming Convention=1

This is very similar to the ODBC connection string except the keyword is "Library List" instead of DBQ.

When configuring a linked server, place these connection string values in the provider string property along with any other values that need to be set.

2. Use the Library List Defined in the User Profile's Job Description (*JOBD)

The linked server facility connects to DB2 for i with a user name and password supplied by the developer (a.k.a. the security context). With a hard-coded library list in the connection string the connecting user doesn't matter because the job's library list will always be replaced by the one in the connection string. But we can make the host job on IBM i (a.k.a. System i, iSeries, AS/400) use the library list contained in the user profile's job description by specifying the *USRLIBL special value in the connection string:

ODBC: SYSTEM=as400.i3.local;DBQ=,*USRLIBL;NAM=1
IBMDASQL: Library List=*USRLIBL;Naming Convention=1

When using the *USRLIBL special value, the library list will be dependent on the user id connecting to DB2 for i. Also, the *USRLIBL special value can be combined with other individual library names by specifying something like: APPLIB1,*USRLIBL or *USRLIBL,DATALIB. In this first example, APPLIB1 will be the first library in the library list followed by the libraries specified in the user's job description. In the second example, the database server job will start with the libraries in the user's job description followed by the DATALIB library.

Since SQL Server has the ability to vary the DB2 user ID based on the SQL Server user ID, it is possible for the same linked server definition to start database server jobs with different user IDs and theoretically, different library lists. In order for the job to inherit the libraries from the user's job description the job description must have its initial library list parameter filled in with a value other than *NONE.

3. Use the Library List Defined in the QUSRLIBL System Value

That brings us to the final point and also an answer to the specific question about using the QUSRLIBL values: when using *USRLIBL in the connection string and when *NONE is specified on the user's job description initial library list parameter, the job will simply take on the library list specified in the QUSRLIBL system value.

Therefore, if your application allows it, the easiest scenario to make every connection use the default QUSRLIBL library list would be for each linked server definition to use a single DB2 user ID that is defined with a job description that contains an initial library list of *NONE. My guess is you may be using some user profiles that use QDFTJOBD for their job description and its default library list is QTEMP and QGPL. Another option would be to use a different job description for these user profiles that contains the same libraries as system value QUSRLIBL.

How Do We Know We Have the Right Library List?

All of this can be a bit tricky to verify the right list is being used, so I wrote a quick and dirty external CL User-Defined Function (UDF) called RTVLIBL() that will allow a developer to easily verify the system and user library list of the current job. Below is the source code including the CREATE FUNCTION statement required to register the UDF with SQL.

/*                                                               +
                                                                 +
RTVLIBL: Retrieve system and user defined library list           +
         (SQL UDF) - used primarily for verifying a job's        +
         *LIBL is setup correctly.                               +
                                                                 +
                                                                 +
After compiling the program, execute the following SQL statement +
to register the function:                                        +
----------------------------------------                         +
CREATE FUNCTION QGPL/RTVLIBL()                                   +
RETURNS CHAR(2916)                                               +
LANGUAGE CL                                                      +
EXTERNAL                                                         +
PARAMETER STYLE SQL                                              +
DETERMINISTIC                                                    +
NOT FENCED                                                       +
                                                                 +
                                                                 +
*/                                    
Pgm (&AllLibl &AllLibl_NI +                    
     &SqlState &SqlFunc &SqlSpcName &SqlWrnTxt)
Dcl &UsrLibl    *Char 2750                     
Dcl &SysLibl    *Char 165                      
Dcl &AllLibl    *Char 2916                     
Dcl &AllLibl_NI *Char 2  Value(X'0000')         

Dcl &SqlState   *Char 5                        
Dcl &SqlFunc    *Char 120                      
Dcl &SqlSpcName *Char 139                      
Dcl &SqlWrnTxt  *Char 72                       

RtvJobA SysLibl(&SysLibl) UsrLibl(&UsrLibl)   
ChgVar &AllLibl (&SysLibl|<'-'||&UsrLibl)      
                                             
EndPgm

Once the function has been compiled and registered, a linked server job's library list can be easily verified using the OpenQuery table function as follows:

Select * 
  From OpenQuery(DB2i, 'Select RtvLibl() From SYSIBM/SYSDUMMY1') As LiblTest

The result will be one large column that looks something like this:

QSYS       QSYS2      QHLPSYS    QUSRSYS-QTEMP      I3         WRKDBF     QGPL

The system library portion is on the left with the user library portion on the right separated with a dash.

This sample assumes that there is a defined linked server named DB2i and that the system naming convention is in use by the provider. Further it assumes that the RTVLIBL UDF has been created in a library within the job’s library list. (I recommend putting it a library like QGPL just to be safe!)

Remember that even though the job library list may be set up correctly, the queries passed along to DB2 by SQL Server may not use the library list when resolving unqualified table and view names. This depends on whether or not the System or SQL naming convention is used within the ODBC setup. Generally, I like to use the library list feature (and therefore the system naming convention). However, if you're using the four-part table naming convention with linked servers (popular with SQL Server 7.0 and SQL Server 2000), you should stick with the SQL naming convention. In SQL Server 2005 and SQL Server 2008, the EXEC AT statement provides a great mechanism for performing pass-through SQL statements that is well suited for use with the library list and in most ways supersedes the four-part naming convention.

Finally, if none of this is client-side stuff is helpful in your environment, there is a way to set the library list dynamically using the SQL Database Exit Point program (see Related Stories below.) This technique is completely server side (using a language like RPG, C, or COBOL) and uses a little "slight of hand" magic to override the job's library list with each remote SQL request. To meet your requirements, you can use this technique to force each database server job hosting a remote SQL request to have a specific library list matching the one in QUSRLIBL.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Contact him using the IT Jungle Contact page.

RELATED STORIES

V5R4 Improvements to the i5/OS OLE DB Providers

Get Creative Using the SQL Database Exit Point

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot