Catsync Maintenance and Performance Tuning.

Document ID : KB000024057
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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.

Solution:

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
SYSDATABASEa       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
SYSSTOGROUPa       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:

  1. Run the DB2 UNLOAD utility against the DB2 catalog tables.

  2. 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.

  3. Run the REORG and RUNSTATS utilities against the shadow catalog's table spaces.

  4. 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.

  5. 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.

  6. Change the Qualifier name from SYSIBM to Shadow Catalog Creator.

  7. Submit the bind job. You can now run both test and real catalog synchronization jobs against the shadow catalog.