DB2 object catalog tables

The containment chain

On z/OS, data is nested: database → table space → table → column. Indexes hang off tables. Constraints hang off tables. Views are named SELECTs that still appear as rows in SYSTABLES. Catalog queries follow that chain with creator and name columns—not with LUW's TABSCHEMA / TABNAME names.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT D.NAME AS DBNAME,
       S.NAME AS TSNAME,
       T.NAME AS TBNAME,
       T.TYPE,
       T.COLCOUNT
  FROM SYSIBM.SYSDATABASE D
  JOIN SYSIBM.SYSTABLESPACE S
    ON S.DBNAME = D.NAME
  JOIN SYSIBM.SYSTABLES T
    ON T.DBNAME = S.DBNAME
   AND T.TSNAME = S.NAME
 WHERE D.NAME = 'HRDB'
   AND T.TYPE = 'T'
 ORDER BY S.NAME, T.NAME
  WITH UR;

SYSIBM.SYSDATABASE

One row per database (the Db2 database object, not the whole subsystem). Key columns:

• NAME — database name (DSNDB06 is the catalog database itself)
• CREATOR — owner
• STGROUP — default storage group for objects created in the database
• BPOOL — default buffer pool
• CREATEDTS — creation timestamp

Use SYSDATABASE when you ask “which databases exist?” or “what STOGROUP does this database default to?” Table-level questions still need SYSTABLES.

SYSIBM.SYSTABLESPACE

One row per table space. NAME is unique only within DBNAME, so always qualify both. Useful columns include TYPE (partitioning organization), NPARTS / PARTITIONS, PGSIZE, BPOOL, DSSIZE, COMPRESS, and RUNSTATS fields such as SPACEF and NACTIVE. CLONE indicates whether a clone table exists in the space even when NTABLES still looks like one table—IBM documents that clone pairing explicitly.

TYPE values you will see in teaching material include blank for older simple spaces, O for classic partitioned, G for partition-by-growth, and R for partition-by-range. Confirm the value against your release’s SQL Reference when you automate DROP impact reports; do not guess new codes.

1
2
3
4
5
SELECT DBNAME, NAME, PARTITIONS, PGSIZE, BPOOL, COMPRESS
  FROM SYSIBM.SYSTABLESPACE
 WHERE DBNAME = 'HRDB'
 ORDER BY NAME
  WITH UR;

SYSIBM.SYSTABLES

One row per table, view, alias, MQT, and several special table kinds. This is the usual starting point. NAME + CREATOR identify the object. DBNAME and TSNAME locate it. COLCOUNT, CREATEDTS, ALTEREDTS, STATSTIME, and REMARKS (COMMENT ON) round out the definition. CARD / CARDF and NPAGES are optimizer statistics, not a live row count.

Aliases (TYPE A) still occupy a SYSTABLES row. IBM documents that alias rows typically show DBNAME DSNDB06 and TSNAME SYSTSTAB, with TBCREATOR, TBNAME, and LOCATION pointing at the base table or view (LOCATION filled for remote three-part names). Never assume TYPE = 'T' when you meant “anything named EMPLOYEE.”

1
2
3
4
5
SELECT NAME, TYPE, DBNAME, TSNAME, COLCOUNT, STATSTIME
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'HR'
 ORDER BY TYPE, NAME
  WITH UR;

SYSIBM.SYSCOLUMNS

One row for every column of every table and view. Join on TBCREATOR and TBNAME. COLNO is the 1-based ordinal. COLTYPE is a short type name (INTEGER, VARCHAR, DECIMAL, TIMESTMP, BLOB, XML, DISTINCT, and others listed in the SQL Reference). LENGTH and SCALE describe precision; for LOB and ROWID, IBM tells you to use LENGTH2 for the maximum data length because LENGTH is the base-table occupancy, not the LOB payload size.

• NULLS — Y nullable, N NOT NULL
• DEFAULT — default specification
• KEYSEQ — position in the primary key, or 0 if not in the PK
• COLCARDF, HIGH2KEY, LOW2KEY — distribution statistics from RUNSTATS
• TYPESCHEMA / TYPENAME — distinct-type qualification when COLTYPE is DISTINCT

1
2
3
4
5
6
SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'DSN8D10'
   AND TBNAME = 'DEPT'
 ORDER BY COLNO
  WITH UR;

SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART

SYSINDEXES has one row per index. NAME + CREATOR identify the index; TBNAME + TBCREATOR identify the table. CLUSTERING, COLCOUNT, NLEAF, NLEVELS, FIRSTKEYCARD, FULLKEYCARD, and CLUSTERRATIOF are the optimizer’s view of the B-tree after RUNSTATS.

SYSINDEXPART has one row per index partition (partition 0 appears for non-partitioned indexes in many reports). Join IXCREATOR / IXNAME to SYSINDEXES. Use it for per-part SPACE, cardinality, and limit-key style operational questions—not as a substitute for SYSKEYS when you only need column order.

1
2
3
4
5
SELECT I.NAME, I.UNIQUERULE, I.CLUSTERING, I.COLCOUNT, I.NLEVELS
  FROM SYSIBM.SYSINDEXES I
 WHERE I.TBCREATOR = 'HR'
   AND I.TBNAME = 'EMPLOYEE'
  WITH UR;

SYSIBM.SYSKEYS and SYSIBM.SYSKEYCOLUSE

These two tables look similar and are not interchangeable.

