DB2 Catalog

The DB2 catalog is a set of tables owned by DB2 itself (creator SYSIBM) that describe everything in the database: tables, columns, indexes, views, plans, privileges and more. It is the data dictionary of DB2 - DB2 reads it for every SQL statement, and only DB2 is allowed to update it.

Important catalog tables

• SYSIBM.SYSTABLES - One row per table or view: name, creator, type, tablespace, number of columns.
• SYSIBM.SYSCOLUMNS - One row per column: name, data type, length, nullability, default value.
• SYSIBM.SYSINDEXES - One row per index: name, table it belongs to, uniqueness, clustering.
• SYSIBM.SYSTABLESPACE - One row per tablespace: name, database, buffer pool, partitions.
• SYSIBM.SYSVIEWS - The SQL text of every view definition.
• SYSIBM.SYSTABAUTH - Who has which privileges on which tables.

Querying the catalog

• Anyone with SELECT privilege can read the catalog. It is the fastest way to answer "what tables exist?" or "what columns does this table have?".
• Example - list your tables and describe one table:-
  SELECT NAME, CREATOR, TYPE FROM SYSIBM.SYSTABLES WHERE CREATOR = 'MYID'; SELECT NAME, COLTYPE, LENGTH, NULLS FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'EMP' AND TBCREATOR = 'MYID' ORDER BY COLNO;
• The first query lists all tables you created. The second shows every column of the EMP table with its data type and length.

Catalog vs directory

• The catalog lives in database DSNDB06 and holds readable descriptions of objects - the tables listed above.
• The directory lives in database DSNDB01 and holds internal control blocks DB2 needs to operate (database descriptors, skeleton plans). You do not query it directly.
• Both are updated automatically by DB2 when you run DDL like CREATE TABLE or GRANT. You must never update them with your own SQL.
• Because every statement reads the catalog, keep its tablespaces healthy: regular REORG and RUNSTATS on DSNDB06 keep the whole subsystem fast.