SchemaList CLI/ODBC and IBM data server driver configuration keyword

You can use the SchemaList keyword to specify a schema list. The schema list reduces the time that an application takes to query table information and reduce the number of tables that are returned to the application. Each schema name is case-sensitive. As shown in the following example, you must delimit a schema name with single quotation marks, separate schema names by commas, and enclose the list in double quotation marks:

 
    SchemaList="'USER1','USER2','USER3'"

For the Db2® for z/OS® server, you can include the CURRENT SQLID value in the list, but without the single quotation marks, as shown in the following example:

 
    SchemaList="'USER1',CURRENT SQLID,'USER3'"

You can specify the user library list (*USRLIBL) or *ALL value in the list of schema names to find libraries in a catalog function call. Specify the *USRLIBL value to search the current libraries of the server job. Specify the *ALL value to search all schemas in the connected database. The *ALL value is the default for the CLI driver. For Db2 for IBM i servers, if you specify the *USRLIBL value, the CLI driver searches the current libraries of the server job. The *USRLIBL value that is retrieved from the server, on the first catalog function call, is cached by default. The same *USRLIBL value is used for all subsequent catalog function calls in the same connection handle. You can change the caching behavior of the user library list with one of the following methods:

• The SQL_ATTR_CACHE_USRLIBL attribute that is specified with the SQLSetConnectAttr() function.
• The CacheUsrLibL keyword in the db2cli.ini file.
• The CacheUsrLibL keyword in the db2dsdriver.cfg file.

If you are migrating from the IBM i Access ODBC drive, and you specified the *USRLIBL value for the DBQ or DefaultLibraries connection string keyword, add the *USRLIBL value to the list of schema names for the SchemaList keyword, as shown in the following example:

  [DSNSAMP]
  SCHEMALIST="*USRLIBL"

Following catalog functions are affected by the *USRLIBL value for the SchemaList keyword when you are connecting to the Db2 for IBM i server:

• SQLTables()
• SQLTablePrivileges()
• SQLColumns()
• SQLColumnPrivileges()

A catalog function call to a Db2 for IBM i server returns a result set that contains database objects with schema that belong to user library list (*USRLIBL) and schema names that are listed in the SchemaList keyword. For example, following SchemaList keyword example returns a result set that contains database objects with schemas associated with the user library list, "schema1" name and "schema2" name:

  [DSNSAMP]
  SCHEMALIST="*USRLIBL, ‘schema1’, ‘schema2’"

To obtained database objects with combined user library list (*USRLIBL) and schema names that you specified for the SchemaList keyword, the Db2 for IBM i server must meet the following requirements:

• You must apply PTF Group SF99601 Version 21 or later on Db2 for IBM i V6R1.
• You must apply PTF Group SF99701 Version 11 or later on Db2 for IBM i V7R1.