• SYSKEYS — one row per column of an index key. Join IXNAME and IXCREATOR to SYSINDEXES. COLSEQ is position in the key (1 = first). ORDERING is A ascending or D descending. COLNAME / COLNO identify the table column.
• SYSKEYCOLUSE — one row per column of a unique constraint (including primary keys). CONSTNAME plus table qualification identify the constraint. This is the catalog table you want when the question is “which columns are in PRIMARY KEY PK_EMP?” rather than “which columns are in index XEMP1?”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Index key order
SELECT K.COLSEQ, K.COLNAME, K.ORDERING
  FROM SYSIBM.SYSINDEXES I
  JOIN SYSIBM.SYSKEYS K
    ON K.IXCREATOR = I.CREATOR
   AND K.IXNAME = I.NAME
 WHERE I.TBCREATOR = 'HR'
   AND I.TBNAME = 'EMPLOYEE'
   AND I.NAME = 'XEMP1'
 ORDER BY K.COLSEQ
  WITH UR;
 
-- Unique / primary constraint columns
SELECT CONSTNAME, COLSEQ, COLNAME
  FROM SYSIBM.SYSKEYCOLUSE
 WHERE TBCREATOR = 'HR'
   AND TBNAME = 'EMPLOYEE'
 ORDER BY CONSTNAME, COLSEQ
  WITH UR;

SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS

SYSRELS is one row per referential constraint. CREATOR and TBNAME are the dependent (child) table. REFTBCREATOR and REFTBNAME are the parent. RELNAME is the constraint name. IXNAME identifies the index used to support the relationship.

SYSFOREIGNKEYS is one row per column of that foreign key. Join CREATOR, TBNAME, and RELNAME. COLSEQ is the order of columns in the constraint. Together the pair answers both “who points at DEPARTMENT?” and “which child columns form the key?”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT R.RELNAME,
       R.CREATOR, R.TBNAME AS CHILD,
       R.REFTBCREATOR, R.REFTBNAME AS PARENT,
       R.DELETERULE,
       F.COLSEQ, F.COLNAME
  FROM SYSIBM.SYSRELS R
  JOIN SYSIBM.SYSFOREIGNKEYS F
    ON F.CREATOR = R.CREATOR
   AND F.TBNAME = R.TBNAME
   AND F.RELNAME = R.RELNAME
 WHERE R.REFTBCREATOR = 'HR'
   AND R.REFTBNAME = 'DEPARTMENT'
 ORDER BY R.RELNAME, F.COLSEQ
  WITH UR;

SYSIBM.SYSCHECKS

One row per table check constraint. You get the constraint name, the table (TBOWNER / TBNAME in IBM column naming), and the search condition text (CHECKCONDITION). Related tables such as SYSCHECKDEP record which columns the constraint references—use them when a DROP COLUMN impact report must find checks, not only FKs.

1
2
3
4
5
SELECT TBOWNER, TBNAME, CHECKNAME, CHECKCONDITION
  FROM SYSIBM.SYSCHECKS
 WHERE TBOWNER = 'HR'
   AND TBNAME = 'EMPLOYEE'
  WITH UR;

SYSIBM.SYSVIEWS

SYSTABLES TYPE V tells you a view exists. SYSVIEWS holds the view definition—the SELECT text CREATE VIEW stored, plus attributes such as whether the view is marked valid. Check IBM’s column list for your release for STATEMENT versus TEXT and any companion SYSVIEWS_STMT table when the definition is long.

To see what a view depends on, do not stop here: SYSVIEWDEP (next catalog page on dependencies) lists base tables, views, and other objects the view uses. That is the impact-analysis join when someone wants to DROP a table that might be under a view.

1
2
3
4
5
SELECT T.CREATOR, T.NAME, T.TYPE
  FROM SYSIBM.SYSTABLES T
 WHERE T.CREATOR = 'HR'
   AND T.TYPE = 'V'
  WITH UR;

Putting a table dossier together

A practical “describe this table” script hits SYSTABLES, SYSCOLUMNS, SYSINDEXES+SYSKEYS, SYSRELS+SYSFOREIGNKEYS, SYSCHECKS, and SYSTABAUTH (authorization page). Run each SELECT with WITH UR. Do not SELECT * in scheduled jobs: catalog tables are wide, and statement or check-condition columns can be large.

Explain It Like I'm Five

Think of a school. SYSDATABASE is the school building. SYSTABLESPACE is a classroom. SYSTABLES is the class roster (is it a real class, a nickname, or a window looking into another class?). SYSCOLUMNS is the list of subjects on each desk. SYSINDEXES is the labeled index tabs in the grade book. SYSKEYS tells you the order of those tabs. SYSRELS is the rule “you cannot be in Chess Club unless you are in this homeroom.” SYSCHECKS is “you must wear a badge.” SYSVIEWS is a window that shows only some of the desks without copying the desks.

Exercises

1. Write SQL to list every TYPE value currently used in SYSTABLES for CREATOR SYSIBM, with a count per TYPE.
2. For a table you know, join SYSINDEXES to SYSKEYS and print key columns in COLSEQ order. Identify UNIQUERULE.
3. Explain when you would query SYSKEYCOLUSE instead of SYSKEYS.
4. Find all child tables of a parent using SYSRELS and list DELETERULE for each.
5. Query SYSCOLUMNS for a LOB column and compare LENGTH with LENGTH2. What is each for?