DB2 catalog overview and how to query it

What the catalog is

When you issue CREATE TABLE, Db2 does not only allocate a table space data set. It also inserts and updates rows that describe the table name, creator, columns, and indexes. That metadata is the catalog. It is system-managed: DDL, GRANT/REVOKE, BIND/FREE, RUNSTATS, and COPY keep it current inside the same unit of work as the change. If the CREATE fails, you do not get leftover catalog rows.

Catalog tables are real base tables, not LUW-style SYSCAT views. You SELECT them like any other table, provided you have privilege. You do not treat them as application data. Unsupported catalog surgery can disagree with the directory (DSNDB01) that Db2 uses at startup and runtime.

A useful first query is to ask the catalog about itself:

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

That row lives in database DSNDB06 (typically table space SYSTSTAB). Internal identifiers such as DBID and OBID show up in utility messages and log tools; HEX(DBID) is a common trick when you match DSN1LOGP output to an object.

Catalog table families

There are many SYSIBM tables. You do not memorize every column on day one. You learn families and the two or three tables you join for each job.

Start almost every inventory with SYSIBM.SYSTABLES. Filter TYPE so you do not mix tables, views, and aliases. Then join SYSCOLUMNS on TBCREATOR and TBNAME, SYSINDEXES on the same pair, and SYSTABLESPACE on DBNAME and TSNAME. Authorization work starts from SYSTABAUTH or SYSUSERAUTH, not from guessing RACF group names. Performance and hint work uses RUNSTATS columns plus the SYSQUERY family described below.

Later pages in this catalog section walk object tables, package and routine tables, authorization tables, and dependency/type tables in detail. Keep this overview as the map: pick the family, then pick the join keys (usually creator + name, or QUERYID).

How to query the catalog

Privileges and tools

You need SELECT on each catalog table you name (or an authority that includes it). Many shops grant DBAs SELECT on SYSIBM.* catalog tables and leave application IDs without that access. SPUFI, QMF, DSNTEP2, IBM Data Studio, and vendor catalog browsers all issue the same SQL underneath.

Always qualify and always isolate

Catalog names are not unique by NAME alone. Two creators can own EMPLOYEE. Always predicate on CREATOR (schema) and NAME. Isolation WITH UR is the usual default for reporting: you accept a tiny risk of seeing in-flight DDL in exchange for not locking catalog pages that BIND and utilities need.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Tables in one schema (TYPE T = base table)
SELECT NAME, DBNAME, TSNAME, COLCOUNT, CREATEDTS
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'HR'
   AND TYPE = 'T'
 ORDER BY NAME
  WITH UR;
 
-- Columns of one table
SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'HR'
   AND TBNAME = 'EMPLOYEE'
 ORDER BY COLNO
  WITH UR;

Habits that keep catalog SQL cheap

• Filter early — CREATOR, NAME, TYPE, DBNAME. Avoid SELECT * on wide catalog tables that include LOB statement text
• Join on documented keys — TBCREATOR/TBNAME, IXCREATOR/IXNAME, QUERYID. Accidental Cartesian products on SYSIBM tables are painfully slow
• Prefer CARDF/float statistics columns when INTEGER CARD has overflowed on large tables (IBM documents both generations of columns)
• Remember trailing blanks — many catalog CHAR columns are padded. Predicates with VARCHAR host variables can miss rows if you are sloppy about trimming
• Do not confuse LUW names — z/OS uses SYSIBM.SYSTABLES, not SYSCAT.TABLES

If your subsystem uses an access control authorization exit (often RACF classes for Db2), catalog GRANT tables may be incomplete for “who can really SELECT.” IBM documents that exception explicitly: catalog queries then describe Db2 GRANTs, not necessarily the exit’s decisions.

The SYSQUERY family (access-path repository)

Classic optimization hints were tied to a QUERYNO in PLAN_TABLE and were brittle when programmers inserted lines in a COBOL program (the precompiler’s default query number is often the source line number). Db2 later added a catalog access-path repository so statement-level hints and option overrides live in SYSIBM tables and match on statement identity, not only on a fragile number.

SYSIBM.SYSQUERY

Each SYSIBM.SYSQUERY row identifies an SQL statement. Db2 uses the row to influence access-path selection when a matching statement is optimized. Important columns include:

• QUERYID — unique BIGINT identity; join key to SYSQUERYPLAN and SYSQUERYOPTS
• QUERY_HASH / QUERY_SEC_HASH — hashes of statement text (and modified text) used to match statements
• QUERYNO — the query number associated with the row
• SOURCE — origin of the row (IBM documents 0 for statement-level optimization hints)
• USERFILTER — optional filter name that groups queries, or blank
• PLAN_VALID — blank if no path is stored but options exist in SYSQUERYOPTS; Y if SYSQUERYPLAN has a valid path; N if a stored path is invalid and not used
• ACCESSPATH_HINT — Y or N when a hint is specified and you can trust the flag; blank means you must look in SYSQUERYPLAN to see whether a path is specified
• OPTION_OVERRIDE — same pattern for SYSQUERYOPTS: Y, N, or blank meaning “go look”

1
2
3
4
5
SELECT QUERYID, QUERYNO, SCHEMA, PLAN_VALID,
       ACCESSPATH_HINT, OPTION_OVERRIDE, USERFILTER
  FROM SYSIBM.SYSQUERY
 ORDER BY QUERYID
  WITH UR;

Rows are not something you INSERT by hand in production. The usual path is: populate EXPLAIN input (PLAN_TABLE) and DSN_USERQUERY_TABLE, then run BIND QUERY. Db2 copies matching hint and option data into SYSQUERY, SYSQUERYPLAN, and SYSQUERYOPTS. FREE QUERY removes them. Enablement also depends on subsystem parameters such as OPTHINT and on whether the next BIND/REBIND or dynamic PREPARE is eligible to pick the hint up.

