As time goes by and data is continuously loaded into the Repository, you may notice that the Overall performance of the repository degrades.
Routine maintenance must be performed to keep the Repository running at optimal performance.
This includes DB2 Runstats, rebinds, and reorgs.
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;
Check to see if any repository tables/indexes are returned by these queries and reorg them as necessary.
As a best practice, it is recommended that the following 'core' repository tables (DBX_XREF, DBX_WKSN_XREF, DBX_DDL_ELEMENTS) are frequently reorganized.