Control the Library List from a SQL Server Linked Server Definition
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?
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:
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:
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|