DB2 creates and maintains an extensive set of system catalog tables for each database. These tables contain information about the logical and physical structure of database objects such as tables, views, packages, referential integrity relationships, functions, distinct types, and triggers. They are created when the database is created, and are updated in the course of normal operation. You cannot explicitly create or drop them, but you can query and view their contents.
For more information, refer to the SQL Reference.
The catalog views are like any other database view. You can use SQL statements to look at the data, exactly in the same way that you would for any other view in the system.
You can find very useful information about tables in the SYSCAT.TABLES catalog. To find the names of existing tables that you have created, issue a statement similar to the following:
SELECT TABNAME, TYPE, CREATE_TIME
FROM SYSCAT.TABLES
WHERE DEFINER = USER
This statement produces the following result:
TABNAME TYPE CREATE_TIME
------------------ ---- --------------------------
ORG T 1997-05-22-11.15.27.850000
STAFF T 1997-05-22-11.15.29.470000
DEPARTMENT T 1997-05-22-11.15.30.850000
EMPLOYEE T 1997-05-22-11.15.31.310000
EMP_ACT T 1997-05-22-11.15.32.850000
PROJECT T 1997-05-22-11.15.34.410007
EMP_PHOTO T 1997-05-22-11.15.35.190000
EMP_RESUME T 1997-05-22-11.15.40.600000
SALES T 1997-05-22-11.15.43.000000
The following list includes catalog views pertaining to subjects discussed
in this book. There are many other catalog views, and they are listed
in detail in the SQL Reference and Administration Guide.
| Description | Catalog View |
|---|---|
| check constraints | SYSCAT.CHECKS |
| columns | SYSCAT.COLUMNS |
| columns referenced by check constraints | SYSCAT.COLCHECKS |
| columns used in keys | SYSCAT.KEYCOLUSE |
| datatypes | SYSCAT.DATATYPES |
| function parameters or result of a function | SYSCAT.FUNCPARMS |
| referential constraints | SYSCAT.REFERENCES |
| schemas | SYSCAT.SCHEMATA |
| table constraints | SYSCAT.TABCONST |
| tables | SYSCAT.TABLES |
| triggers | SYSCAT.TRIGGERS |
| user-defined functions | SYSCAT.FUNCTIONS |
| views | SYSCAT.VIEWS |