DB2 Version 10.1 for Linux, UNIX, and Windows

SchemaList CLI/ODBC configuration keyword

Restricts schemas that are used to query table information.

db2cli.ini keyword syntax:

SchemaList = " 'schema1', 'schema2',… 'schemaN' "

Default setting:

None

Usage notes:

You can use the SchemaList keyword to specify a schema list to reduce the time that it takes for an application to query table information and reduce the number of tables that are listed by 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 DB2® for z/OS®, you can include the CURRENT SQLID option in the list, but without the single quotation marks, as shown in the following example:

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

The maximum length of the string is 2047 characters.

You can use this keyword with the DBName and TableType keywords to further limit the number of tables for which information is returned.

You can specify the user library list (*USRLIBL) or *ALL option in the list of schema names to find libraries in a catalog API call. Specify the *USRLIBL option to search the current libraries of the server job. Specify the *ALL option to search all schemas in the connected database. The *ALL option is the default for CLI. For DB2 for i servers, if you specify the *USRLIBL option, CLI searches the current libraries of the server job. If you are migrating from the IBM® i Access ODBC drive, and you specified the *USRLIBL option for the DBQ or DefaultLibraries connection string keyword, add the *USRLIBL option 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 option for the SchemaList keyword when connecting to the DB2 for i server:

SQLTables
SQLTablePrivileges
SQLColumns
SQLColumnPrivileges

If you specify the *USRLIBL with one or more schema names for the SchemaList keyword option, a call to a catalog function in DB2 for i server returns a result set that contains only the database objects with schema associated with the user library list (*USRLIBL) and not the schema names.

In Version 10.1 Fix Pack 1and later fix packs, a catalog function call toDB2 for 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 i server must meet the following requirements:

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