Other catalog and directory tables in DB2

Catalog versus directory in this list

Most names below are catalog tables in database DSNDB06. Two names are directory table spaces in DSNDB01: SYSLGRNX and SYSUTILX. The catalog is the SQL-facing metadata store. The directory is what Db2 uses while it runs: database descriptors, skeleton packages, log ranges, and utility restart data. You maintain both by running the correct utilities—not by writing UPDATE statements.

SYSIBMTS (Text Search schema)

Checklists sometimes write SYSIBM.SYSIBMTS. On Db2 for z/OS, SYSIBMTS is a reserved schema for IBM Text Search catalog tables, not a single SYSIBM base table. After you enable text search, you see objects such as SYSIBMTS.SYSTEXTSERVERS (text-server connection data), SYSIBMTS.SYSTEXTSTATUS (whether text search is started), and SYSIBMTS.SYSTEXTCONNECTINFO. Administration is through procedures such as SYSPROC.SYSTS_START, not through casual INSERT.

When you list “user tables,” exclude system schemas so text-search and catalog objects do not drown the report:

1
2
3
4
5
SELECT CREATOR, NAME, TYPE, DBNAME, TSNAME
  FROM SYSIBM.SYSTABLES
 WHERE TYPE = 'T'
   AND CREATOR NOT IN ('SYSIBM', 'SYSPROC', 'SYSIBMTS')
 ORDER BY CREATOR, NAME;

DDF must be started for the text-search server to connect back to Db2 even if you CALL SYSTS_START locally. That surprise is why Text Search appears in a catalog chapter: empty SYSTEXTSERVERS rows look like a catalog problem but are often a network or DDF problem.

SYSIBM.SYSSTOGROUP

SYSSTOGROUP has one row per storage group. NAME and CREATOR identify the STOGROUP. VCATNAME is the VSAM catalog high-level qualifier Db2 uses when it defines data sets. SPACE / SPACEF estimate allocated space after STOSPACE. DATACLAS, MGMTCLAS, and STORCLAS are SMS classes when the storage group is SMS-managed. Volumes for non-SMS storage groups appear in SYSIBM.SYSVOLUMES, not on this row.

CREATE STOGROUP writes this row. ALTER STOGROUP ADD/REMOVE volumes updates SYSVOLUMES and timestamps on SYSSTOGROUP. DROP STOGROUP is blocked while table spaces still name the storage group. Finding storage groups is therefore: query SYSSTOGROUP, then join SYSTABLESPACE or SYSINDEXES on the STOGROUP column to see who still uses it.

1
2
3
4
SELECT S.NAME, S.CREATOR, S.VCATNAME,
       S.DATACLAS, S.MGMTCLAS, S.STORCLAS, S.ALTEREDTS
  FROM SYSIBM.SYSSTOGROUP S
 ORDER BY S.NAME;

SYSIBM.SYSCOPY

SYSCOPY contains the information Db2 needs for recovery. Each row is an event against a table space or index space: a COPY, a QUIESCE, a LOAD or REORG that affects recoverability, an ALTER that changes the recovery base, and similar utilities. DBNAME, TSNAME, and DSNUM (partition or 0 for the whole space) identify the object. DSNAME is the image-copy data set. TIMESTAMP is when the event happened. START_RBA / PIT_RBA locate the log.

ICTYPE values you will actually read

ICTYPE is a one-byte code. Beginners should memorize the recovery-critical ones and look up rare ALTER subtypes in the SQL Reference:

• F — COPY FULL YES (full image copy). This is a new recovery base.
• I — COPY FULL NO (incremental). RECOVER needs a prior full copy plus incrementals.
• Q — QUIESCE. A consistent point (RBA/LRSN) without copying pages.
• P — RECOVER TOCOPY / TOLOGPOINT / TORBA (point-in-time recovery).
• E — RECOVER to current.
• R / S — LOAD REPLACE with LOG YES / LOG NO.
• Y / Z — LOAD (resume-style) LOG NO / LOG YES.
• W / X — REORG LOG NO / LOG YES.
• B — REBUILD INDEX.
• D — CHECK DATA LOG(NO) (log range not usable the same way).
• M — MODIFY RECOVERY (rows deleted from SYSCOPY itself).
• T — TERM UTILITY.
• A — ALTER; read STYPE for what changed (add partition, drop column, DSSIZE, and many more).
• V — REPAIR CATALOG.

