SQL Getting Started

Introduction to Catalog Views

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.

Selecting Rows from System Catalogs

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    1999-07-21-13.42.55.128005
     STAFF              T    1999-07-21-13.42.55.609001
     DEPARTMENT         T    1999-07-21-13.42.56.069001
     EMPLOYEE           T    1999-07-21-13.42.56.310001
     EMP_ACT            T    1999-07-21-13.42.56.710001
     PROJECT            T    1999-07-21-13.42.57.051001
     EMP_PHOTO          T    1999-07-21-13.42.57.361001
     EMP_RESUME         T    1999-07-21-13.42.59.154001
     SALES              T    1999-07-21-13.42.59.855001
     CL_SCHED           T    1999-07-21-13.43.00.025002
     IN_TRAY            T    1999-07-21-13.43.00.055001

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 manuals.

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