Catalog Queries for Db2 for z/OS

Introduction

If you are experienced with database management systems outside of mainframe, you've probably heard about an information schema. This is a set of views that provide information about all tables, columns, and all other database objects defined in the system. Db2 for z/OS does not have an information schema, but has a Db2 catalog containing all the metadata about the objects.

There is already a lot of blogs, articles, presentations about the Db2 catalog (for instance you can start with Denis' blog, which is an overview of the catalog, but also contains useful links - check it out); so why to write another?

What I would like to share here is few hints and tips where to learn more about the catalog structure and relations between the tables. Sure, you can just open the manual and go through all the tables and indexes in the catalog and figuring out the relations. When I started with my research of the Db2 catalog I was lucky to have a Db2 catalog poster and a reference guide on my desk. The Db2 tools vendors usually provide these for their customers. I know that BMC and Broadcom do so, but there might be other companies (including IBM) as well (please share them in the comments). If you have a chance to visit the IDUG conference or some other event try to stop at the booth of your favorite vendor and they will be happy to provide you the poster and/or the reference guide.

However, even if you don't have these materials in your hands, you can still do a valuable research yourself. Even Db2 manuals and sample SQL contain helpful information.

SQL queries in the documentation

Let's start with the section "Using the catalog in database design" in the Db2 Admin guide. There are many queries prepared for your use. As an example, I am selecting some of them here, but make sure to go through all of them if you want to learn more about catalog:

• If you need the information about your table, let's submit this SQL selecting from SYSIBM.SYSTABLES:

SELECT * FROM sysibm.systables WHERE name = 'PROJACT' AND creator =
'DSN8C10'; 

the output will list all details about the DSN8C10.PROJACT table (part of the IBM sample tables). I would like to highlight few columns from the SYSTABLES table:

• There are some identification columns - CREATOR is the schema, and NAME is obviously the name; but there are also internal ids - DBID and PSID, you might find them useful when working with some utilities and their outputs. For example if you want to filter log records in DSN1LOGP for a particular object. In such case you often need a hex value of the ids, so use the HEX() function, for example HEX(DBID).
• The type of the table is indicated in the TYPE column. Normal tables have `T' here, but if you are searching for views, type WHERE TYPE = `V'
• You can see the corresponding database and tablespace in DBNAME, TSNAME columns
• If you are interested who created the table and when, check CREATEDBY and CREATEDTS
• If you want to know if the table is has some children or parents relationships, see PARENTS or CHILDREN columns. If the values are non-zero, the table is in a relationship.

• and there are more - number of columns, number of check constraints, statistics like number of pages, etc.

  Now you have information about the table, what about the columns? We already know the number of columns from the SYSTABLES, but you can get more information from the SYSCOLUMNS using the following query:

SELECT colno, name, tbname, coltype, length, nulls, default FROM
  sysibm.syscolumns WHERE tbname='DEPT' AND tbcreator = 'DSN81210'
  ORDER BY colno; 

From this query we get the column names, the column type, length; but also indication whether NULLs are allowed, whether there is a default value (you would find the value in DEFAULTVALUE column). Also note the COLNO, which is also used for ordering.

If you wanted to check the primary keys, see the KEYSEQ column. If there is a non-zero value, the column is a part of the primary key. Ordering on the KEYSEQ would reveal the primary key.

Let's find some details about referential constraints. Two tables are important here - SYSRELS - containing one row for every referential constraint; and SYSFOREIGNKEYS containing one row for every column of every foreign key. The RELNAME column in both tables links those together and refers to the constraint name.

So, if you just wanted to find all children of your table, you can easily select from SYSRELS and specifying REFTBNAME and REFTBCREATOR as your table in the WHERE clause. For example like this:

;SELECT * FROM sysibm.sysrels
  WHERE reftbname='DEPT'
  AND reftbcreator = 'DSN81210';

SQL queries in the SDSNSAMP

SELECT * FROM sysibm.sysviewdep WHERE dname = 'VDEPMG1' AND dcreator = 'DSN81210';  

Catalog queries on catalog objects

As a closing note, I would also mention that you can even query the Db2 catalog on the catalog itself. Do you want to see the details about SYSIBM.SYSTABLES? Just type the following query and there you go.

SELECT * FROM sysibm.systables WHERE name = 'SYSTABLES' AND creator =
'SYSIBM';                                                            

Now you are one level of abstraction higher :-)

Summary