When dealing with a database, it is important to understand the “DB2 catalog” concept. The catalog is a set of DB2 tables that store the DB2 system state. Almost every action that takes place in DB2 is reflected in a change to one or more catalog tables. There are more than 150 catalog tables as of version 11! You might ask what information can be found in them. Here is the summary:
- Information about each DB2 object: databases, tables, stored procedures, columns, indexes, plans, packages, JARs, XML, etc. These tables make up a majority of the catalog tables.
- Security and audit configuration: authorization ids, privileges of various objects, audit policies, etc.
- Utility activities: image copies, loads, reorgs, etc.
- Communication settings: remote servers and locations, trusted contexts, etc.
- Performance: object statistics, maintenance recommendations, alerts, profiles, etc.
So how can you find and access these catalog tables? They are created under the SYSIBM schema within the DSNDB06 database. Most of the tables are read-only access and their content is being automatically updated by the DB2 subsystem, when a new object is created or a command is issued for example. However some tables can be updated directly with SQL or by running a utility such as RUNSTATS. For specific table descriptions, refer to IBM DB2 documentation (http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_catalogtablesintro.dita?cp=SSEPEK_11.0.0%2F10-0&lang=en).
You can query the SYSIBM.SYSTABLES table to learn details of all a subsystem’s database tables. This includes information such as its type, encoding, database and table space names, owner, auditing options, and many more. Details about a table’s specific columns, such as type, length, default value, minimal and maximum values can be found in the SYSIBM.SYSCOLUMNS table. If you are interested in knowing whether or not the table has an index, review the contents of the SYSIBM.SYSINDEXES table.
The catalog tables are tightly interconnected and the following sample SQL demonstrates this fact. The query returns a few table details and some column information for existing indexes on a sample table named 'EMP':
SELECT T.NAME AS TABLE, T.DBNAME, T.COLCOUNT,
I.NAME AS INDEX,
C.NAME AS COLUMN, C.COLTYPE, C.COLNO
FROM SYSIBM.SYSTABLES T JOIN
SYSIBM.SYSINDEXES I ON
T.NAME=I.TBNAME AND T.CREATOR=I.TBCREATOR JOIN
SYSIBM.SYSCOLUMNS C ON
I.TBNAME=C.TBNAME AND I.TBCREATOR=C.TBCREATOR
WHERE T.NAME='EMP';
Why should you care about the DB2 catalog? As a DB2 developer, you might need to find information about the DB2 system or certain object characteristics. As a DB2 administrator, you would also need to ensure the catalog is available and contains accurate information effective to ensure smooth database execution.
To get more information about the DB2 catalog, you can review these related IDUG presentations: