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.
- 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.
- 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
- Query 2 returns a list of index spaces that are candidates
- Query 1
SELECT DBNAME, TSNAME
WHERE ((CARD > 0 AND (NEARINDREF + FARINDREF) * 100 / CARD > 10)
OR PERCDROP > 10);
- Query 2
SELECT IXNAME, IXCREATOR
WHERE LEAFDIST > 200;
If any Repository Tablespaces/Indexspaces are returned by the above queries then appropriate action should be taken to reorg those objects.
- Check that there are no missing indexes on the Repository tables. Contact CA Support for a list of indexes