CA Repository For z/OS RDBMS Exchange(s) Performance

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

Description:

If you are running any of the CA Repository For z/OS RDBMS Exchanges and experiencing poor performance, the following measures should be taken to improve the overall performance of the Exchange.

Solution:

  1. DB2 Runstats

    You should ideally perform RUNSTATS on tables and indexes in the following situations:

    • After data has been loaded into a table and the appropriate indexes have been created. However, it is preferable to create indexes before performing the LOAD command, and collecting statistics during the LOAD.

    • After a new index has been created.

    • After a table has been reorganized with the REORG utility.

    • After the table and its indexes have been extensively updated by data updates, deletions, and insertions.

    • After the prefetch size has been changed.

    • After running the REDISTRIBUTE DATABASE PARTITION GROUP utility.

      Once Runstats has been performed, rebind packages containing static SQL using the BIND command.

      We strongly recommend that this utility is run weekly.

  2. DB2 Reorg Utility

    Queries 1 + 2 provide information on when you should run the REORG utility to
    reorganize a table space.

    • Query 1 returns a list of table spaces that are candidates
      for reorganization.

    • Query 2 returns a list of index spaces that are candidates
      for reorganization.

      • Query 1

        SELECT DBNAME, TSNAME
        FROM SYSIBM.SYSTABLEPART
        WHERE ((CARD > 0 AND (NEARINDREF + FARINDREF) * 100 / CARD > 10)
        OR PERCDROP > 10);

      • Query 2

        SELECT IXNAME, IXCREATOR
        FROM SYSIBM.SYSINDEXPART
        WHERE LEAFDIST > 200;

        If any Repository Tablespaces/Indexspaces are returned by the above queries then appropriate action should be taken to reorg those objects.

  3. Check that there are no missing indexes on the Repository tables. Contact CA Support for a list of indexes