Like most of the facilities within the CA Repository for z/OS tool set, CatSync performance is affected by the fragmentation and statistics of the DB2 table spaces that store the metadata it manipulates. Accordingly, we recommend that you run the DB2 utilities REORG and RUNSTATS against the table spaces that the DB2 dialog uses. After you run RUNSTATS, you should also rebind the packages that contain the CatSync DBRMs (DBXCAT*). CatSync uses the most time and resources during the Catalog step, when it is actually scanning the catalog tables.
If the performance of the Catalog step is an issue for your site, you may want to evaluate the benefits gained through using a shadow catalog. A shadow catalog is a set of standard DB2 tables that are exact copies of the Catalog tables. These tables should be populated with the data currently found within the real Catalog on a regularly scheduled basis. As far as the CatSync process is concerned, the only difference between the shadow and the real catalog tables is that indexes can be added to the shadow tables.
Note: Multiple shadow catalogs are required to support sites where duplicate databases, tablespaces, tables, views or distinct data types exist across multiple DB2 subsystems.
If you decide to use a shadow catalog to improve CatSync performance, we suggest that you add indexes to the columns of the shadow tables that correspond to the columns of the real DB2 Catalog tables listed below.
DB2 Catalog Table Catalog Columns
SYSAUXRELS AUXTBNAME, TBNAME, PARTITION
SYSCOLUMNS TBNAME, TBCREATOR, NAME
SYSDATATYPES SCHEMA, NAME, SOURCETYPE
SYSFIELDS TBNAME, TBCREATOR, NAME, FLDPROC
SYSFOREIGNKEYS TBNAME, CREATOR, RELNAME
SYSINDEXESa NAME, CREATOR, DBNAME
SYSINDEXPART IXNAME, IXCREATOR, PARTITION
SYSKEYS IXNAME, IXCREATOR, COLNAME
SYSRELS TBNAME, CREATOR
SYSROUTINES SCHEMA, NAME
SYSSYNONYMS TBNAME, TBCREATOR
SYSTABLEPART TSNAME, DBNAME
SYSTABLESa NAME, CREATOR, TYPE
SYSTABLES NAME, CREATOR
SYSTABLES TSNAME, DBNAME, TYPE
SYSTABLESPACEa NAME, DBNAME
SYSTRIGGERS NAME, SCHEMA, TBNAME
SYSVIEWDEP DNAME, DCREATOR, BNAME
SYSVIEWS NAME, CREATOR
This should be a unique index.
Populate the Shadow Catalog
The simplest way to populate the new shadow tables with the DB2 object definitions stored in the Catalog is to use the DB2 Unload and Load utilities. Because the shadow catalogs should be composed of the same columns as the catalog tables, the manipulation of the utilities should be fairly straightforward.
To populate the shadow catalog, follow these steps:
- Run the DB2 UNLOAD utility against the DB2 catalog tables.
- Run the DB2 LOAD utility to load the data extracted from the DB2 catalog tables in step 1 into the shadow catalog tables. Make sure you save the JCL for this process because you will need to repopulate of the shadow catalog on a regular basis, especially if the catalog is very active.
- Run the REORG and RUNSTATS utilities against the shadow catalog's table spaces.
- Generate the bind job for the repository packages for DBXCAT*DBRMs. You can use the CA Repository for z/OS Installation Menu's Bind Plans option to generate this job.
- Check the bind statement to ensure it includes the DBRM DBXCAT:
- If you are using DB2 version 6, make sure it includes DBXCAT6
- If you are using DB2 version 7, make sure it includes DBXCAT7
- If you are using DB2 version 8, make sure it includes DBXCAT8 etc.
- Change the Qualifier name from SYSIBM to Shadow Catalog Creator.
- Submit the bind job. You can now run both test and real catalog synchronization jobs against the shadow catalog.