Guide for overall Repository Performance Maintenance.

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

Description

As time goes by and data is continuously loaded into the Repository, you may notice that the Overall performance of the repository degrades.

Solution

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.