SYSIBM.SYSQUERYPLAN

SYSQUERYPLAN holds the plan hint information for queries in SYSQUERY—the catalog analogue of PLAN_TABLE rows that describe ACCESS_TYPE, MATCHCOLS, SORTN_UNIQ, and the rest of the access-path story. Correlate with QUERYID (and typically QUERYNO / QBLOCKNO / PLANNO as in EXPLAIN). If SYSQUERY.ACCESSPATH_HINT is blank, this is the table you query to see whether a path was actually stored.

1
2
3
4
5
6
7
8
SELECT Q.QUERYID, Q.QUERYNO, P.QBLOCKNO, P.PLANNO,
       P.METHOD, P.TNAME, P.ACCESSTYPE, P.MATCHCOLS
  FROM SYSIBM.SYSQUERY Q
  JOIN SYSIBM.SYSQUERYPLAN P
    ON P.QUERYID = Q.QUERYID
 WHERE Q.QUERYID = 12345
 ORDER BY P.QBLOCKNO, P.PLANNO
  WITH UR;

SYSIBM.SYSQUERYOPTS

SYSQUERYOPTS holds optimization parameters for a SYSQUERY statement when you want to override defaults without necessarily pinning a full access path: examples include REOPT behavior, parallelism degree, and star-join related options. OPTION_OVERRIDE on SYSQUERY tells you whether those overrides are in effect. BIND QUERY is again the supported way to get rows here.

SYSQUERYNO — the QUERYNO idea

IBM does not ship a standalone catalog table named SYSIBM.SYSQUERYNO in current Db2 for z/OS catalog lists. What shops mean by “SYSQUERYNO” is the QUERYNO identifier:

• A column on SYSQUERY, SYSPACKSTMT, SYSSTMT, and EXPLAIN tables such as PLAN_TABLE
• An optional QUERYNO integer clause on SELECT, INSERT, UPDATE, DELETE, MERGE, and related statements so the number stays stable when source lines move

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
  WITH UR
  QUERYNO 1000;

If you omit QUERYNO, Db2 often derives a number from the statement’s source line. Line numbers above 32767 can be reported as 0 in some EXPLAIN contexts, and inserting a paragraph in COBOL shifts later numbers. Statement-level hints in SYSQUERY reduce that pain by matching hashed statement text, but QUERYNO remains the glue for EXPLAIN correlation and for older PLAN_TABLE hints. Pick a numbering standard (for example start at 1000 and jump by 1000) so two statements never share a number inside one package.

SYSQUERYPROT — protecting a chosen path

There is likewise no IBM base catalog table named SYSIBM.SYSQUERYPROT in the published Db2 12/13 catalog list. “Query protection” in this family means: keep a chosen access path in effect and know when it is no longer valid.

• PLAN_VALID = Y — a path exists in SYSQUERYPLAN and is considered valid (including after the statement has used it)
• PLAN_VALID = N — a path is stored but invalid; Db2 will not use it
• ACCESSPATH_HINT — Y/N tell you a hint is specified and whether it is in effect; blank means inspect SYSQUERYPLAN
• Related catalog tables SYSQUERYSEL and SYSQUERYPREDICATE hold extended optimization (selectivity) data keyed by QUERYID

Protection is not magic armor against bad statistics. If you freeze a nested-loop plan and the inner table grows a hundred times, the hint can still be “valid” in the catalog sense while being a production problem. Revisit hints when RUNSTATS, DDL, or clustering changes. FREE QUERY when the hint is no longer wanted.

A beginner catalog-query toolkit

Put these three patterns on a cheat sheet. They answer most “what is out there?” questions before you need vendor tools.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 1) Object inventory
SELECT CREATOR, NAME, TYPE, DBNAME, TSNAME
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'HR'
  WITH UR;
 
-- 2) Package health
SELECT COLLID, NAME, VERSION, VALID, OPERATIVE, BINDTIME
  FROM SYSIBM.SYSPACKAGE
 WHERE COLLID = 'HRCOLL'
  WITH UR;
 
-- 3) Statement-level hints in the repository
SELECT QUERYID, QUERYNO, PLAN_VALID, ACCESSPATH_HINT, OPTION_OVERRIDE
  FROM SYSIBM.SYSQUERY
 WHERE PLAN_VALID <> ''
  WITH UR;

From here, the next pages zoom into object tables (SYSTABLES through SYSVIEWS), then packages, plans, and routines, then authorization tables, then dependencies and types. The catalog is large; the join keys stay small.

Explain It Like I'm Five

Imagine a giant toy box. The toys are your tables and programs. The catalog is the notebook taped to the lid that lists every toy: its name, color, who owns it, and which shelf it sits on. You can read the notebook with SQL. You do not scribble in it with a crayon—Db2 writes when you create or bind something. SYSQUERY is a special page in the notebook that says “when you build this puzzle (SQL statement), use these instructions (hints).” QUERYNO is the sticker number on that puzzle so you can find the same puzzle after someone moves the toys around.

Exercises

1. Query SYSIBM.SYSTABLES for CREATOR = 'SYSIBM' and count how many rows have TYPE = 'T' versus TYPE = 'V'.
2. Write a catalog SELECT that lists column names and COLTYPE for SYSIBM.SYSCOLUMNS of table SYSTABLES, ordered by COLNO, using WITH UR.
3. Explain in one paragraph why WITH UR is recommended for catalog reporting.
4. List the join key you would use between SYSQUERY and SYSQUERYPLAN, and what PLAN_VALID = 'N' means.
5. Add QUERYNO 5000 to a simple SELECT and then predict which catalog or EXPLAIN column will show 5000 after EXPLAIN or BIND.