Page 1 of 1

How do I set *LIBL for an iSeries Database Resource?

Posted: Mon Mar 09, 2009 10:37 am
by Support_Duane
  • Edit the Database Server resource that is pointing to your iSeries.
  • In the JDBC URL field, you currently may have something like this:
    jdbc:as400://216.170.14.235;date format=iso;errors=full;naming=sql;prompt=false;time format=iso;transaction isolation=none
    include the list of libraries in the JDBC URL as show below (shown in RED) :
    jdbc:as400://216.170.14.235;date format=iso;errors=full;naming=sql;prompt=false;time format=iso;transaction isolation=none;libraries=LIB1, LIB2, LIB3
  • Edit the project and change your SQL statements (SELECT, DELETE or INSERT) and remove the library name. Use the unqualified name of the file.
  • Update the project and execute it.

If you prefer to use the library list of the user specified in the Database Server resource, Change the JDBC URL in the resource to as shown below:

jdbc:as400://216.170.14.235;date format=iso;errors=full;naming=system;prompt=false;time format=iso;transaction isolation=none;libraries=*LIBL

*LIBL is a valid option if and only if the naming is system. This will gather the library list from the user’s job description (JOBD). Please refer to this IBM article on when to use "system" versus "sql" in the naming setting: http://www-01.ibm.com/support/docview.w ... s8N1017000

Changing the library list at the Database Resource level will affect all the projects that are using the resource. So, if you want different projects to use different sets of libraries, create different database resources.

It would be possible to use just two database resources. One using "naming=sql" and a second using "naming=system" and "libraries=*LIBL". When using the second one, if something other than the default library list specified in the job description of the user is desired, the following command can be passed in a variable, and then specify that variable in a "Run iSeries Command or Program" task.

CHGLIBL LIBL(LIBONE LIBTWO LIBTHREE)

The list of libraries can be built in the CL program that calls the project.

If only one library needs to be added to the library list, use the ADDLIBLE command and pass the library name to be added as a variable.

Two addtional options:

1. You can pass both the library name and the file name in to the project as variables.
2. You can run something like the following command in a query in an SQL task before you run the select query:
qcmdexc (CHGLIBL LIBL(LIBRARY1 LIBRARY2 LIBRARY3 LIBRARY4 LIB5 LIBR6) : 60)
The library list would then be available for any queries run in that task.

For additional information, please refer to the Java documentation on JDBC properties: http://javadoc.midrange.com/jtopen_6_3/ ... rties.html