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';