SHRLEVEL, ICBACKUP (LP/LB/RP/RB for local/recovery primary/backup), and ICUNIT (disk/tape) tell you whether the copy is usable in the current environment. COPY that specified CONCURRENT or FLASHCOPY has additional STYPE encoding—match the utility syntax you ran rather than guessing.

1
2
3
4
5
SELECT TIMESTAMP, ICTYPE, STYPE, DSNUM, DSNAME, SHRLEVEL
  FROM SYSIBM.SYSCOPY
 WHERE DBNAME = 'PAYROLL'
   AND TSNAME = 'TSACCT'
 ORDER BY TIMESTAMP DESC;

SYSIBM.SYSLGRNX (directory)

SYSLGRNX tracks the opening and closing of table spaces, indexes, or partitions and associates those intervals with log RBAs or LRSNs. During RECOVER, Db2 uses SYSLGRNX to avoid scanning the entire log for an object that was not open. That is why the table can grow huge on busy spaces and why IBM recommends regular MODIFY RECOVERY plus REORG of DSNDB01.SYSLGRNX.

MODIFY RECOVERY DELETE AGE(n) or DATE(yyyymmdd) deletes SYSCOPY rows and matching SYSLGRNX ranges older than the criterion. Do not delete so aggressively that you cannot recover to the image copies you still keep on tape. SYSCOPY and SYSLGRNX do not hold records for a few system spaces (including SYSTSCPY, SYSUTILX, DBD01); MODIFY RECOVERY on those spaces does not delete the same way.

SYSIBM.SYSUTILX (directory)

SYSUTILX contains a row for every utility job that is running. The row remains until the utility finishes. If the job abends, Db2 uses that row when you restart the utility with the same UID. -DISPLAY UTILITY reads this status for operators. -TERM UTILITY removes the execution and releases resources—use it when you will not restart.

You do not INSERT into SYSUTILX. Starting COPY, LOAD, REORG, or RUNSTATS creates the row. Leaving a utility in STOPPED status overnight is a common source of SQLCODE -904 (resource unavailable) on the target space. Display first, then TERM or restart.

SYSIBM.SYSPENDINGDDL

Pending definition changes—ALTER TABLESPACE that cannot take effect until REORG—land in SYSPENDINGDDL. Rows identify DBNAME, TSNAME, object schema and name, OBJTYPE, OPTION_KEYWORD, OPTION_VALUE, partition scope (REORG_SCOPE_LOWPART / HIGHPART), CREATEDTS, and STATEMENT_TEXT. SYSPENDINGOBJECTS holds names and OBIDs for objects pending creation as part of that materialization.

Finding “why is my new DSSIZE not in effect?” starts here. If SYSPENDINGDDL still has rows, applications see the old definition. REORG TABLESPACE with the pending-changes materialization behavior your version documents applies them. DROP pending changes with the ALTER ... DROP PENDING CHANGES form when you decide not to materialize.

1
2
3
4
SELECT DBNAME, TSNAME, OBJNAME, OBJTYPE,
       OPTION_KEYWORD, OPTION_VALUE, CREATEDTS
  FROM SYSIBM.SYSPENDINGDDL
 ORDER BY CREATEDTS;

SYSIBM.SYSDEPENDENCIES (SYSDEPEND)

Outlines sometimes say SYSIBM.SYSDEPEND. The SQL Reference name is SYSIBM.SYSDEPENDENCIES. Each row links a base object (BNAME, BSCHEMA, BTYPE) to a dependent object (DNAME, DSCHEMA, DTYPE, DVERSION). Use it when DROP warns about dependents, or when you need impact analysis before altering a table that functions, views, or variables reference.

