Repository For z/OS RDBMS Exchange(s) Performance

Products

• Repository

Issue/Introduction

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

 

 

Environment

7.2

Repository-Webstation Option

Resolution

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 Broadcom Support t for a list of indexes