Improving Fault Analyzer DB2 performance

The following information is applicable to DB2® versions prior to DB2® V10. For DB2® V10 or later, this information can be ignored.

DB2® does not have an index defined on the SYSIBM.SYSDBRM catalog table. Fault Analyzer accesses the SYSIBM.SYSDBRM catalog table whenever it performs analysis for a DB2® fault. To avoid the possibility of poor performance when accessing SYSIBM.SYSDBRM, you can create a user-defined index on the SYSIBM.SYSDBRM catalog table. The non-unique index should specify the following columns (in order):

PLNAME
NAME

The following sample DDL can be used to define the index:

CREATE TYPE 2 INDEX nnnnnn.DBRMX            ❶
          ON SYSIBM.SYSDBRM
         (
          PLNAME ASC,
          NAME ASC
         )
         USING STOGROUP mmmmmm       ❶
               PRIQTY pp             ❷
               SECQTY ss             ❷
CLOSE NO;

Notes:

❶: Change the name of the index (nnnnnn) and storage group (mmmmmm) to suit your requirements. For example, use index name SYSIBM and storage group STOGROUP.
❷: Change the primary (pp) and secondary (ss) quantities to suit your requirements. For example, use 250 for both primary and secondary quantity.

A sample job, IDISDB2X, is distributed in IDI.SIDISAM1 to help you do this.