Package dependencies on tables and indexes are also in SYSIBM.SYSPACKDEP(and plan dependencies in older SYSPLANDEP-style tables). SYSDEPENDENCIES is the more general object graph; SYSPACKDEP answers “which packages must I REBIND.”

SYSIBM.SYSPERIODS and SYSIBM.SYSARCHIVE

SYSPERIODS has one row for each period defined on a table: SYSTEM_TIME or BUSINESS_TIME, with the begin and end column names. Query it to find temporal tables without parsing CREATE TABLE text. SYSTEM_TIME periods imply a history table (see SYSTABLES versioning columns). BUSINESS_TIME periods imply application-period constraints.

SYSARCHIVE links an archive-enabled table to its archive table. When rows are deleted from the enabled table (and archive-sensitive settings apply), Db2 can move them to the archive table. Catalog queries that only look at SYSTABLES TYPE = T miss this pairing—join SYSARCHIVE when you ask “where did deleted rows go?”

SYSIBM.SYSENVIRONMENT

SYSENVIRONMENT stores a unique set of default options used when an object was created or a package was bound: CURRENT SCHEMA, PATHSCHEMAS, CCSID fields, decimal point, string delimiters, mixed data, date/time formats, host language, rounding, APPLCOMPAT, and an ENVID key. Views and SQL functions store an ENVID that must agree on rounding mode when used together in one statement. When two packages “behave differently” on the same SQL, compare their environment rows before blaming the optimizer.

SYSIBM.SYSSTATFEEDBACK, SYSAUXRELS, SYSXMLSTATS

SYSSTATFEEDBACK records missing or conflicting statistics the optimizer noticed (table, column, index, TYPE of recommendation, REASON, LASTDATE). Externalize in-memory recommendations with -ACCESS DATABASE MODE(STATS) or the related profile controls, then RUNSTATS the listed objects. BLOCK_RUNSTATS can suppress a recommendation you have already decided to ignore.

SYSAUXRELS has one row for each auxiliary table created for a LOB column: base table owner/name, COLNAME, PARTITION, and the auxiliary table names. Finding “which LOB table space belongs to EMP.RESUME” is a join from SYSCOLUMNS through SYSAUXRELS to SYSTABLES, not a guess from DSNDB04 names.

SYSXMLSTATS holds statistics for XML data (document counts and similar measures your RUNSTATS level collected). Pair it with SYSXMLSTRINGS (string IDs used to condense XML) when XML query performance is the question. Empty SYSXMLSTATS usually means RUNSTATS never collected XML statistics, not that the XML column is unused.

Finding objects: a query cookbook

Finding tables and columns

1
2
3
4
5
6
7
8
9
10
SELECT T.CREATOR, T.NAME, T.DBNAME, T.TSNAME, T.COLCOUNT
  FROM SYSIBM.SYSTABLES T
 WHERE T.NAME = 'EMPLOYEE'
   AND T.TYPE = 'T';
 
SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'HR'
   AND TBNAME = 'EMPLOYEE'
 ORDER BY COLNO;

Finding indexes, partitions, and keys

SYSINDEXES lists indexes (UNIQUERULE tells P/U/D/C uniqueness). SYSINDEXPART and SYSTABLEPART describe partitions (limit keys, space, STORTYPE). SYSKEYS is the classic index-key column order. Unique and primary-key columns also appear in SYSKEYCOLUSE keyed by constraint name. Primary keys: SYSTABCONST where TYPE = 'P'. Foreign keys: SYSRELS (relationship) plus SYSFOREIGNKEYS (column list) matching RELNAME.

1
2
3
4
5
6
7
8
9
10
SELECT I.CREATOR, I.NAME, I.UNIQUERULE, I.CLUSTERING, I.INDEXSPACE
  FROM SYSIBM.SYSINDEXES I
 WHERE I.TBCREATOR = 'HR'
   AND I.TBNAME = 'EMPLOYEE';
 
