SchemaFilter IBM data server driver configuration keyword

Restricts schemas that are used to query table information.

Equivalent CLI keyword
SchemaList
Equivalent IBM® data server provider for .NET connection string keyword
SchemaList
IBM data server driver configuration file (db2dsdriver.cfg) syntax
<parameter name="SchemaFilter" value=" 'schema1', 'schema2',… 'schemaN' "/>
Default setting
None
Usage notes
You can use the SchemaFilter 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. You must delimit a schema name with single quotation marks and separate schema names by commas, as shown in the following example: 
 
    SchemaFilter='USER1','USER2','USER3'
For 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: 
 
    SchemaFilter='USER1',CURRENT SQLID,'USER3'

The maximum length of the string is 2047 characters.

You can use the SchemaFilter keyword with the ZOSDBNameFilter keyword and the TableTypeFilter keyword to further limit the number of database objects for which information is returned.

For Db2 for IBM i server, DBQ, or DefaultLibraries specifies the IBM i libraries to add to the server job's library list. The libraries are delimited by commas or spaces, and the user library list (*USRLIBL) can be used as a place holder for the server job's current library list. The library list is used for resolving unqualified stored procedure calls and finding libraries in catalog function calls. If the *USRLIBL value is not specified, the specified libraries replace the server job's current library list.

You can specify the *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 *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 driver 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 SchemaFilter keyword, as shown in the following example:
  <parameter name=SchemaFilter value=*USRLIBL/>
If you are migrating from the IBM i Access for Windows .NET provider and you specified the *ALL value for the searchSchemaFilter property, specify the *ALL value for the SchemaFilter keyword, as shown in the following example:
  <parameter name=SchemaFilter value=*ALL/>
Following catalog functions are affected by the *USRLIBL value for the SchemaFilter keyword when you are connecting to Db2 for IBM i servers:
  • SQLTables()
  • SQLTablePrivileges()
  • SQLColumns()
  • SQLColumnPrivileges()
A catalog function (for CLI) or a GetSchema() function (for .NET data provider) call toDb2 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 SchemaFilter keyword. For example, following SchemaFilter keyword example returns a result set that contains database objects with schemas associated with the user library list, “schema1” name and “schema2” name: 
  <parameter name=SchemaFilter value=*ALL, ‘schema1’, ‘schema2’/>
To obtained database objects with combined user library list (*USRLIBL) and schema names that you specified for the SchemaFilter 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.