DB2 Universal Database provides several CLI/ODBC initialization keywords that can be used to limit the amount of data that will be returned by the initial API calls during the "information gathering" stage after the database is first connected to. These keywords can be set by:
- DBNAME - TABLETYPE - SCHEMALIST - SYSSCHEMA - CLISCHEMA
With the exception of the information for CLISCHEMA, these keywords are documented in the CLI/ODBC help and CLI Guide and Reference. The rest of this discussion relates only to the use of CLISCHEMA.
Here is the documentation for CLISCHEMA that will be added to CLI Guide and Reference in due course:
db2cli.ini Keyword Syntax: CLISCHEMA = clischema Default Setting: No alternatives specified. DB2 CLI/ODBC Settings Tab: Not present.
The CLISCHEMA option indicates an alternative schema, tables, and index set to be searched instead of the SYSIBM (or SYSTEM, QSYS2) schemas when the DB2 CLI and ODBC Catalog Function calls are issued to obtain catalog information.
For example, if you specify CLISCHEMA='SERGE', the internal CLI/ODBC API calls that normally reference the system tables will reference the following user tables instead:
- SERGE.TABLES - SERGE.COLUMNS - SERGE.SPECIALCOLUMNS - SERGE.TSTATISTICS - SERGE.PRIMARYKEYS - SERGE.FOREIGNKEYS - SERGE.TABLEPRIVILEGES - SERGE.COLUMNTABLES - SERGE.PROCEDURES - SERGE.PROCEDURESCOLUMNS.
These user tables must be built by the database administrator before CLISCHEMA can be used.
| Note: | DataPropagator provides support for CLISCHEMA, so that the Database
Administrator can perform this task in three possible ways:
|
A utility to set up the user tables required by CLISCHEMA is provided in the form of the previously undocumented bldschem support command of the CLI Command Line Interface, which can be found as: /samples/cli/db2cli.exe. Documentation for db2cli.exe, not including the bldschem support command, can found in /samples/cli/INTCLI.DOC.
For example, to build the set of user tables that is required to work with CLISCHEMA='SERGE' for the table name STAFF owned by the schema owner (creator) USERID, in the database SAMPLE, you would run the following command after issuing db2start and after registering the database to ODBC/CLI:
db2cli < addstaff.txt
Where "addstaff.txt" contains the following script:
opt callerror on opt echo on quickc 1 1 sample userid password # # Repeat next line for each table to add. # bldschem 1 SERGE USERID STAFF # # Exit # killenv 1
This will result in the creation of the set of tables SERGE.* as listed above, with indexes, populated using the system catalog table data for the table USERID.STAFF. For example, SERGE.TABLES will be populated with a new row for each entry that is matched. Additional bldschem calls result in appends to the existing SERGE.* tables, with replacement of existing rows.
In summary, the syntax of the bldschem support command is:
bldschem <handle_number> <value_of_CLISCHEMA> <schema_owner> <table_name>Where:
- <handle_number> should be 1
- <value_of_CLISCHEMA> should be the same as the schema name specified with
the CLISCHEMA keyword
- <schema_owner> is the creator of the table
- <table_name> can be the name of a user table, a view, an alias, a synonym,
or a system table name.
(Wildcard characters are allowed).
A second example is as follows. If you subsequently run the following through db2cli.exe, then you will append to the user tables SERGE.* created in the previous example, adding rows which reflect the data in the system catalog tables for each table for which FRED and BERT are the schema owners.
bldschem 1 SERGE FRED % bldschem 1 SERGE BERT %
When the CLISCHEMA CLI/ODBC keyword is subsequently set to SERGE, processing by ODBC/CLI applications against the SAMPLE database will reference the SERGE.* set of tables instead of the system catalog tables.