Chapter 21: Catalog and Directory — Understanding DB2's Data About Your Data

Learning Objectives

By the end of this chapter, you will be able to:

1. Navigate the DB2 catalog tables and views on both z/OS and LUW platforms
2. Query the catalog for metadata about tables, columns, indexes, and tablespaces
3. Use catalog information for impact analysis and change management
4. Distinguish the DB2 directory [z/OS] from the catalog, and contrast it with the catalog-only approach on LUW
5. Build catalog-based automation scripts for routine DBA tasks
6. Create catalog queries tailored to the Meridian National Bank environment

Opening: The Database That Knows Everything About Itself

DB2 knows everything about your database. Every table you have ever created, every column in every table, every index, every foreign key, every privilege granted, every stored procedure compiled, every tablespace allocated. It knows the data types, the lengths, the nullability constraints, the default values. It knows which user created what, when statistics were last collected, how many rows the optimizer thinks exist, and how those rows are distributed across values.

All of this knowledge lives in one place: the catalog.

If you are a DBA, the catalog is not just a curiosity — it is your primary tool. It is the foundation of every impact analysis you will ever perform ("what happens if I drop this column?"), every automation script you will ever write ("generate DDL for all tables in this schema"), every health check you will ever run ("which tables have not had RUNSTATS in 90 days?"), and every security audit you will ever pass ("who has SELECT authority on the customer table?").

And yet, many DBAs — even experienced ones — treat the catalog as a black box. They know it exists. They might run the occasional query against SYSTABLES when they cannot remember a table name. But they never invest the time to learn the catalog systematically, to understand its structure, to build a personal toolkit of catalog queries that makes them faster and more accurate at their job.

This chapter changes that. By the time you finish, you will know the catalog as well as you know your own application schema. You will carry a mental map of the key catalog tables and the relationships between them. And you will have a battle-tested collection of queries — the Meridian Bank Catalog Toolkit — that you can adapt to any DB2 environment you ever manage.

Let us begin with a fundamental question: what, exactly, is the catalog?

21.1 What Is the DB2 Catalog?

The Metadata Repository Concept

Every relational database needs a way to describe itself. When you issue a CREATE TABLE statement, the database must record not just the data that will eventually be inserted into the table, but the definition of the table itself — its name, its schema, its columns, their data types, its constraints, the tablespace where it lives, who created it, and when. This information about information is called metadata, and the structure that stores it is the catalog.

The DB2 catalog is a set of tables (on z/OS) or views (on LUW) that contain metadata about every object in the database. The catalog is:

• System-managed: DB2 creates and maintains catalog entries automatically. When you execute a CREATE TABLE statement, DB2 inserts rows into the appropriate catalog tables. When you ALTER a column, DB2 updates the corresponding catalog rows. When you DROP an object, DB2 deletes the catalog entries. You never insert into or delete from the catalog directly under normal circumstances.

• Queryable via standard SQL: The catalog tables look like any other tables from a SQL perspective. You can SELECT from them, join them together, filter them with WHERE clauses, and aggregate them with GROUP BY. This is one of the most powerful features of the relational model — the metadata is stored in the same relational format as the data itself.

• Read-only for most purposes: While DB2 updates the catalog automatically, direct UPDATE statements against catalog tables are restricted. On z/OS, certain columns (primarily statistics columns) can be updated directly — this is how manual statistics insertion works. On LUW, the SYSCAT views are strictly read-only; the underlying SYSSTAT views allow statistics updates. Attempting to INSERT, UPDATE, or DELETE other catalog data directly is either prohibited or extremely dangerous.

• Always consistent: The catalog is maintained within the same transaction boundaries as the DDL operations that modify it. If a CREATE TABLE fails, no catalog entries are left behind. If an ALTER TABLE is rolled back, the catalog reflects the original state. This transactional consistency is not optional — it is fundamental to DB2's correctness.

Why the Catalog Matters to You

Consider the tasks you perform as a DBA or developer:

Every one of these tasks is a catalog query. The catalog is not a peripheral feature — it is the foundation of database administration.

Catalog vs. Data Dictionary

You will sometimes hear the terms "catalog" and "data dictionary" used interchangeably. Strictly speaking, a data dictionary is a broader concept that may include business metadata (data definitions, ownership, lineage) in addition to technical metadata. The DB2 catalog is a technical metadata repository. Some organizations build a separate data dictionary or data governance layer on top of the catalog, enriching it with business context. But the catalog itself is purely technical — it records what the database knows about its own objects, nothing more.

Check Your Understanding (Box 1)

Pause and answer these questions from memory before continuing:

1. What is the DB2 catalog, and what kind of information does it store?
2. Who maintains the catalog — the DBA or DB2 itself?
3. Can you query the catalog using standard SQL? Can you insert rows directly?

If you cannot answer all three, re-read Section 21.1 before moving on.

21.2 [z/OS] SYSIBM Catalog Tables

On DB2 for z/OS, the catalog consists of tables owned by the SYSIBM schema. These are actual base tables — not views — stored in a dedicated database called DSNDB06. The catalog database is created automatically when DB2 is installed and is maintained by DB2 throughout the life of the subsystem.

There are dozens of SYSIBM catalog tables, but a working DBA needs deep familiarity with approximately 15 to 20 of them. Here are the essential ones, grouped by function.

Object Definition Tables

SYSIBM.SYSTABLES — One row per table, view, alias, or materialized query table.

SYSTABLES is the starting point for almost every catalog exploration. If you want to know what exists, start here.

SYSIBM.SYSCOLUMNS — One row per column in every table and view.

SYSCOLUMNS is your go-to table for understanding the structure of any table. The statistics columns (COLCARDF, HIGH2KEY, LOW2KEY) are critical for the optimizer and for understanding data distribution.

SYSIBM.SYSINDEXES — One row per index.

SYSIBM.SYSKEYS — One row per column in each index key. Join with SYSINDEXES on IXNAME and IXCREATOR to get the column order.

Storage Tables

SYSIBM.SYSTABLESPACE — One row per tablespace.

SYSIBM.SYSDATABASE — One row per database.

Relationship Tables

SYSIBM.SYSRELS — One row per foreign key relationship.

SYSIBM.SYSFOREIGNKEYS — One row per column in each foreign key. Join with SYSRELS to get the full foreign key definition.

Authorization Tables

SYSIBM.SYSTABAUTH — Table-level privileges.

SYSIBM.SYSPACKAGE — One row per package (bound plan component).

This is not an exhaustive list — there are catalog tables for stored procedures (SYSROUTINES), triggers (SYSTRIGGERS), check constraints (SYSCHECKS), buffer pools (SYSBUFFERPOOL), and many more. But the tables above form the core that every z/OS DBA must know.

21.3 [z/OS] The DB2 Directory

The catalog tells you what objects exist and how they are defined. The directory tells DB2 how to work with those objects at runtime. This distinction is unique to DB2 for z/OS — DB2 for LUW has no separate directory.

What the Directory Contains

The DB2 directory is stored in a separate database called DSNDB01. Unlike the catalog (DSNDB06), which you query with standard SQL, the directory is managed internally by DB2 and is not directly queryable through normal SQL access. The directory contains:

