IBM Support

Behavior of the Libraries Property and Default Collection

Troubleshooting


Problem

This document describes the various behaviors that can be expected with various combinations of the default collection and library list properties on an IBM® Toolbox for Java™ JDBC connection.

Resolving The Problem

This document describes the behavior of the IBM Toolbox for Java JDBC driver with various combinations of the default collection and libraries connection properties and unqualified SQL queries. The behavior is different depending on the naming convention (SQL or system) that is used.

SQL Naming
  • Only one library is ever searched with SQL naming. This library is known as the default collection.
  • If a library is specified in the URL, it is the default collection.
  • If no library is specified on the URL, the first library in the "libraries" property becomes the default collection.
  • If no library is specified in the URL or the "libraries" property, the default collection is set to the user profile name of the signed on user.
  • If the libraries property starts with *LIBL, the second value becomes the default collection.
  • The library list of the job is updated with the libraries in the "libraries" property. This setting might affect the behavior of triggers and stored procedures.

System Naming
  • The library list of the job is searched, UNLESS a default collection is specified.
  • The library list of the job is updated to the value specified in the "libraries" connection property.
  • If a default collection is specified on the URL, it is the only library searched.

Examples
The examples in the following tables were generated running the unqualified SQL statement "SELECT * FROM MYTABLE" that exists in library MYLIB. In the following table, BOGUS refers to a library that does not exist. In these examples, the library list of the job does not contain MYLIB unless added with the connection properties. The user profile for these tests was MYUSER.
 
Scenario SQL Naming System Naming
1. No library specified any place Default collection is the user profile name. No default collection. Library list is searched.
2. Library specified on URL does not contain file. Default collection is the specified library. Default collection is the URL specified library, and is the only library searched.
3. Default library specified on URL contains file. Default collection is the specified library. Default collection is the URL specified library, and is the only library searched.
4. Libraries specified by the "libraries" property. Default collection is the first specified library. No default collection. All libraries in the "libraries" property searched.
5. Libraries specified on URL and "libraries" property. Default collection is the library specified on the URL. Default collection is the URL specified library, and is the only library searched.
6. Libraries specified by the "libraries" property. List contains an invalid name. Default collection is the first specified library, even if it is bad. No default collection. Library list is searched. However, the library list is not updated with the new list because of the invalid value.
7. Libraries specified by the "libraries" property. File is found in the second library in the list. Default collection is the first specified library. No default collection. All libraries in the "libraries" property searched.
8. Libraries specified by the "libraries" property. The value of the property starts with a comma. Default collection is user profile name. No default collection. All libraries in the "libraries" property searched.
9. Libraries specified by the "libraries" property. The value of the property starts with *LIBL. Default collection is second value in the list. No default collection. All values in the "libraries" property are added to the end of the current library list, and the entire list is searched.
10. Libraries specified by the "libraries" property. The value of the property ends with *LIBL. Default collection is the first specified library. No default collection. All values in the "libraries" property are added to the beginning of the current library list, and the entire list is searched.

Results
In the following table, the "sql" in the URL was replaced with "system" for the system naming test.
Scenario SQL Naming System Naming
1. jdbc:as400://system;naming=sql; [SQL0204] MYTABLE in MYUSER type *FILE not found. [SQL0204] MYTABLE in *LIBL type *FILE not found.
2. jdbc:as400://system/QGPL;naming=sql; [SQL0204] MYTABLE in QGPL type *FILE not found. [SQL0204] MYTABLE in QGPL type *FILE not found.
3. jdbc:as400://system/MYLIB;naming=sql; Data returned Data returned
4. jdbc:as400://system;libraries=MYLIB;naming=sql; Data returned Data returned
5. jdbc:as400://system/QGPL;libraries=MYLIB;naming=sql; [SQL0204] MYTABLE in QGPL type *FILE not found. [SQL0204] MYTABLE in QGPL type *FILE not found.
6. jdbc:as400://system;libraries=BOGUS,MYLIB;naming=sql; [SQL0204] MYTABLE in BOGUS type *FILE not found. [SQL0204] MYTABLE in *LIBL type *FILE not found.
7. jdbc:as400://system;libraries=QGPL,MYLIB;naming=sql; [SQL0204] MYTABLE in QGPL type *FILE not found. Data returned.
8. jdbc:as400://system;libraries=,MYLIB,QGPL;naming=sql; [SQL0204] MYTABLE in MYUSER type *FILE not found. Data returned.
9. jdbc:as400://system;libraries=*LIBL,MYLIB,QGPL;naming=sql; Data returned. Data returned.
10. jdbc:as400://system;libraries=MYLIB,QGPL,*LIBL;naming=sql; Data returned. Data returned.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

26775920

Document Information

Modified date:
29 June 2021

UID

nas8N1017000

Page Feedback