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
- 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.