When a COBOL Db2 job fails with an object, privilege, or package problem, the catalog is often the fastest place to confirm the facts. It can show whether a table exists, which columns it has, which indexes support it, when statistics were collected, and which package or plan is tied to application SQL.
The Db2 catalog is a set of Db2 tables, mostly under the SYSIBM schema, that records metadata about objects, authorizations, packages, plans, constraints, routines, communications, and optimizer statistics. Db2 updates many catalog rows when DDL, DCL, bind, or utility work changes the system.
Catalog versus directory
The catalog and directory are both used by Db2, but they serve different support roles. The catalog is queryable through SQL and is useful for developers, DBAs, and support teams. The directory contains internal control information that Db2 uses to run and recover the subsystem; it is not a normal application query target.
| Area | What it contains | How support teams use it |
|---|---|---|
| Db2 catalog | Metadata about tables, columns, indexes, views, privileges, packages, plans, routines, constraints, and statistics. | Query with SQL to investigate object definitions, authorization, bind status, and access-path inputs. |
| Db2 directory | Internal Db2 control information needed for operation and recovery. | Managed by Db2 and DBA utilities; do not treat it as a normal reporting source. |
Catalog tables developers often use
| Catalog table | Typical question it answers |
|---|---|
SYSIBM.SYSTABLES | Does this table, view, alias, or synonym exist, and who owns it? |
SYSIBM.SYSCOLUMNS | What are the column names, data types, lengths, null rules, and column order? |
SYSIBM.SYSINDEXES | Which indexes exist for a table, and are they unique or clustering indexes? |
SYSIBM.SYSKEYS | Which columns form an index key, and in what order? |
SYSIBM.SYSTABLESPACE | Which table spaces exist, and which database owns them? |
SYSIBM.SYSTABAUTH | Which table or view privileges are granted? |
SYSIBM.SYSUSERAUTH | Which system-level authorities are recorded for an authorization ID? |
SYSIBM.SYSPACKAGE | Which packages exist for an application, collection, or version? |
SYSIBM.SYSPACKSTMT | Which SQL statements are associated with a package? |
SYSIBM.SYSROUTINES | Which stored procedures and user-defined functions exist? |
Catalog queries for daily support
Find columns for a table
SELECT NAME,
COLTYPE,
LENGTH,
NULLS,
COLNO
FROM SYSIBM.SYSCOLUMNS
WHERE TBOWNER = 'APP1'
AND TBNAME = 'ACCOUNT'
ORDER BY COLNO;This is a quick check before changing a COBOL copybook, DCLGEN member, or host variable definition.
List indexes for a table
SELECT I.NAME,
I.CREATOR,
I.UNIQUERULE,
I.CLUSTERING
FROM SYSIBM.SYSINDEXES I
WHERE I.TBCREATOR = 'APP1'
AND I.TBNAME = 'ACCOUNT'
ORDER BY I.NAME;Use this before reviewing an access path or explaining why a predicate is not using the expected index.
Check package existence
SELECT COLLID,
NAME,
VERSION,
VALID,
OPERATIVE
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'APP1COLL'
AND NAME = 'ACCTUPD';This helps when a runtime failure points to a missing, invalid, or wrong collection package.
Catalog data used by the optimizer
Db2 uses catalog statistics when it chooses access paths for static and dynamic SQL. RUNSTATS updates statistics such as table cardinality, index cardinality, column distribution, and partition-level information. Stale statistics can lead Db2 to pick a poor access path even when the SQL text has not changed.
| Catalog area | Why it matters |
|---|---|
SYSTABLES and related statistics rows | Table size and organization influence access-path choice. |
SYSINDEXES and key statistics | Index availability, uniqueness, and clustering affect predicate access. |
SYSCOLDIST | Column distribution data can help Db2 estimate filter factors for skewed values. |
| History statistics tables | Useful for comparing recent statistics changes when a query changed behavior after maintenance. |
Catalog safety rules
- Use catalog SELECT queries for investigation; do not update catalog tables directly unless IBM documentation and site procedure explicitly allow a specific task.
- Prefer Db2 DDL, DCL, BIND, REBIND, RUNSTATS, and utilities to make supported changes.
- Use qualified names, especially owner, creator, database, table space, collection, and package name.
- Check the Db2 subsystem before comparing test and production catalog rows.
- Save catalog query output when it explains a production incident or release issue.
Common support scenarios
SQLCODE says an object is not found
Check SYSTABLES, SYSTABLESPACE, package collection, qualifier, and bind options. A table can exist in one subsystem or schema while the program is bound to another.
A query changed after RUNSTATS
Check catalog statistics and package bind time. If a rebind occurred after statistics changed, the package might have a new access path.
A user cannot run a query
Check table privileges, system privileges, role or group handling, and whether the application runs under a different authorization ID than the interactive user.
Related Db2 topics
Use this guide with Db2 Directory, Db2 Packages, Db2 Indexing, Db2 Utilities, and Db2 Optimizer.
FAQ
What is the Db2 catalog?
The Db2 catalog is a set of Db2 tables that stores metadata about objects, columns, indexes, privileges, packages, plans, routines, constraints, communications, and optimizer statistics.
Can developers query the Db2 catalog?
Yes, when they have the required authority. Catalog SELECT queries are common for checking object definitions, package status, privileges, and statistics.
Should catalog tables be updated manually?
No for normal work. Use supported Db2 statements, bind commands, utilities, and DBA procedures. Direct catalog updates are dangerous unless a documented IBM or site procedure specifically requires them.
No comments:
Post a Comment