Deep Dive into Db2 LUW System Tables and Catalogs

Db2 LUW relies on a robust system of tablespaces, schemas, and views to manage metadata like object definitions, privileges, and stats. Key players include SYSCATSPACE as the backbone tablespace, SYSIBM for base tables, SYSCAT for polished views, SYSIBMADM for admin insights, and SYSSTAT for optimizer fuel.

Picture generated with ChatGPT

SYSCATSPACE tablespace

SYSCATSPACE is the default tablespace for all catalog tables, created automatically during database setup. It stores critical metadata across schemas like SYSIBM and SYSCAT, ensuring DDL operations update object info instantly. You can’t drop it — it’s non-negotiable for database integrity, storing everything from table definitions to authorizations.

SYSIBM schema

SYSIBM schema holds base system tables like:

• SYSTABLES (table/index info),
• SYSCOLUMNS (column details),
• SYSINDEXES (index stats),
• SYSPACKAGE (stored procedure packages).

These are off-limits for direct DML—only Db2 engine modifies them via CREATE/ALTER/DROP.

Schemas with useful views

No raw SYSIBM mess — just readable insights.

SYSCAT: Everyday admin views

SYSCAT builds friendly views over SYSIBM, such as:

• SYSCAT.TABLES (table status),
• SYSCAT.INDEXES (index usability),
• SYSCAT.COLUMNS (nullable/ default info),
• SYSCAT.ROUTINES (function/procedure details),
• SYSCAT.TABLESPACES (tablespace details and state),
• SYSCAT.DBAUTH (lists database-level grants),
• SYSCAT.TABAUTH (covers tables/views).

SYSIBMADM: Performance allies

SYSIBMADM offers dynamic views like:

• ADMINTABINFO (table info),
• MON_CURRENT_SQL (active queries).

It’s gold for troubleshooting without deep dives.

SYSSTAT: Stats powerhouse

SYSSTAT holds statistics tables, updated by RUNSTATS, feeding optimizers with cardinality and size data from SYSCAT.TABLES.

SYSSTAT mirrors stats tables like:

• SYSSTAT.TABLES (post-RUNSTATS cardinality),
• SYSSTAT.COLUMNS (distinct values).

These tablespaces and schemas are your Db2 LUW Swiss Army knife — master them for efficient operations. Dive in with SELECTs tailored to your setup.

Query examples from my notes

Check user privileges (SYSIBMADM.PRIVILEGES)

SELECT AUTHID, PRIVILEGE, OBJECTSCHEMA, OBJECTNAME, OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES
WHERE AUTHID = 'USER_ID'

Estimate table size (SYSIBMADM.ADMINTABINFO)

SELECT TABSCHEMA, TABNAME, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE) AS TOTAL_SIZE_KB
FROM SYSIBMADM.ADMINTABINFO
WHERE TABSCHEMA = 'YOUR_SCHEMA' AND TABNAME = 'YOUR_TABLE'

Check indexes (with details) on your table (SYSCAT.INDEXES)

SELECT IND.INDSCHEMA, IND.INDNAME, IND.UNIQUERULE, IND.INDEXTYPE, IND.TABNAME, SUBSTR(LISTAGG(COLS.COLNAME, ', ') WITHIN GROUP (ORDER BY COLS.COLSEQ),1,32) AS COLUMNS
FROM SYSCAT.INDEXES AS IND JOIN SYSCAT.INDEXCOLUSE AS COLS
ON IND.INDSCHEMA = COLS.INDSCHEMA AND IND.INDNAME = COLS.INDNAME 
WHERE IND.TABSCHEMA = 'YOUR_SCHEMA' AND IND.TABNAME = 'YOUR_TABLE' 
GROUP BY IND.INDSCHEMA, IND.INDNAME, IND.UNIQUERULE, IND.INDEXTYPE, IND.TABNAME 
ORDER BY IND.INDNAME

In order not to make the queries too cluttered, I omitted the SUBSTR() functions that improve readability of the output (but you probably want to include them in your case)

The queries above were tested in Db2 11.5.9.0.