DBD (Database Descriptor) — For every database, DB2 maintains an internal descriptor that maps the logical structure (tables, indexes, tablespaces) to the physical structure (VSAM data sets, page ranges, partition boundaries). When DB2 opens a tablespace, it consults the DBD to locate the underlying data sets. The DBD is the runtime representation of the information stored in the catalog's SYSTABLESPACE, SYSTABLES, and SYSINDEXES tables, optimized for fast access.

SPT01 (Skeleton Package Table) — Contains the executable form of SQL packages. When you BIND a package, DB2 compiles the SQL statements and stores the access paths (the optimizer's chosen execution plans) in SPT01. At runtime, DB2 reads the skeleton package from SPT01, fills in any variable values, and executes the plan. SPT01 is the z/OS equivalent of what LUW stores in its package cache and catalog package tables.

SCT02 (Skeleton Cursor Table) — Contains the executable form of plans (as opposed to packages). Plans are the older binding unit in DB2 for z/OS; packages are the modern approach. SCT02 serves the same purpose as SPT01 but for plans.

SYSLGRNX (Log Range Table) — Maps each object (tablespace partition, index space) to the range of log records that contain changes to that object. When DB2 needs to recover an object — for instance, during RECOVER TABLESPACE — it consults SYSLGRNX to determine which log records to read. Without SYSLGRNX, recovery would require scanning the entire log, which could take hours or days instead of minutes.

SYSUTILX (Utility Table) — Tracks the status of running and recently completed utilities. When you issue a DISPLAY UTILITY command, DB2 reads SYSUTILX.

Catalog vs. Directory: A Critical Distinction

The practical implication: if you lose the catalog, you cannot create or modify objects, but DB2 can still process existing SQL. If you lose the directory, DB2 cannot start. The directory is the more critical structure. This is why IBM recommends dual-copy protection for the directory datasets — BSDS (Bootstrap Data Set), log data sets, and directory data sets should all be duplexed.

When You Interact with the Directory

Most DBA interactions with the directory are indirect:

• BIND PACKAGE / BIND PLAN: Writes new access paths to SPT01/SCT02
• FREE PACKAGE / FREE PLAN: Removes entries from SPT01/SCT02
• RECOVER TABLESPACE: Reads SYSLGRNX to determine log ranges
• REPAIR DBD: Rebuilds a corrupted DBD from catalog information
• MODIFY RECOVERY: Cleans old recovery entries from SYSLGRNX

The REPAIR DBD command deserves special mention. If a DBD becomes corrupted (rare, but possible after certain failures), DB2 can reconstruct it from the catalog. This means the catalog is, in effect, the master definition — the directory is a derived runtime structure. Understanding this relationship helps you reason about recovery scenarios.

Check Your Understanding (Box 2)

1. What is the fundamental difference between the DB2 catalog and the DB2 directory on z/OS?
2. What happens to DB2 if the directory database (DSNDB01) is lost?
3. Which directory component stores the optimizer's chosen access paths?
4. Can a corrupted DBD be rebuilt? From what source?

21.4 [LUW] SYSCAT Views

On DB2 for LUW, the catalog is presented through a set of read-only views in the SYSCAT schema. These views are built on top of internal system tables in the SYSIBM schema, but IBM discourages direct access to the underlying SYSIBM tables on LUW because their structure may change between versions. The SYSCAT views provide a stable, documented interface.

There is also a SYSSTAT schema containing updatable views — these are the same metadata but limited to the statistics columns. You use SYSSTAT views when you want to manually update statistics (for example, to test optimizer behavior with different cardinality estimates).

Key SYSCAT Views

SYSCAT.TABLES — One row per table, view, alias, nickname, or MQT.

SYSCAT.COLUMNS — One row per column.

SYSCAT.INDEXES — One row per index.

SYSCAT.INDEXCOLUSE — One row per column in each index key. The LUW equivalent of SYSIBM.SYSKEYS on z/OS.

SYSCAT.TABLESPACES — One row per tablespace.

SYSCAT.REFERENCES — One row per foreign key relationship. The LUW equivalent of SYSIBM.SYSRELS.

SYSCAT.ROUTINES — One row per stored procedure, function, or method.

No Separate Directory on LUW

DB2 for LUW does not have a separate directory structure. The runtime information that z/OS stores in the directory — access paths, recovery ranges, utility status — is managed differently on LUW:

• Access paths: Stored in the package cache (in memory) and in SYSCAT.PACKAGES / SYSCAT.STATEMENTS (on disk)
• Recovery ranges: Managed through the recovery history file (db2rhist.asc) and internal tracking
• Utility status: Tracked in SYSIBMADM administrative views and the list utilities command

This architectural simplification is one of the reasons LUW administration is generally considered more approachable than z/OS administration — there is one fewer critical structure to understand and protect.

21.5 Querying for Table and Column Information

Now that you know what the catalog contains, let us put it to work. This section presents practical queries that you will use regularly as a DBA. Each query is shown in both z/OS (SYSIBM) and LUW (SYSCAT) syntax.

Find All Tables in a Schema

z/OS:

SELECT NAME, TYPE, CARD, NPAGES, CREATEDTS
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN'
  AND  TYPE    = 'T'
ORDER BY NAME;

LUW:

SELECT TABNAME, TYPE, CARD, NPAGES, CREATE_TIME
FROM   SYSCAT.TABLES
WHERE  TABSCHEMA = 'MERIDIAN'
  AND  TYPE      = 'T'
ORDER BY TABNAME;

The TYPE = 'T' filter excludes views, aliases, and MQTs. Remove it if you want to see everything in the schema.

Get Column Details for a Specific Table

z/OS:

SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT, KEYSEQ
FROM   SYSIBM.SYSCOLUMNS
WHERE  TBCREATOR = 'MERIDIAN'
  AND  TBNAME    = 'CUSTOMER'
ORDER BY COLNO;

LUW:

SELECT COLNO, COLNAME, TYPENAME, LENGTH, SCALE, NULLS, DEFAULT, KEYSEQ
FROM   SYSCAT.COLUMNS
WHERE  TABSCHEMA = 'MERIDIAN'
  AND  TABNAME   = 'CUSTOMER'
ORDER BY COLNO;

Find Large Tables (by Row Count)

z/OS:

SELECT CREATOR, NAME, CARD, NPAGES,
       DECIMAL(NPAGES * 4, 15, 0) AS SIZE_KB
FROM   SYSIBM.SYSTABLES
WHERE  TYPE = 'T'
  AND  CARD > 1000000
ORDER BY CARD DESC
FETCH FIRST 20 ROWS ONLY;

LUW:

SELECT TABSCHEMA, TABNAME, CARD, NPAGES, FPAGES,
       FPAGES * (PAGESIZE / 1024) AS SIZE_KB
FROM   SYSCAT.TABLES T
       JOIN SYSCAT.TABLESPACES TS ON T.TBSPACE = TS.TBSPACE
WHERE  T.TYPE = 'T'
  AND  T.CARD > 1000000
ORDER BY T.CARD DESC
FETCH FIRST 20 ROWS ONLY;

Find Tables with Stale Statistics

This query identifies tables where RUNSTATS has not been executed recently — a common source of poor optimizer decisions.

z/OS:

SELECT CREATOR, NAME, CARD, STATSTIME
FROM   SYSIBM.SYSTABLES
WHERE  TYPE = 'T'
  AND  CREATOR NOT IN ('SYSIBM', 'SYSPROC', 'SYSIBMTS')
  AND  (STATSTIME < CURRENT TIMESTAMP - 30 DAYS
        OR STATSTIME IS NULL)
ORDER BY STATSTIME NULLS FIRST;

LUW:

SELECT TABSCHEMA, TABNAME, CARD, STATS_TIME
FROM   SYSCAT.TABLES
WHERE  TYPE = 'T'
  AND  TABSCHEMA NOT LIKE 'SYS%'
  AND  (STATS_TIME < CURRENT TIMESTAMP - 30 DAYS
        OR STATS_TIME IS NULL)
ORDER BY STATS_TIME NULLS FIRST;

Find Tables Without Primary Keys

Tables without primary keys are a data modeling red flag — and a common finding in legacy environments.

z/OS:

SELECT T.CREATOR, T.NAME
FROM   SYSIBM.SYSTABLES T
WHERE  T.TYPE = 'T'
  AND  T.CREATOR = 'MERIDIAN'
  AND  NOT EXISTS (
         SELECT 1
         FROM   SYSIBM.SYSINDEXES I
         WHERE  I.TBCREATOR  = T.CREATOR
           AND  I.TBNAME     = T.NAME
           AND  I.UNIQUERULE = 'P'
       )
ORDER BY T.NAME;

LUW:

SELECT T.TABSCHEMA, T.TABNAME
FROM   SYSCAT.TABLES T
WHERE  T.TYPE = 'T'
  AND  T.TABSCHEMA = 'MERIDIAN'
  AND  NOT EXISTS (
         SELECT 1
         FROM   SYSCAT.INDEXES I
         WHERE  I.TABSCHEMA  = T.TABSCHEMA
           AND  I.TABNAME    = T.TABNAME
           AND  I.UNIQUERULE = 'P'
       )
ORDER BY T.TABNAME;

Search for a Column Across All Tables

"Which tables have a column named CUSTOMER_ID?" This question comes up constantly during impact analysis.

z/OS:

SELECT TBCREATOR, TBNAME, NAME, COLTYPE, LENGTH
FROM   SYSIBM.SYSCOLUMNS
WHERE  NAME = 'CUSTOMER_ID'
ORDER BY TBCREATOR, TBNAME;

LUW:

SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME, LENGTH
FROM   SYSCAT.COLUMNS
WHERE  COLNAME = 'CUSTOMER_ID'
ORDER BY TABSCHEMA, TABNAME;

This is the simplest form of impact analysis: before changing a column definition, find every table that uses it.

Check Your Understanding (Box 3)

1. Write a catalog query (for either platform) that finds all tables created in the last 7 days.
2. Why is filtering on TYPE = 'T' important when querying for tables?
3. What does a NULL STATSTIME / STATS_TIME value indicate?

21.6 Querying for Index Information

Indexes are the primary mechanism for query performance, and the catalog is the definitive source of truth about what indexes exist, what columns they cover, and how effective they are.

Find All Indexes on a Table

z/OS:

SELECT I.NAME, I.UNIQUERULE, I.CLUSTERING, I.COLCOUNT,
       I.NLEAF, I.NLEVELS, I.FULLKEYCARD, I.CLUSTERRATIOF
FROM   SYSIBM.SYSINDEXES I
WHERE  I.TBCREATOR = 'MERIDIAN'
  AND  I.TBNAME    = 'TRANSACTION'
ORDER BY I.NAME;

LUW:

SELECT I.INDNAME, I.UNIQUERULE, I.INDEXTYPE, I.COLCOUNT,
       I.NLEAF, I.NLEVELS, I.FULLKEYCARD, I.CLUSTERRATIO
FROM   SYSCAT.INDEXES I
WHERE  I.TABSCHEMA = 'MERIDIAN'
  AND  I.TABNAME   = 'TRANSACTION'
ORDER BY I.INDNAME;

Find Index Columns with Order

To see the full key definition, you need to join indexes with their key columns.

z/OS:

SELECT I.NAME AS INDEX_NAME, I.UNIQUERULE, I.CLUSTERING,
       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 = 'MERIDIAN'
  AND  I.TBNAME    = 'TRANSACTION'
ORDER BY I.NAME, K.COLSEQ;

LUW:

SELECT I.INDNAME, I.UNIQUERULE, I.INDEXTYPE,
       IC.COLSEQ, IC.COLNAME, IC.COLORDER
FROM   SYSCAT.INDEXES I
       JOIN SYSCAT.INDEXCOLUSE IC
         ON IC.INDSCHEMA = I.INDSCHEMA
        AND IC.INDNAME   = I.INDNAME
WHERE  I.TABSCHEMA = 'MERIDIAN'
  AND  I.TABNAME   = 'TRANSACTION'
ORDER BY I.INDNAME, IC.COLSEQ;

Identify Clustering Index Health

A low cluster ratio means the physical data order has diverged from the index order — a signal that REORG may be needed.

z/OS:

SELECT I.TBCREATOR, I.TBNAME, I.NAME AS INDEX_NAME,
       I.CLUSTERRATIOF AS CLUSTER_RATIO,
       T.CARD AS TABLE_ROWS,
       I.NLEAF AS INDEX_LEAF_PAGES
FROM   SYSIBM.SYSINDEXES I
       JOIN SYSIBM.SYSTABLES T
         ON T.CREATOR = I.TBCREATOR
        AND T.NAME    = I.TBNAME
WHERE  I.CLUSTERING = 'Y'
  AND  I.CLUSTERRATIOF < 0.80
  AND  T.CARD > 10000
ORDER BY I.CLUSTERRATIOF;

LUW:

SELECT I.TABSCHEMA, I.TABNAME, I.INDNAME,
       I.CLUSTERRATIO,
       T.CARD AS TABLE_ROWS,
       I.NLEAF AS INDEX_LEAF_PAGES
FROM   SYSCAT.INDEXES I
       JOIN SYSCAT.TABLES T
         ON T.TABSCHEMA = I.TABSCHEMA
        AND T.TABNAME   = I.TABNAME
WHERE  I.INDEXTYPE = 'CLUS'
  AND  I.CLUSTERRATIO BETWEEN 0 AND 80
  AND  T.CARD > 10000
ORDER BY I.CLUSTERRATIO;

Find Potentially Redundant Indexes

Two indexes with the same leading columns are often redundant. This query identifies candidates for review.

z/OS:

SELECT A.NAME AS INDEX_A, B.NAME AS INDEX_B,
       A.TBCREATOR, A.TBNAME,
       KA.COLNAME AS LEADING_COLUMN
FROM   SYSIBM.SYSINDEXES A
       JOIN SYSIBM.SYSKEYS KA
         ON KA.IXCREATOR = A.CREATOR AND KA.IXNAME = A.NAME AND KA.COLSEQ = 1
       JOIN SYSIBM.SYSINDEXES B
         ON B.TBCREATOR = A.TBCREATOR AND B.TBNAME = A.TBNAME AND B.NAME > A.NAME
       JOIN SYSIBM.SYSKEYS KB
         ON KB.IXCREATOR = B.CREATOR AND KB.IXNAME = B.NAME AND KB.COLSEQ = 1
WHERE  KA.COLNAME = KB.COLNAME
  AND  A.TBCREATOR = 'MERIDIAN'
ORDER BY A.TBNAME, KA.COLNAME;

This query finds pairs of indexes on the same table where the first key column is identical. These are candidates for consolidation — but review carefully before dropping, as the additional columns may serve different queries.

21.7 Querying for Relationship Information

Foreign key relationships define the logical structure of your database. The catalog stores every relationship, making it possible to trace dependency chains, perform impact analysis, and understand the data model without looking at DDL scripts.

Find All Foreign Keys from a Table (Child Relationships)

z/OS:

SELECT R.RELNAME AS FK_NAME,
       R.CREATOR AS CHILD_SCHEMA, R.TBNAME AS CHILD_TABLE,
       R.REFTBCREATOR AS PARENT_SCHEMA, R.REFTBNAME AS PARENT_TABLE,
       R.DELETERULE
FROM   SYSIBM.SYSRELS R
WHERE  R.CREATOR = 'MERIDIAN'
  AND  R.TBNAME  = 'TRANSACTION'
ORDER BY R.RELNAME;

LUW:

SELECT R.CONSTNAME AS FK_NAME,
       R.TABSCHEMA AS CHILD_SCHEMA, R.TABNAME AS CHILD_TABLE,
       R.REFTABSCHEMA AS PARENT_SCHEMA, R.REFTABNAME AS PARENT_TABLE,
       R.DELETERULE
FROM   SYSCAT.REFERENCES R
WHERE  R.TABSCHEMA = 'MERIDIAN'
  AND  R.TABNAME   = 'TRANSACTION'
ORDER BY R.CONSTNAME;

Find All Tables That Depend on a Parent Table

This is the critical impact analysis query: "if I change the CUSTOMER table, what other tables are affected?"

z/OS:

SELECT R.CREATOR AS CHILD_SCHEMA, R.TBNAME AS CHILD_TABLE,
       R.RELNAME AS FK_NAME,
       R.DELETERULE
FROM   SYSIBM.SYSRELS R
WHERE  R.REFTBCREATOR = 'MERIDIAN'
  AND  R.REFTBNAME    = 'CUSTOMER'
ORDER BY R.TBNAME;

LUW:

SELECT R.TABSCHEMA AS CHILD_SCHEMA, R.TABNAME AS CHILD_TABLE,
       R.CONSTNAME AS FK_NAME,
       R.DELETERULE
FROM   SYSCAT.REFERENCES R
WHERE  R.REFTABSCHEMA = 'MERIDIAN'
  AND  R.REFTABNAME   = 'CUSTOMER'
ORDER BY R.TABNAME;

Recursive Dependency Chain

Some schema changes cascade through multiple levels. A change to CUSTOMER might affect ACCOUNT, which affects TRANSACTION, which affects TRANSACTION_DETAIL. You can trace these chains with a recursive CTE.

z/OS:

WITH DEPENDENCY_CHAIN (LEVEL, CHILD_SCHEMA, CHILD_TABLE,
                       PARENT_SCHEMA, PARENT_TABLE) AS (
  -- Anchor: direct children of CUSTOMER
  SELECT 1, R.CREATOR, R.TBNAME, R.REFTBCREATOR, R.REFTBNAME
  FROM   SYSIBM.SYSRELS R
  WHERE  R.REFTBCREATOR = 'MERIDIAN'
    AND  R.REFTBNAME    = 'CUSTOMER'
  UNION ALL
  -- Recursive: children of children
  SELECT D.LEVEL + 1, R.CREATOR, R.TBNAME, R.REFTBCREATOR, R.REFTBNAME
  FROM   DEPENDENCY_CHAIN D
         JOIN SYSIBM.SYSRELS R
           ON R.REFTBCREATOR = D.CHILD_SCHEMA
          AND R.REFTBNAME    = D.CHILD_TABLE
  WHERE  D.LEVEL < 10
)
SELECT LEVEL, CHILD_SCHEMA, CHILD_TABLE, PARENT_SCHEMA, PARENT_TABLE
FROM   DEPENDENCY_CHAIN
ORDER BY LEVEL, CHILD_TABLE;

LUW:

WITH DEPENDENCY_CHAIN (LEVEL, CHILD_SCHEMA, CHILD_TABLE,
                       PARENT_SCHEMA, PARENT_TABLE) AS (
  SELECT 1, R.TABSCHEMA, R.TABNAME, R.REFTABSCHEMA, R.REFTABNAME
  FROM   SYSCAT.REFERENCES R
  WHERE  R.REFTABSCHEMA = 'MERIDIAN'
    AND  R.REFTABNAME   = 'CUSTOMER'
  UNION ALL
  SELECT D.LEVEL + 1, R.TABSCHEMA, R.TABNAME, R.REFTABSCHEMA, R.REFTABNAME
  FROM   DEPENDENCY_CHAIN D
         JOIN SYSCAT.REFERENCES R
           ON R.REFTABSCHEMA = D.CHILD_SCHEMA
          AND R.REFTABNAME   = D.CHILD_TABLE
  WHERE  D.LEVEL < 10
)
SELECT LEVEL, CHILD_SCHEMA, CHILD_TABLE, PARENT_SCHEMA, PARENT_TABLE
FROM   DEPENDENCY_CHAIN
ORDER BY LEVEL, CHILD_TABLE;

This recursive query produces a full dependency tree from any starting table. The LEVEL < 10 guard prevents runaway recursion in the event of circular references (which should not exist in a well-designed schema but can appear in poorly maintained systems).

View Dependency Analysis

Views depend on their base tables. If you alter a base table, views may become invalid.

z/OS:

SELECT D.BNAME AS BASE_TABLE, D.BCREATOR AS BASE_SCHEMA,
       D.DNAME AS VIEW_NAME, D.DCREATOR AS VIEW_SCHEMA
FROM   SYSIBM.SYSVIEWDEP D
WHERE  D.BCREATOR = 'MERIDIAN'
  AND  D.BNAME    = 'CUSTOMER'
ORDER BY D.DNAME;

LUW:

SELECT D.BSCHEMA AS BASE_SCHEMA, D.BNAME AS BASE_TABLE,
       D.TABSCHEMA AS VIEW_SCHEMA, D.TABNAME AS VIEW_NAME
FROM   SYSCAT.VIEWDEP D
WHERE  D.BSCHEMA = 'MERIDIAN'
  AND  D.BNAME   = 'CUSTOMER'
ORDER BY D.TABNAME;

21.8 Querying for Space and Storage Information

Space management is a core DBA responsibility. The catalog provides the raw data for understanding how much space each object uses, how that space is distributed, and where growth is happening.

Tablespace Overview

z/OS:

SELECT TS.NAME, TS.DBNAME, TS.TYPE, TS.NPARTS,
       TS.PGSIZE, TS.BPOOL,
       TS.SPACEF AS SPACE_KB,
       TS.NACTIVE AS ACTIVE_PAGES,
       TS.COMPRESS
FROM   SYSIBM.SYSTABLESPACE TS
WHERE  TS.DBNAME = 'MERIDDB'
ORDER BY TS.SPACEF DESC;

LUW:

SELECT TS.TBSPACE, TS.TBSPACETYPE, TS.DATATYPE,
       TS.PAGESIZE, TS.EXTENTSIZE, TS.BUFFERPOOLID
FROM   SYSCAT.TABLESPACES TS
ORDER BY TS.TBSPACE;

On LUW, detailed space usage requires the SYSIBMADM administrative views or the ADMIN_GET_TAB_INFO table function rather than the SYSCAT views alone:

SELECT TABSCHEMA, TABNAME,
       DATA_OBJECT_L_SIZE AS DATA_KB,
       INDEX_OBJECT_L_SIZE AS INDEX_KB,
       LONG_OBJECT_L_SIZE AS LONG_KB
FROM   SYSIBMADM.ADMINTABINFO
WHERE  TABSCHEMA = 'MERIDIAN'
ORDER BY DATA_OBJECT_L_SIZE DESC;

Partition-Level Space (z/OS)

For partitioned tablespaces on z/OS, SYSIBM.SYSTABLEPART provides partition-level detail:

SELECT TP.TSNAME, TP.PARTITION, TP.PQTY,
       TP.SPACEF AS SPACE_KB,
       TP.CARD AS PARTITION_ROWS,
       TP.LIMITKEY
FROM   SYSIBM.SYSTABLEPART TP
WHERE  TP.TSNAME = 'TSTRXN'
  AND  TP.DBNAME = 'MERIDDB'
ORDER BY TP.PARTITION;

This query shows you exactly how data is distributed across partitions — essential for identifying hot partitions, planning partition rotation, and deciding whether to rebalance.

Find Tables with High Free Space Percentage

Tables with a high percentage of free space relative to total space may be candidates for REORG to reclaim wasted DASD.

z/OS:

SELECT T.CREATOR, T.NAME, T.CARD,
       T.NPAGES AS USED_PAGES,
       TS.NACTIVE AS TOTAL_PAGES,
       CASE WHEN TS.NACTIVE > 0
            THEN DECIMAL((1.0 - DECIMAL(T.NPAGES, 15, 4) / TS.NACTIVE) * 100, 5, 1)
            ELSE 0
       END AS FREE_SPACE_PCT
FROM   SYSIBM.SYSTABLES T
       JOIN SYSIBM.SYSTABLESPACE TS
         ON TS.NAME   = T.TSNAME
        AND TS.DBNAME = T.DBNAME
WHERE  T.TYPE = 'T'
  AND  T.CREATOR = 'MERIDIAN'
  AND  TS.NACTIVE > 100
ORDER BY FREE_SPACE_PCT DESC;

Check Your Understanding (Box 4)

1. On z/OS, which catalog table stores partition-level space information?
2. On LUW, why do you need SYSIBMADM views for detailed space information rather than SYSCAT views alone?
3. What does a high free space percentage in a tablespace indicate?

21.9 Querying for Privilege Information

Security auditors love the catalog. Every GRANT and REVOKE operation is recorded, making the catalog the definitive source for answering "who can do what to which objects?"

Find All Privileges on a Table

z/OS:

SELECT GRANTEE, GRANTOR,
       SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH,
       ALTERAUTH, INDEXAUTH
FROM   SYSIBM.SYSTABAUTH
WHERE  TCREATOR = 'MERIDIAN'
  AND  TTNAME   = 'CUSTOMER'
ORDER BY GRANTEE;

LUW:

SELECT GRANTEE, GRANTEETYPE, GRANTOR,
       SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH,
       ALTERAUTH, INDEXAUTH, REFAUTH
FROM   SYSCAT.TABAUTH
WHERE  TABSCHEMA = 'MERIDIAN'
  AND  TABNAME   = 'CUSTOMER'
ORDER BY GRANTEE;

Find All Tables a User Can Access

z/OS:

SELECT TCREATOR AS TABLE_SCHEMA, TTNAME AS TABLE_NAME,
       SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH
FROM   SYSIBM.SYSTABAUTH
WHERE  GRANTEE = 'APPUSER1'
  AND  (SELECTAUTH IN ('Y','G') OR INSERTAUTH IN ('Y','G')
        OR UPDATEAUTH IN ('Y','G') OR DELETEAUTH IN ('Y','G'))
ORDER BY TCREATOR, TTNAME;

LUW:

SELECT TABSCHEMA, TABNAME,
       SELECTAUTH, INSERTAUTH, UPDATEAUTH, DELETEAUTH
FROM   SYSCAT.TABAUTH
WHERE  GRANTEE = 'APPUSER1'
  AND  (SELECTAUTH IN ('Y','G') OR INSERTAUTH IN ('Y','G')
        OR UPDATEAUTH IN ('Y','G') OR DELETEAUTH IN ('Y','G'))
ORDER BY TABSCHEMA, TABNAME;

Identify Users with Excessive Privileges

For security audits, you want to find users who have been granted authorities they probably should not have — such as ALTER or INDEX authority on production tables.

z/OS:

SELECT GRANTEE, TCREATOR, TTNAME,
       ALTERAUTH, INDEXAUTH
FROM   SYSIBM.SYSTABAUTH
WHERE  TCREATOR = 'MERIDIAN'
  AND  (ALTERAUTH IN ('Y','G') OR INDEXAUTH IN ('Y','G'))
  AND  GRANTEE NOT IN ('SYSADM', 'DBADM01', 'DBADM02')
ORDER BY GRANTEE, TTNAME;

Column-Level Privilege Auditing

DB2 supports column-level UPDATE privileges. If your security policy requires column-level access control (common in financial and healthcare environments), you need to audit SYSCOLAUTH.

z/OS:

SELECT GRANTEE, TCREATOR, TTNAME, NAME AS COLUMN_NAME,
       PRIVILEGE
FROM   SYSIBM.SYSCOLAUTH
WHERE  TCREATOR = 'MERIDIAN'
ORDER BY GRANTEE, TTNAME, NAME;

LUW:

SELECT GRANTEE, TABSCHEMA, TABNAME, COLNAME, PRIVTYPE
FROM   SYSCAT.COLAUTH
WHERE  TABSCHEMA = 'MERIDIAN'
ORDER BY GRANTEE, TABNAME, COLNAME;

Role Membership (LUW)

DB2 for LUW supports roles, which simplify privilege management. Query SYSCAT.ROLEAUTH to see role assignments:

SELECT ROLENAME, GRANTEE, GRANTEETYPE, ADMIN
FROM   SYSCAT.ROLEAUTH
WHERE  ROLENAME LIKE 'MERIDIAN%'
ORDER BY ROLENAME, GRANTEE;

21.10 Catalog-Based Automation

The true power of the catalog emerges when you use it to generate scripts, automate routine tasks, and build self-managing processes. This section demonstrates patterns that experienced DBAs use every day.

Generating DDL from the Catalog

When you need to recreate a table but do not have the original DDL script (a surprisingly common situation in legacy environments), you can construct it from catalog metadata.

z/OS — Generate CREATE TABLE:

SELECT 'CREATE TABLE ' || RTRIM(CREATOR) || '.' || RTRIM(NAME) || ' ('
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN' AND NAME = 'CUSTOMER'
UNION ALL
SELECT '  ' || RTRIM(C.NAME) || ' ' ||
       RTRIM(C.COLTYPE) ||
       CASE WHEN C.COLTYPE IN ('CHAR','VARCHAR','GRAPHIC','VARGRAPHIC')
            THEN '(' || RTRIM(CHAR(C.LENGTH)) || ')'
            WHEN C.COLTYPE = 'DECIMAL'
            THEN '(' || RTRIM(CHAR(C.LENGTH)) || ',' || RTRIM(CHAR(C.SCALE)) || ')'
            ELSE ''
       END ||
       CASE WHEN C.NULLS = 'N' THEN ' NOT NULL' ELSE '' END ||
       CASE WHEN C.COLNO < T.COLCOUNT THEN ',' ELSE '' END
FROM   SYSIBM.SYSCOLUMNS C
       JOIN SYSIBM.SYSTABLES T
         ON T.CREATOR = C.TBCREATOR AND T.NAME = C.TBNAME
WHERE  C.TBCREATOR = 'MERIDIAN' AND C.TBNAME = 'CUSTOMER'
ORDER BY C.COLNO
UNION ALL
SELECT ')  IN ' || RTRIM(DBNAME) || '.' || RTRIM(TSNAME) || ';'
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN' AND NAME = 'CUSTOMER';

This is a simplified version — a production DDL generator would also handle primary keys, check constraints, foreign keys, default values, GENERATED ALWAYS columns, and tablespace attributes. But the principle is the same: the catalog contains everything you need to reconstruct any object.

On LUW, the db2look utility generates DDL from the catalog automatically and is the preferred approach. But understanding how to construct DDL from SYSCAT views is valuable when you need partial extracts or customized output.

Automated Impact Analysis

Before any schema change, you should know exactly what will be affected. Here is a comprehensive impact analysis query for a column change:

z/OS — "What depends on CUSTOMER.CUSTOMER_ID?":

-- Tables with the same column name (potential joins)
SELECT 'TABLE_COLUMN' AS DEP_TYPE, TBCREATOR, TBNAME, NAME
FROM   SYSIBM.SYSCOLUMNS
WHERE  NAME = 'CUSTOMER_ID'
  AND  NOT (TBCREATOR = 'MERIDIAN' AND TBNAME = 'CUSTOMER')

UNION ALL

-- Foreign keys referencing the parent table
SELECT 'FOREIGN_KEY', CREATOR, TBNAME, RELNAME
FROM   SYSIBM.SYSRELS
WHERE  REFTBCREATOR = 'MERIDIAN' AND REFTBNAME = 'CUSTOMER'

UNION ALL

-- Views depending on the table
SELECT 'VIEW', DCREATOR, DNAME, BNAME
FROM   SYSIBM.SYSVIEWDEP
WHERE  BCREATOR = 'MERIDIAN' AND BNAME = 'CUSTOMER'

UNION ALL

-- Packages that reference the table
SELECT 'PACKAGE', DCREATOR, DNAME, BNAME
FROM   SYSIBM.SYSPACKDEP
WHERE  BCREATOR = 'MERIDIAN' AND BNAME = 'CUSTOMER'
  AND  BTYPE = 'T'

UNION ALL

-- Triggers on the table
SELECT 'TRIGGER', SCHEMA, NAME, TBNAME
FROM   SYSIBM.SYSTRIGGERS
WHERE  TBCREATOR = 'MERIDIAN' AND TBNAME = 'CUSTOMER'

ORDER BY 1, 2, 3;

This single query shows you every table with the same column, every foreign key, every view, every compiled package, and every trigger that touches the CUSTOMER table. Run it before any ALTER TABLE and you will never be surprised by cascading failures.

Change Detection Script

You can use the catalog to detect changes between two points in time. This is useful for auditing and change management.

z/OS — Tables created or altered in the last 7 days:

SELECT CREATOR, NAME, 'CREATED' AS CHANGE_TYPE, CREATEDTS AS CHANGE_TIME
FROM   SYSIBM.SYSTABLES
WHERE  CREATEDTS > CURRENT TIMESTAMP - 7 DAYS
  AND  TYPE = 'T'
UNION ALL
SELECT CREATOR, NAME, 'ALTERED', ALTEREDTS
FROM   SYSIBM.SYSTABLES
WHERE  ALTEREDTS > CURRENT TIMESTAMP - 7 DAYS
  AND  TYPE = 'T'
  AND  ALTEREDTS > CREATEDTS
ORDER BY CHANGE_TIME DESC;

RUNSTATS Scheduling Based on Volatility

Highly volatile tables need more frequent RUNSTATS than relatively static reference tables. You can use catalog statistics to prioritize.

z/OS:

SELECT T.CREATOR, T.NAME, T.CARD,
       T.STATSTIME,
       DAYS(CURRENT TIMESTAMP) - DAYS(T.STATSTIME) AS DAYS_SINCE_STATS,
       CASE WHEN T.CARD > 10000000 THEN 'HIGH'
            WHEN T.CARD > 1000000  THEN 'MEDIUM'
            ELSE 'LOW'
       END AS PRIORITY
FROM   SYSIBM.SYSTABLES T
WHERE  T.TYPE = 'T'
  AND  T.CREATOR = 'MERIDIAN'
  AND  T.STATSTIME < CURRENT TIMESTAMP - 7 DAYS
ORDER BY T.CARD DESC;

A production version of this script would also consider the rate of INSERT/UPDATE/DELETE activity (available from real-time statistics on z/OS, discussed in Section 21.11) to determine true volatility rather than relying on table size as a proxy.

Check Your Understanding (Box 5)

1. Name three types of dependencies that a comprehensive impact analysis query should check.
2. Why is generating DDL from the catalog useful in legacy environments?
3. How would you use the catalog to prioritize RUNSTATS scheduling?

21.11 Real-Time Statistics [z/OS]

Traditional catalog statistics are point-in-time snapshots captured by RUNSTATS. Between executions of RUNSTATS, the catalog statistics grow increasingly stale. DB2 for z/OS addresses this with Real-Time Statistics (RTS) — a feature that continuously updates space and status information as data changes occur.

The RTS Tables

Real-Time Statistics are stored in two catalog tables:

SYSIBM.SYSTABLESPACESTATS — Real-time tablespace statistics.

SYSIBM.SYSINDEXSPACESTATS — Real-time index space statistics.

Why RTS Matters

Real-Time Statistics transform three critical DBA workflows:

1. Intelligent REORG scheduling. Instead of reorganizing tablespaces on a fixed calendar schedule ("REORG everything every Sunday"), you can use RTS to reorganize only the objects that need it:

SELECT SS.DBNAME, SS.NAME, SS.PARTITION,
       SS.TOTALROWS,
       SS.REORGINSERTS + SS.REORGDELETES + SS.REORGUPDATES AS CHANGES_SINCE_REORG,
       SS.REORGLASTTIME
FROM   SYSIBM.SYSTABLESPACESTATS SS
WHERE  SS.DBNAME = 'MERIDDB'
  AND  (SS.REORGINSERTS + SS.REORGDELETES + SS.REORGUPDATES) >
       SS.TOTALROWS * 0.20
ORDER BY (SS.REORGINSERTS + SS.REORGDELETES + SS.REORGUPDATES) DESC;

This query finds tablespace partitions where the total changes since the last REORG exceed 20% of the total rows — a common threshold for triggering reorganization.

2. Intelligent COPY scheduling. You can schedule image copies based on actual change volume rather than a fixed calendar:

SELECT SS.DBNAME, SS.NAME, SS.PARTITION,
       SS.COPYCHANGES,
       SS.COPYLASTTIME,
       DAYS(CURRENT TIMESTAMP) - DAYS(SS.COPYLASTTIME) AS DAYS_SINCE_COPY
FROM   SYSIBM.SYSTABLESPACESTATS SS
WHERE  SS.DBNAME = 'MERIDDB'
  AND  (SS.COPYCHANGES > 10000
        OR DAYS(CURRENT TIMESTAMP) - DAYS(SS.COPYLASTTIME) > 7)
ORDER BY SS.COPYCHANGES DESC;

3. Intelligent RUNSTATS scheduling. As mentioned in Section 21.10, RTS provides the true change count needed to schedule RUNSTATS based on actual data volatility:

SELECT SS.DBNAME, SS.NAME, SS.PARTITION,
       SS.TOTALROWS,
       SS.STATSINSERTS + SS.STATSDELETES + SS.STATSUPDATES AS CHANGES_SINCE_STATS,
       SS.STATSLASTTIME,
       CASE WHEN SS.TOTALROWS > 0
            THEN DECIMAL(
              (SS.STATSINSERTS + SS.STATSDELETES + SS.STATSUPDATES) * 100.0
              / SS.TOTALROWS, 7, 2)
            ELSE 0
       END AS CHANGE_PERCENT
FROM   SYSIBM.SYSTABLESPACESTATS SS
WHERE  SS.DBNAME = 'MERIDDB'
  AND  (SS.STATSINSERTS + SS.STATSDELETES + SS.STATSUPDATES) >
       CASE WHEN SS.TOTALROWS > 1000000 THEN SS.TOTALROWS * 0.10
            ELSE SS.TOTALROWS * 0.20
       END
ORDER BY CHANGE_PERCENT DESC;

This query applies different thresholds for large tables (10% change) versus smaller tables (20% change), reflecting the fact that even a small percentage change in a very large table represents significant statistical shift.

RTS and the Optimizer

Starting with DB2 10 for z/OS, the optimizer can use RTS information to adjust its estimates when catalog statistics are stale. If RUNSTATS was last collected with 1 million rows but RTS shows 5 million rows due to subsequent inserts, the optimizer can factor in the growth. This is not a replacement for regular RUNSTATS — the optimizer still needs full column distribution statistics — but it provides a safety net against the worst cases of stale statistics.

21.12 The Meridian Bank Catalog Toolkit

Throughout this chapter, you have seen individual catalog queries. Now let us assemble them into a cohesive toolkit — a set of utility queries that a Meridian National Bank DBA would run as part of their daily routine.

Toolkit Query 1: Daily Health Dashboard

This query produces a one-screen summary of the Meridian database health.

z/OS:

SELECT 'Total Tables' AS METRIC,
       CHAR(COUNT(*)) AS VALUE
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN' AND TYPE = 'T'

UNION ALL

SELECT 'Total Indexes',
       CHAR(COUNT(*))
FROM   SYSIBM.SYSINDEXES
WHERE  TBCREATOR = 'MERIDIAN'

UNION ALL

SELECT 'Tables Without PK',
       CHAR(COUNT(*))
FROM   SYSIBM.SYSTABLES T
WHERE  T.CREATOR = 'MERIDIAN' AND T.TYPE = 'T'
  AND  NOT EXISTS (
         SELECT 1 FROM SYSIBM.SYSINDEXES I
         WHERE  I.TBCREATOR = T.CREATOR
           AND  I.TBNAME = T.NAME
           AND  I.UNIQUERULE = 'P')

UNION ALL

SELECT 'Stale Stats (>30 days)',
       CHAR(COUNT(*))
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN' AND TYPE = 'T'
  AND  (STATSTIME < CURRENT TIMESTAMP - 30 DAYS
        OR STATSTIME IS NULL)

UNION ALL

SELECT 'Clustering Ratio < 80%',
       CHAR(COUNT(*))
FROM   SYSIBM.SYSINDEXES
WHERE  TBCREATOR = 'MERIDIAN'
  AND  CLUSTERING = 'Y'
  AND  CLUSTERRATIOF < 0.80
  AND  CLUSTERRATIOF > 0;

Toolkit Query 2: Top 10 Largest Tables

SELECT T.NAME, T.CARD,
       T.NPAGES,
       DECIMAL(T.NPAGES * TS.PGSIZE / 1024.0 / 1024.0, 12, 2) AS SIZE_MB,
       T.STATSTIME
FROM   SYSIBM.SYSTABLES T
       JOIN SYSIBM.SYSTABLESPACE TS
         ON TS.NAME = T.TSNAME AND TS.DBNAME = T.DBNAME
WHERE  T.CREATOR = 'MERIDIAN'
  AND  T.TYPE = 'T'
ORDER BY T.NPAGES DESC
FETCH FIRST 10 ROWS ONLY;

Toolkit Query 3: Index Health Check

SELECT I.NAME AS INDEX_NAME, I.TBNAME,
       I.NLEAF, I.NLEVELS,
       I.CLUSTERRATIOF AS CLUSTER_RATIO,
       I.FULLKEYCARD AS DISTINCT_KEYS,
       T.CARD AS TABLE_ROWS,
       CASE WHEN T.CARD > 0
            THEN DECIMAL(I.FULLKEYCARD * 100.0 / T.CARD, 7, 2)
            ELSE 0
       END AS SELECTIVITY_PCT,
       I.STATSTIME
FROM   SYSIBM.SYSINDEXES I
       JOIN SYSIBM.SYSTABLES T
         ON T.CREATOR = I.TBCREATOR AND T.NAME = I.TBNAME
WHERE  I.TBCREATOR = 'MERIDIAN'
ORDER BY I.CLUSTERRATIOF;

Toolkit Query 4: Identify Potentially Unused Indexes

An index that the optimizer never chooses is consuming space, slowing INSERT/UPDATE/DELETE operations, and adding REORG overhead for no benefit. While the catalog alone cannot definitively tell you an index is unused (you need access path monitoring for that), you can identify suspicious candidates.

z/OS — Indexes not referenced by any package:

SELECT I.CREATOR, I.NAME, I.TBNAME,
       I.NLEAF AS LEAF_PAGES,
       I.CREATEDTS
FROM   SYSIBM.SYSINDEXES I
WHERE  I.TBCREATOR = 'MERIDIAN'
  AND  NOT EXISTS (
         SELECT 1
         FROM   SYSIBM.SYSPACKDEP PD
         WHERE  PD.BCREATOR = I.CREATOR
           AND  PD.BNAME    = I.NAME
           AND  PD.BTYPE    = 'I'
       )
  AND  I.UNIQUERULE <> 'P'   -- Do not flag primary key indexes
ORDER BY I.NLEAF DESC;

This query finds indexes that are not referenced in any package dependency. This does not guarantee the index is truly unused — dynamic SQL will not appear in SYSPACKDEP — but it is a strong signal that the index deserves investigation.

Toolkit Query 5: Privilege Review

SELECT A.GRANTEE,
       COUNT(CASE WHEN A.SELECTAUTH IN ('Y','G') THEN 1 END) AS SELECT_COUNT,
       COUNT(CASE WHEN A.INSERTAUTH IN ('Y','G') THEN 1 END) AS INSERT_COUNT,
       COUNT(CASE WHEN A.UPDATEAUTH IN ('Y','G') THEN 1 END) AS UPDATE_COUNT,
       COUNT(CASE WHEN A.DELETEAUTH IN ('Y','G') THEN 1 END) AS DELETE_COUNT,
       COUNT(CASE WHEN A.ALTERAUTH  IN ('Y','G') THEN 1 END) AS ALTER_COUNT
FROM   SYSIBM.SYSTABAUTH A
WHERE  A.TCREATOR = 'MERIDIAN'
GROUP BY A.GRANTEE
ORDER BY ALTER_COUNT DESC, DELETE_COUNT DESC;

This provides a per-user summary of how many tables each user can access. Users with ALTER or DELETE authority on many tables deserve scrutiny during security reviews.

Toolkit Query 6: Schema Change History

SELECT 'TABLE CREATED' AS EVENT,
       CREATOR || '.' || NAME AS OBJECT,
       CREATEDTS AS EVENT_TIME
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN'
  AND  CREATEDTS > CURRENT TIMESTAMP - 30 DAYS

UNION ALL

SELECT 'TABLE ALTERED',
       CREATOR || '.' || NAME,
       ALTEREDTS
FROM   SYSIBM.SYSTABLES
WHERE  CREATOR = 'MERIDIAN'
  AND  ALTEREDTS > CURRENT TIMESTAMP - 30 DAYS
  AND  ALTEREDTS > CREATEDTS

UNION ALL

SELECT 'INDEX CREATED',
       CREATOR || '.' || NAME || ' ON ' || TBNAME,
       CREATEDTS
FROM   SYSIBM.SYSINDEXES
WHERE  TBCREATOR = 'MERIDIAN'
  AND  CREATEDTS > CURRENT TIMESTAMP - 30 DAYS

ORDER BY EVENT_TIME DESC;

This query builds a chronological audit trail of all schema changes in the Meridian schema over the past 30 days. Adjust the time window as needed for your change-management review cycle.

Spaced Review: Concepts from Earlier Chapters

This section revisits concepts from Chapters 11, 14, and 17 to reinforce your long-term retention through spaced repetition.

From Chapter 11 (Data Definition Language)

Review Question 1: When you issue a CREATE TABLE statement, what happens internally? Think about both the data side (the tablespace where row data will live) and the metadata side (the catalog entries DB2 creates). Now that you have studied the catalog in detail, you can describe the metadata side with precision. Which catalog tables receive new rows when a CREATE TABLE completes?

Answer: At minimum, SYSTABLES receives a new row, SYSCOLUMNS receives one row per column, and if a primary key is specified, SYSINDEXES and SYSKEYS receive entries for the primary key index. If the table is in a new tablespace, SYSTABLESPACE also receives a row.

Review Question 2: In Chapter 11, you learned that ALTER TABLE ADD COLUMN is an online operation on most platforms. What happens in the catalog when a column is added? Which column in SYSTABLES changes?

Answer: SYSCOLUMNS receives a new row for the added column. SYSTABLES.COLCOUNT increments by one. SYSTABLES.ALTEREDTS is updated to the current timestamp.

From Chapter 14 (Physical Database Design)

Review Question 3: Chapter 14 discussed the relationship between page size, row length, and rows per page. How would you use the catalog to calculate the average row length for a table?

Answer: Sum the lengths from SYSCOLUMNS for each column, accounting for the overhead of variable-length columns and null indicators. Alternatively, use SYSTABLES.CARD and SYSTABLES.NPAGES: average_row_length is approximately (NPAGES * page_size) / CARD.

Review Question 4: Chapter 14 emphasized the importance of buffer pool assignment. How would you use the catalog to find which buffer pool each of Meridian's tablespaces uses?

Answer: Query SYSTABLESPACE.BPOOL WHERE DBNAME = 'MERIDDB'.

From Chapter 17 (DB2 Utilities)

Review Question 5: Chapter 17 covered RUNSTATS as a critical utility. Now that you understand the catalog, explain exactly what RUNSTATS does in terms of catalog updates.

Answer: RUNSTATS reads the data in a tablespace or index and writes statistical summaries to the catalog. For tables, it updates SYSTABLES.CARD, SYSTABLES.NPAGES, SYSTABLES.STATSTIME, and column-level statistics in SYSCOLUMNS (COLCARDF, HIGH2KEY, LOW2KEY). For indexes, it updates SYSINDEXES fields such as NLEAF, NLEVELS, FIRSTKEYCARD, FULLKEYCARD, and CLUSTERRATIOF.

Review Question 6: Chapter 17 discussed REORG. How could you use the catalog (and RTS) to determine which tables need REORG, rather than reorganizing on a fixed calendar?

Answer: Use SYSIBM.SYSTABLESPACESTATS to compare REORGINSERTS + REORGDELETES + REORGUPDATES against TOTALROWS. When the ratio exceeds a threshold (commonly 20%), schedule a REORG. Also check SYSINDEXES.CLUSTERRATIOF — when the clustering ratio drops below 80%, reorganization restores physical order.

Chapter Summary

The DB2 catalog is the metadata repository that makes database administration possible. It stores the definition of every object, the statistics the optimizer uses to choose access paths, the privilege grants that control security, and the dependency relationships that enable impact analysis. On z/OS, the catalog lives in SYSIBM tables within DSNDB06, supplemented by the directory in DSNDB01. On LUW, the catalog is accessed through SYSCAT views.

The key SYSIBM/SYSCAT structures you must know:

• Tables: SYSTABLES / SYSCAT.TABLES
• Columns: SYSCOLUMNS / SYSCAT.COLUMNS
• Indexes: SYSINDEXES / SYSCAT.INDEXES
• Index keys: SYSKEYS / SYSCAT.INDEXCOLUSE
• Relationships: SYSRELS / SYSCAT.REFERENCES
• Tablespaces: SYSTABLESPACE / SYSCAT.TABLESPACES
• Privileges: SYSTABAUTH / SYSCAT.TABAUTH
• Packages: SYSPACKAGE / SYSCAT.PACKAGES

The z/OS directory (DSNDB01) stores runtime structures that the catalog does not: the DBD (database descriptor), SPT01 (skeleton package table), SCT02 (skeleton cursor table), and SYSLGRNX (log range table). The directory is more critical than the catalog for DB2 startup — losing the directory prevents DB2 from starting, while losing the catalog prevents only DDL operations and new binds.

Real-Time Statistics on z/OS transform DBA workflows by providing continuous updates on space usage and data change volumes. They enable intelligent scheduling of REORG, COPY, and RUNSTATS based on actual need rather than fixed calendars.

The Meridian Bank Catalog Toolkit demonstrates how to combine catalog queries into a practical DBA workflow: daily health dashboards, space monitoring, index health checks, unused index detection, privilege reviews, and schema change auditing. These queries are not academic exercises — they are the foundation of effective database administration.

Learn the catalog. Query it daily. Build your own toolkit. The DBA who knows the catalog intimately is the DBA who catches problems before they become incidents.

Return to Part IV: Administration Fundamentals | Continue to Exercises