DB2ConnectionStringBuilder.SchemaList Property

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 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 i returns result set that contains only database objects with schema that belongs to *USRLIBL.

GetSchema() call to DB2 for i returns result set that contains database objects with combined *USRLIBL with schema names specified in the SchemaList. The DB2 for i must have following requirements:

• PTF Group SF99601 Version 21 and later must be applied on DB2 for i V6R1.
• PTF Group SF99701 Version 11 and later must be applied on DB2 for 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);

Version information

Last update

This topic was last updated for: IBM DB2 Version 10 Fix Pack 1

.NET Framework version

Supported in: 2.0, 3.0, 3.5 and 4.0

IBM Data Server Client

Supported in: IBM DB2 Version 9 through Version 9.7 and later