Getting Started with the DB2 for z/OS Catalog

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:

2 Comments
1 Like

Outlook Web App

May 6, 2014 05:11 AM by kevin pitter

Hello the links in the article take me to an outlook web app login page regquesting a domain/username and password. Where do i register or request one from?


 

RE: Outlook Web App

May 7, 2014 02:52 PM by Denis Tronin

Hello Kevin, of course I am not sure, but it looks as if there is some problem with your company firewall..

Recent Stories
Introduction to Db2 for z/OS System Profiles

10 questions to review and understand your active log data set configuration

DB2 for z/OS query support over FTP