DB2®ConnectionStringBuilder.SchemaList Property

The value indicates the list of schemas to be included when GetSchema is called without a schema restriction value.

Namespace:
IBM.Data.DB2
Assembly:
IBM.Data.DB2 (in IBM.Data.DB2.dll)

Syntax

[Visual Basic]
Public Property SchemaList As String
[C#]
public string SchemaList {get; set;}
[C++]
public: __property String* get_SchemaList();
public: __property void set_SchemaList(String*);
[JScript]
public function get SchemaList() : String;
public function set SchemaList(String);

Property value

A string that represents the list of schemas to use. Each schema name is case-sensitive, must be delimited with single quotation marks, and separated by commas.

SchemaList syntax:
SchemaList = " 'schema1', 'schema2',… 'schemaN' "
Default setting:
None
Usage notes:

SchemaList is used to provide a more restrictive default, and therefore improve performance, for those applications that list every table in the DBMS.

If there are many tables defined in the database, a schema list can be specified to reduce the time it takes for the application to query table information, and reduce the number of tables listed by the application. The entire string must also be enclosed in double quotation marks. For example: 
 
    SchemaList="'USER1','USER2','USER3'"
For Db2® for z/OS®, CURRENT SQLID can also be included in this list, but without the quotation marks, for example: 
 
    SchemaList="'USER1',CURRENT SQLID,'USER3'"

The maximum length of the string is 256 characters.

This option can be used with DBName and TableType to further limit the number of tables for which information is returned.

You can specify *USRLIBL or *ALL along with the existing list of schema names to resolve unqualified stored procedure calls or to find database objects in GetSchema() calls.

If you are migrating from the IBM® i Access .NET driver and you specified *USRLIBL in the DBQ or DefaultLibraries connection string keywords, add *USRLIBL to the list of schema names in the SchemaList keyword as shown in the following example:
  SCHEMALIST="*USRLIBL"
Note: *USRLIBL and *ALL is supported against Db2 for IBM i Version 6 Release 1 and later.

In Db2 Version 9.7 Fix Pack 5, if SchemaList contains *USRLIBL along with other schema names and SchemaFilter contains *USRLIBL, the GetSchema() call to Db2 for IBM i returns result set that contains only database objects with schema that belongs to *USRLIBL.

GetSchema() call to Db2 for IBM i returns result set that contains database objects with combined *USRLIBL with schema names specified in the SchemaList. The Db2 for IBM i must have following requirements:
  • PTF Group SF99601 Version 21 and later must be applied on Db2 for IBM i V6R1.
  • PTF Group SF99701 Version 11 and later must be applied on Db2 for IBM i V7R1.

To reduce a performance cost associated with retrieval of the *USRLIBL value, *USRLIBL value is cached by default. Caching behavior of *USRLIBL is controlled by DB2Connection.CacheUSRLIBLValue property and *USRLIBL cache can be cleared by using the DB2Connection.ClearUSRLIBLCache() method.

 

Examples

Using .NET SchemaList connection string keyword:

DB2Connection conn = new DB2Connection
("database=mydb;uid=user;pwd=password;SchemaList = SCHEMA1, *USRLIBL, schema3;");
Conn.Open();
DataTable tables = conn.GetSchema(DB2MetaDataCollectionNames.Tables);

Using SchemaFilter db2dsdriver.cfg keyword

<cnfiguration>
  <dncollection>
    <dn alias="alias1" name="db1" host="host1" port="50000">
		<prameter name = "SchemaFilter" value = "'SCHEMA1', *USRLIBL, 'schema3'"/>
	</dsn>
   </dsncollection>
 </configuration>

DB2Connection conn = new DB2Connection("database=alias1;uid=user;pwd=password;");
Conn.Open();
DataTable tables = conn.GetSchema(DB2MetaDataCollectionNames.Tables);