Db2 catalog vs directory

Why Db2 splits catalog and directory

Relational engines need a place to remember what objects exist. On Db2 for z/OS that place is richer than a single dictionary file. IBM separates a queryable catalog from an internal directory so humans and tools can discover definitions while the engine still has a fast, controlled place for compiled and structural runtime artifacts.

When you issue CREATE TABLE, Db2 records the table in catalog tables and also updates database descriptors in the directory. When you BIND PACKAGE, catalog rows describe the package while the directory stores the skeleton package Db2 actually runs. When RUNSTATS finishes, statistics land in the catalog so the optimizer can see them. The two stores stay consistent because Db2 owns both updates inside its control path—not because applications write to them directly.

This split is a z/OS-specific teaching point. Db2 for Linux, UNIX, and Windows exposes catalog views and does not present a separate DSNDB01-style directory to administrators in the same way. If a LUW colleague asks “where is the directory?”, answer that on z/OS it is DSNDB01 and it is not the same thing as SYSCAT views.

The Db2 catalog (DSNDB06)

The catalog is the collection of tables in database DSNDB06, owned by schema SYSIBM. These are real base tables maintained by Db2. They describe databases, table spaces, tables, columns, indexes, views, routines, privileges, plans and packages at the descriptive level, image-copy and utility history, and statistics that influence access-path choices.

Installation creates the catalog. For the life of the subsystem, DDL such as CREATE, ALTER, and DROP; DCL such as GRANT and REVOKE; BIND and FREE; and utilities such as RUNSTATS and COPY keep catalog content current. You inventorie the subsystem by running SELECT statements—often through tools, scripts, or products that wrap those queries.

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

Catalog queries are how you answer “what tables does HR own?”, “which columns are nullable?”, and “who can UPDATE this table?”. They are also how monitoring and change-management tools build inventories. Treat the catalog as read-mostly for humans: you SELECT; Db2 writes.

What the catalog is not

• Not your application database — payroll rows live in user table spaces, not as the purpose of DSNDB06
• Not the optimizer’s only memory — runtime structures and caches also matter, and directory DBDs support physical mapping
• Not free to update by hand — unsupported catalog surgery can strand directory structures and break recovery

The Db2 directory (DSNDB01)

The directory lives in database DSNDB01. Classic teaching says you do not query it like SYSTABLES for day-to-day reporting. It exists so Db2 can find runtime structures quickly: how a database is laid out internally, how a bound package should execute, which log ranges matter for a table space, and which utilities are in flight.

DBD01 holds database descriptors. After DDL, Db2 must refresh how it understands databases and their objects; the DBD is that operational map. SPT01 and SCT02 hold skeleton packages and plans—the compiled forms FREE PACKAGE and FREE PLAN remove. SYSLGRNX tracks log ranges that recovery logic cares about. SYSUTILX tracks active utilities so restart and DISPLAY logic know what was running.

Modern Db2 releases expanded limited SQL access to some directory tables for diagnostics, but the mental model for beginners should stay clear: the directory is Db2’s private machinery, not a place to design application reports. Prefer documented DISPLAY, REPORT, and recovery procedures over DIY directory browsing until you know exactly why a IBM procedure asks for it.

Why directory health is critical

If catalog pages are damaged, you may still run many existing bound packages against user data for a time, but DDL, catalog reporting, and new binds suffer. If directory structures required for startup or execution are gone, Db2 may not start or cannot rebuild the internal state it needs. That is why shops duplex critical system data sets, take disciplined backups of catalog and directory, and treat restore order as a rehearsed playbook—not an experiment during an outage.

Catalog vs directory side by side

A useful slogan: the catalog tells you what exists; the directory helps Db2 work with what exists. Both must stay consistent. Utilities and BIND paths are written to keep them that way. Shortcuts that touch only one side create haunted subsystems.

How everyday actions touch both

• CREATE / ALTER / DROP — catalog rows change; DBDs and related directory content update
• BIND PACKAGE — package descriptive info in the catalog; skeleton package in SPT01
• RUNSTATS — statistics in catalog tables the optimizer reads
• COPY / RECOVER / REORG — catalog recovery history and directory log-range / utility state participate as designed

1
2
3
4
5
6
7
-- Human-facing discovery lives in the catalog:
SELECT NAME, PCTFREE, LOCKRULE
  FROM SYSIBM.SYSTABLESPACE
 WHERE DBNAME = 'APPDB';
 
-- You do not "SELECT the DBD" as an application pattern.
-- Db2 loads DBDs from the directory when it needs them.

Operational habits that keep both safe

Protect catalog and directory with the same seriousness you give active logs and the bootstrap data set. Use IBM-supported backup and recovery sequences. Run REORG on eligible catalog and directory table spaces when growth and performance guidance say so—knowing that some directory spaces (notably SYSUTILX) have special rules. Monitor sizes; runaway SPT01 growth often traces to package sprawl that FREE and versioning policies should address.

For developers, the practical rule is simpler: query the catalog, trust Db2 to maintain the directory, and escalate to DBAs when catalog queries disagree with what BIND or DISPLAY shows. Disagreement is a symptom, not an invitation to patch system tables with UPDATE.

Explain It Like I'm Five

Imagine a huge toy factory. The catalog is the big binder on the office desk that lists every toy name, who owns it, and what color it is—anyone allowed in the office can read the binder. The directory is the locked machine room with the special blueprints and robot programs the factory needs to actually build toys and restart after a power cut. Kids (applications) play with toys (user tables). Grown-ups read the binder. Only the factory robots use the locked blueprints. If you lose the binder, it is hard to inventorie and change the toy list. If you lose the machine-room blueprints, the factory may not start at all.

Exercises

1. Write from memory: catalog database name, directory database name, and one sentence on who queries each.
2. List three SYSIBM catalog tables and what business question each can answer.
3. Match DBD01, SPT01, SCT02, SYSLGRNX, and SYSUTILX to a one-line purpose.
4. Explain to a Db2 LUW friend why “directory” on z/OS is not the same as SYSCAT.
5. Why is unsupported UPDATE against SYSTABLES dangerous even if your SQLCODE is zero?