SELECT CONSTNAME, COLNAME, COLSEQ
  FROM SYSIBM.SYSKEYCOLUSE
 WHERE TBCREATOR = 'HR'
   AND TBNAME = 'EMPLOYEE'
 ORDER BY CONSTNAME, COLSEQ;

Finding views, packages, plans, and dependencies

Views: SYSTABLES TYPE = 'V' joined to SYSVIEWS (and SYSVIEWDEP for dependencies). Packages: SYSPACKAGE (VALID, OPERATIVE, LASTUSED, isolation). Plans: SYSPLAN. Finding dependencies for bind impact: SYSPACKDEP (BTYPE = T/I/S/R and so on). Finding privileges: SYSTABAUTH, SYSCOLAUTH, SYSDBAUTH, SYSPACKAUTH, SYSPLANAUTH, SYSUSERAUTH, SYSRESAUTH—always filter GRANTEE and look at the *AUTH columns, not at GRANT text in a source library that might be stale.

Finding tablespaces, storage groups, partitions, statistics, owners

• Table spaces — SYSTABLESPACE (PARTITIONS, LOCKRULE, BPOOL, SGNAME).
• Storage groups — SYSSTOGROUP plus SYSVOLUMES; objects point here via SGNAME.
• Partitions — SYSTABLEPART / SYSINDEXPART (PARTITION, LIMITKEY, SPACE).
• Statistics — CARD/CARDF and STATSTIME on SYSTABLES, SYSCOLUMNS, SYSINDEXES; SYSCOLDIST for frequencies; SYSSTATFEEDBACK for gaps; RTS tables SYSTABLESPACESTATS / SYSINDEXSPACESTATS for real-time counters.
• Owners — CREATOR/OWNER on the object row; CREATEDBY is who issued CREATE; OWNERTYPE distinguishes user versus role.

Finding unused and invalid objects

Unused is never a single catalog flag. Combine LASTUSED on packages (and SYSPACKSTMT execution metrics if you collect them), RTS counters that stay zero, and absence from SYSPACKDEP. A table with CARD = -1 often means RUNSTATS never ran, not that the table is empty. Invalid packages have VALID = 'N' on SYSPACKAGE (an ALTER dropped a dependent object). OPERATIVE = 'N' means the package cannot run until you REBIND or FREE. Invalid views show up when underlying tables changed incompatibly—regenerate or DROP/CREATE. Do not DROP “unused” production objects from a one-day LASTUSED sample; batch jobs may run monthly.

1
2
3
4
5
SELECT COLLID, NAME, VERSION, VALID, OPERATIVE, LASTUSED
  FROM SYSIBM.SYSPACKAGE
 WHERE VALID = 'N'
    OR OPERATIVE = 'N'
 ORDER BY LASTUSED;

Explain It Like I'm Five

The catalog is a giant library card drawer for Db2. Some cards say what books (tables) exist. Other cards say where the photocopies (image copies) are, which pages of the diary (log ranges) matter, which copier job is still running (utilities), and which promised shelf rearrangement (pending DDL) is waiting for a big tidy-up (REORG). The directory is the librarian’s private notebook. SYSIBMTS is a special drawer for the “find this word in the book” search engine. Finding objects is just knowing which drawer to open instead of reading every book.

Exercises

1. List storage groups from SYSSTOGROUP and count how many table spaces reference each SGNAME.
2. For one user table space, list SYSCOPY rows from the last 30 days and explain each ICTYPE you see.
3. Query SYSPENDINGDDL. If it is empty, write what that means for ALTER TABLESPACE pending changes.
4. Find the primary key columns of a sample table using SYSTABCONST and SYSKEYCOLUSE.
5. Produce a list of packages with VALID = 'N' and name one safe next action (REBIND versus investigate DROP).