Guide for overall Repository Performance Maintenance.

Products

• Repository

Issue/Introduction

 

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

 

 

 

Environment

 7.2

Repository-Webstation Option
 

Resolution

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.