Diagnostic information in Db2 catalog tables

The Db2 catalog tables contain descriptive information about the data stored in Db2 databases.

These tables are updated by SQL statements. The catalog tables are useful diagnostic aids for the following problems:

Authorization problems
If authorization problems occur, a message is issued. Look at the following catalog tables:
  • SYSIBM.SYSCOLAUTH
  • SYSIBM.SYSTABAUTH
  • SYSIBM.SYSDBAUTH
  • SYSIBM.SYSRESAUTH
  • SYSIBM.SYSPACKAUTH
  • SYSIBM.SYSPLANAUTH
  • SYSIBM.SYSUSERAUTH
Select the catalog table that corresponds to the format of the GRANT statement. This catalog table includes information about who has authority and who can grant it.
Index problems
If you receive a message from a utility (message identifier DSNUxxxx) saying the index is incorrect for a given table space, look first at the SYSIBM.SYSINDEXES table, which identifies the table and database. After the table name is known, look at the SYSIBM.SYSTABLES catalog table, which identifies the table space.
Catalog consistency
The sample library, SDSNSAMP, contains sample queries in member DSNTESQ that can be used to check for consistency between catalog tables.
RECOVER utility failure
If the RECOVER utility fails, either a message is issued or an abend occurs. You can use the REPORT utility to view recovery information that the RECOVER utility uses or you can review the SYSIBM.SYSCOPY catalog table. Try to determine which data sets and volumes of image copies were required by RECOVER.
Performance problems following RUNSTATS processing
If performance problems occur after running the RUNSTATS utility, check the data in the SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE catalog tables to confirm that any plans and packages associated with the table space on which the utility was run are rebound. The table spaces referred to by the plans are recorded in SYSIBM.SYSPLANDEP. The tables referred to by the packages are recorded in SYSIBM.SYSPACKDEP.
Performance problems following creation of a new index
If performance problems occur after a new index is created, check the data in the SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE tables to make sure that any plans and packages that use the table on which the index was created are rebound. The tables referred to by the plans are recorded in SYSIBM.SYSPLANDEP. The tables referred to by the packages are recorded in SYSIBM.SYSPACKDEP.
Shortage of EDM pool space
If you receive a message saying that there is insufficient EDM pool space, check the SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE catalog tables. Determine how many plans and associated packages are to be running concurrently and/or the total number of plans and packages accessed during the lifetime of Db2.

Check the KEEPDYNAMIC field in SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE to determine whether plans and packages use the dynamic statement cache. Dynamic statement caching uses the EDM pool, so using the cache without increasing the size of the EDM pool can lead to storage shortages. Using a data space for cached dynamic statements can help alleviate storage shortages.

Conversion error during LOAD utility processing or during INSERT or UPDATE processing
If a conversion error during LOAD processing occurs, a message is issued. Check the SYSIBM.SYSCOLUMNS catalog table to ensure that fields and columns being loaded are compatible. You can also determine the cases in which nulls are permitted.
DASD problems
Check the SPACE column in the SYSIBM.SYSSTOGROUP, SYSIBM.SYSTABLESPACE, and SYSIBM.SYSINDEXES catalog tables. The SPACE column is correct and current only if STOSPACE utility was run. Furthermore, the SPACE column is only applicable to table spaces and indexes using STOGROUPS, and is not applicable to user-defined data sets.