DB2 System Catalog Tables

Ever wondered, when any new DB2 object is created like Table,View,Stored Procedure,DBRM etc. where all the information is stored. Is there any place where I can look up to get all the details like all the Indexes of the table, all the columns in an index, Referential integrity on the table etc..

DB2 system catalog tables is the answer and Good Developer/programmer should know about it.

All the mainframe shops generally have tools to get this information.

These tools goes by different name , most common of those and people many know is by BMC.
BMC Change and Catalog Manager,BMC Main View for DB2 etc.

This tool will get you all this information and have easy to use User interface and navigation but this tool in background also use the information from DB2 Systems catalog table.

So, DB2 system catalog tables is the ultimate source where all information is recorded.

As the name suggest , there are set of special tables where information get recorded automatically whenever any object is created. With proper access we can just query these tables ,like we query any other tables, and get the desired information.

All catalog tables are qualified by schema SYSIBM.

Ex SYSIBM.SYSTABLES,SYSIBM.SYSKEYS etc.

Sample query -

SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME = <Table name>

More about it in upcoming posts.