Replication Guide and Reference

Using the DB2 ODBC Catalog

The DB2 ODBC Catalog is designed to improve the performance of ODBC applications. The tables in the DB2 ODBC Catalog are prejoined and indexed to support faster catalog access for ODBC applications. IBM's ODBC driver also supports multiple views of the DB2 ODBC Catalog.

Support for use of the DB2 ODBC Catalog is provided by DB2 DataPropagator Version 5 and later. For information about Version 5 level support, see the IBM Replication Guide and Reference V5. Enhancements to the DB2 ODBC Catalog for DB2 DataPropagator Version 6 include:

Support for the SYSIBM.SYSROUTINES table
Support for the SQLProcedureColumns ODBC function call

You can eliminate data currency problems by using the DB2 ODBC Catalog tables. DB2 DataPropagator for OS/390 Version 6 can keep data in the DB2 ODBC Catalog synchronized with the contents of the real DB2 catalog table. The Capture program identifies log records that represent changes to the DB2 catalog and records these changed data records in a staging table. The Apply program replicates the changed data records to the DB2 ODBC Catalog tables.

This section describes how to implement the DB2 ODBC Catalog using the automatic mode. The automatic mode automatically replicates any DB2 Catalog changes to the DB2 ODBC Catalog tables.

Setting up the DB2 ODBC Catalog

The following section provides setup instructions needed to prepare your client and server to run your ODBC queries.

Setting up the workstation client

To use the entire DB2 ODBC Catalog, add the entry CLISCHEMA=CLISCHEM to the DB2CLI.INI file. To use your own set of views rather than the entire DB2 ODBC Catalog, add the entry CLISCHEMA=MYSCHEMA to the DB2CLI.INI file. The following example contains both statements.

[tstcli1x]
uid=userid
pwd=password
autocommit=0
TableType="'TABLE','VIEW','SYSTEM TABLE'"
 
[tstcli2x]
  Assuming dbalias2 is a database in DB2 for MVS
SchemaList="'OWNER1','OWNER2','CURRENT SQLID'"
 
[MyVeryLongDBALIASName]
dbalias=dbalias3
SysSchema=MYSCHEMA
 
[RDBD2205]
AUTOCOMMIT=1
LOBMAXCOLUMNSIZE=33554431
LONGDATACOMPAT=1
PWD=USRT006
UID=USRT006
DBALIAS=RDBD2205
CLISCHEMA=CLISCHEM
 
[RDBD2206]
AUTOCOMMIT=1
LOBMAXCOLUMNSIZE=33554431
LONGDATACOMPAT=1
PWD=USRT006
UID=USRT006
DBALIAS=RDBD2206
CLISCHEMA=MYSCHEMA

You must define views for all the DB2 ODBC Catalog tables when you use your own schema. See Table 10 for the list of the DB2 ODBC Catalog tables for which you must define a view. Use the following VIEW MYSCHEMA statement to define the DB2 ODBC Catalog views on CLISCHEM.table_name ODBC tables.

CREATE VIEW MYSCHEMA.table_name FROM CLISCHEM.table_name
 where TABLE_SCHEM=MYUSER

Where table_name is one of DB2 ODBC Catalog table names.

Setting up the server

To set up the server, define the following control information for replication:

Create the DB2 DataPropagator for OS/390 control tables, if they do not already exist.
1. Review the header portion of the ASNL2CN6.SQL file and customize the table spaces according to your site requirements.
2. Connect to the OS/390 RDB that contains the catalog from which you want to create a new DB2 ODBC Catalog.
3. Run the ASNL2CN6.SQL file from either the client or the OS/390 server.
Create the source, subscription control, and table space information for the DB2 ODBC Catalog.
1. Review the header portion of the ASNL2SY6.SQL, ASNL2RE6.SQL, and ASNL2SU6.SQL files and customize the table spaces according to your site requirements.
2. Replace all occurrences of SRCE in the ASNL2SU6.SQL file with the OS/390 RDB name. You can also define additional views on the predefined subscriptions to further qualify the subscriptions.
3. Connect to the OS/390 RDB that contains the catalog from which you want to create a new DB2 ODBC Catalog.
4. Run the ASNL2SY6.SQL, ASNL2RE6.SQL, and ASNL2SU6.SQL files from either the client or the OS/390 server.
Start the Capture and Apply programs on OS/390. Starting the Capture and Apply programs populates the ODBC Catalog on OS/390.

DB2 ODBC Catalog tables

Table 10 lists the ODBC function calls that are supported by the DB2 ODBC Catalog and explains how the function calls are implemented by DB2 DataPropagator for OS/390 V6.

Table 10. ODBC Function Calls

ODBC Function Call ODBC Catalog Tables
SQLColumns
The SELECT command is issued against preformatted data stored in CLISCHEM.COLUMNS.
This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLColumnPrivileges
The SELECT command is issued against prejoined data stored in CLISCHEM.COLUMNPRIVILEGES.
This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH, and SYSIBM.SYSCOLAUTH.
SQLForeignKeys
The SELECT command is issued against prejoined data stored in CLISCHEM.FOREIGNKEYS.
This call is implemented with source tables SYSIBM.SYSRELS, SYSIBM.SYSFOREIGNKEYS, and SYSIBM.SYSCOLUMNS.
SQLPrimaryKeys
The SELECT command is issued against the primary keys stored in CLISCHEM.PRIMARYKEYS.
This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLProcedures
The SELECT command is issued against CLISCHEM.PROCEDURES, which contains only the columns required by the SQLProcedures function.
This call is implemented with source table SYSIBM.SYSROUTINES.
SQLSpecialColumns
The SELECT command is issued against prejoined data stored in CLISCHEM.SPECIALCOLUMNS.
This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSKEYS, and SYSIBM.SYSINDEXES.
SQLTablesPrivileges
The SELECT command is issued against CLISCHEM.TABLEPRIVILEGES.
This call is implemented with source table SYSIBM.SYSTABAUTH.
SQLTables
The SELECT command is issued against pre-joined data stored in CLISCHEM.TABLES.
This call is implemented with source table SYSIBM.SYSTABLES.
SQLStatistics
The SELECT command is issued against pre-joined data stored in CLISCHEM.TSTATISTICS.
This call is implemented with source tables SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES, and SYSIBM.SYSKEYS.
SQLProcedureColumns
The SELECT command is issued against pre-joined data stored in CLISCHEM.PROCEDURECOLUMNS.
This call is implemented with source tables SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS.

ODBC Function Call	ODBC Catalog Tables
SQLColumns	The SELECT command is issued against preformatted data stored in CLISCHEM.COLUMNS. This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLColumnPrivileges	The SELECT command is issued against prejoined data stored in CLISCHEM.COLUMNPRIVILEGES. This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH, and SYSIBM.SYSCOLAUTH.
SQLForeignKeys	The SELECT command is issued against prejoined data stored in CLISCHEM.FOREIGNKEYS. This call is implemented with source tables SYSIBM.SYSRELS, SYSIBM.SYSFOREIGNKEYS, and SYSIBM.SYSCOLUMNS.
SQLPrimaryKeys	The SELECT command is issued against the primary keys stored in CLISCHEM.PRIMARYKEYS. This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLProcedures	The SELECT command is issued against CLISCHEM.PROCEDURES, which contains only the columns required by the SQLProcedures function. This call is implemented with source table SYSIBM.SYSROUTINES.
SQLSpecialColumns	The SELECT command is issued against prejoined data stored in CLISCHEM.SPECIALCOLUMNS. This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSKEYS, and SYSIBM.SYSINDEXES.
SQLTablesPrivileges	The SELECT command is issued against CLISCHEM.TABLEPRIVILEGES. This call is implemented with source table SYSIBM.SYSTABAUTH.
SQLTables	The SELECT command is issued against pre-joined data stored in CLISCHEM.TABLES. This call is implemented with source table SYSIBM.SYSTABLES.
SQLStatistics	The SELECT command is issued against pre-joined data stored in CLISCHEM.TSTATISTICS. This call is implemented with source tables SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES, and SYSIBM.SYSKEYS.
SQLProcedureColumns	The SELECT command is issued against pre-joined data stored in CLISCHEM.PROCEDURECOLUMNS. This call is implemented with source tables SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]