The Db2 ODBC shadow catalog

The Db2 ODBC shadow catalog provides increased performance when you need catalog information. To increase the performance of an application that frequently queries the catalog, implement the Db2 ODBC shadow catalog. Redirect catalog functions to the shadow catalog instead of to the native Db2 catalog.

The shadow catalog consists of a set of pseudo-catalog tables that contain rows that represent objects that are defined in the Db2 catalog. These tables are pre-joined and indexed to provide faster catalog access for ODBC applications. Tables in the shadow catalog contain only the columns that are necessary for supporting ODBC operations.

Db2 DataPropagator populates and maintains the Db2 ODBC shadow catalog. Db2 for z/OS® supports the DATA CAPTURE CHANGE clause of the ALTER TABLE SQL statement. This support allows Db2 to mark log records that are associated with any statements that change the Db2 catalog.

Additionally, the Db2 DataPropagator Capture and Apply process identifies and propagates the Db2 catalog changes to the Db2 ODBC shadow, based on marked log records.

CLISCHEM is the default schema name for tables that make up the Db2 ODBC shadow catalog. To redirect catalog functions to access these base Db2 ODBC shadow catalog tables, add the entry CLISCHEMA=CLISCHEM to the data source section of the Db2 ODBC initialization file as follows: 
[DATASOURCE]
MVSDEFAULTSSID=V61A
CLISCHEMA=CLISCHEM
Optionally, you can create views for the Db2 ODBC shadow catalog tables that are qualified with your own schema name, and redirect the ODBC catalog functions to access these views instead of the base Db2 ODBC shadow catalog tables. To redirect the catalog functions to access your own set of views, add the entry CLISCHEMA=myschema (where myschema is the schema name of the set of views that you create) to the data source section of the Db2 ODBC initialization file as follows: 
[DATASOURCE]
MVSDEFAULTSSID=V61A
CLISCHEMA=PAYROLL
APPLTRACE=1
APPLTRACEFILENAME="DD:APPLTRC"

You can use the CREATE VIEW SQL statement to create views of the Db2 ODBC shadow catalog tables. To use your own set of views, you must create a view for each Db2 ODBC shadow catalog table.

Example: Execute the following SQL statement to create a view, where table_name is the name of a Db2 ODBC shadow catalog table: 
CREATE VIEW PAYROLL.table_name AS
  SELECT * FROM PAYROLL.table_name WHERE TABLE_SCHEM='USER01';
The following table lists the base Db2 ODBC shadow catalog tables and the catalog functions that access these tables.
Table 1. Shadow catalog tables and Db2 ODBC APIs
Shadow catalog table Db2 ODBC catalog function
CLISCHEM.COLUMNPRIVILEGES SQLColumnPrivileges()
CLISCHEM.COLUMNS SQLColumns()
CLISCHEM.FOREIGNKEYS SQLForeignKeys()
CLISCHEM.PRIMARYKEYS SQLPrimaryKeys()
CLISCHEM.PROCEDURECOLUMNS SQLProcedureColumns()
CLISCHEM.PROCEDURES SQLProcedures()
CLISCHEM.SPECIALCOLUMNS SQLSpecialColumns()
CLISCHEM.TSTATISTICS SQLStatistics()
CLISCHEM.TABLEPRIVILEGES SQLTablePrivileges()
CLISCHEM.TABLE SQLTables()

Example: If you specify CLISCHEMA=PAYROLL in the data source section of the Db2 ODBC initialization file, the ODBC catalog functions that normally query the Db2 catalog tables (SYSIBM schema) reference a set of views of the ODBC shadow catalog base tables.

The following views access the ODBC shadow catalog base tables:
  • PAYROLL.COLUMNS
  • PAYROLL.TABLES
  • PAYROLL.COLUMNPRIVILEGES
  • PAYROLL.TABLEPRIVILEGES
  • PAYROLL.SPECIALCOLUMNS
  • PAYROLL.PRIMARYKEYS
  • PAYROLL.FOREIGNKEYS
  • PAYROLL.TSTATISTICS
  • PAYROLL.PROCEDURES
  • PAYROLL.PROCEDURECOLUMNS