IBM DB2 UDB 8.1 System Catalogs
IBM DB2 UDB 8.1 System Catalogs
IBM DB2 UDB maintains two sets of the INFORMATION_SCHEMA views — one in SYSCAT schema and a subset in the SYSSTAT schema (used for SQL Optimizer to improve query performance). All views are created whenever the CREATE DATABASE command is run; the views comprising the catalog cannot be explicitly dropped, altered, or updated, except for some columns in the SYSSTAT views.
The INFORMATION_SCHEMA objects in DB2
The Table 13-5 shows some of the views we consider most useful.
| Note |
INFORMATION_SCHEMA views are introduced for SQL standard compliance and are maintained on top of system base tables. |
|
INFORMATION_SCHEMA View |
Contains Information About |
|---|---|
|
SYSCAT.ATTRIBUTES |
Attributes of the structured data types |
|
SYSCAT.DBAUTH |
Database authorities |
|
SYSCAT.CHECKS |
Check constraints; corresponds to the SQL99 standard view CHECK_CONSTRAINTS |
|
SYSCAT.COLAUTH |
Column level privileges |
|
SYSCAT.COLUMNS |
Columns accessible for the current user |
|
SYSCAT.COLCHECKS |
Columns referenced by the check constraints |
|
SYSCAT.KEYCOLUSE |
Columns used in the keys — either primary or foreign |
|
SYSCAT.CONSTDEP |
Constraint dependencies |
|
SYSCAT.DATATYPES |
Valid data types |
|
SYSCAT.INDEXAUTH |
Privileges for indices |
|
SYSCAT.INDEXCOLUSE |
Columns of which indices are comprised |
|
SYSCAT.INDEXDEP |
Index dependencies |
|
SYSCAT.INDEXES |
Indices |
|
SYSCAT.PACKAGES |
Packages |
|
SYSCAT.PACKAGEAUTH |
Package privileges |
|
SYSCAT.PACKAGEDEP |
Package dependencies |
|
SYSCAT.PROCOPTIONS |
Stored procedure options. |
|
SYSCAT.PROCPARMOPTIONS |
Stored procedure parameter options |
|
SYSCAT.PROCPARMS |
Stored procedures parameters |
|
SYSCAT.REFERENCES |
Referential constraints; corresponds to the SQL99 standard REFERENTIAL_CONSTRAINTS |
|
SYSCAT.SCHEMAUTH |
Schema privileges |
|
SYSCAT.SCHEMATA |
All the schemas defined for the database |
|
SYSCAT.SEQUENCES |
Database sequences |
|
SYSCAT.PROCEDURES |
Stored procedures |
|
SYSCAT.TABCONST |
Constraints defined for the table |
|
SYSCAT.TABAUTH |
Table level privileges |
|
SYSCAT.TABLES |
Tables created within the database |
|
SYSCAT.TABLESPACES |
Database tablespaces |
|
SYSCAT.TABLESPACEAUTH |
Tablespace privileges |
|
SYSCAT.TRIGDEP |
Trigger dependencies |
|
SYSCAT.TRIGGERS |
Triggers created in the database. |
|
SYSCAT.FUNCTIONS |
User-defined functions |
|
SYSCAT.VIEWS |
Views created in the database |
The SELECT privilege to views is granted to PUBLIC by default. IBM explicitly states that columns in the views might be changed from release to release and recommends querying these tables using SELECT * FROM SYSCAT.<view> syntax. Nevertheless, IBM specifies that some columns are "guaranteed" to work with the corresponding views. (See Table 13-6.)
|
Object |
Selected Column Names |
|---|---|
|
TABLE |
TABSCHEMA, TABNAME, COLCOUNT, KEYCOLUMNS |
|
INDEX |
INDSCHEMA, INDNAME |
|
VIEW |
VIEWSCHEMA, VIEWNAME, TABID, COLNO, COLTYPE |
|
CONSTRAINT |
CONSTSCHEMA, CONSTNAME, |
|
TRIGGER |
TRIGSCHEMA, TRIGNAME |
|
PACKAGE |
PKGSCHEMA, PCKGNAME |
|
TYPE |
TYPESCHEMA, TYPENAME, TYPEID |
|
FUNCTION |
FUNCSCHEMA, FUNCNAME, FUNCID |
|
COLUMN |
COLNAME, COLNO, DEFAULT, REMARKS |
|
SCHEMA |
SCHEMANAME |
The following query retrieves information about the table CUSTOMER created in the ACME database. To display meaningful results, we've limited the number of columns selected to three, because the table contains over 30 columns.
db2 => SELECT tabschema, colcount, create_time FROM syscat.tables WHERE tabname = 'CUSTOMER' TABSCHEMA COLCOUNT CREATE_TIME ------------------- -------- -------------------------- ACME 7 2002-09-23-17.55.50.95300 1 record(s) selected.
The information returned shows that the table CUSTOMER belongs to the schema ACME, was created on 9/23/2002, and contains seven columns. It is possible to join tables of the system catalog to produce combined results. The following query joins two tables, SYSCAT.TABLES and SYSCAT.COLUMNS, to give a single set of values extracted from both tables.
db2 => SELECT tbl.tabname, cl.colname FROM syscat.tables tbl, syscat.columns cl WHERE syscat.tables.tabname = syscat.columns.tabname AND syscat.tables.tabname = 'CUSTOMER' TABNAME COLNAME ------------- ------------------------------ CUSTOMER CUST_ID_N CUSTOMER CUST_PAYTERMSID_FN CUSTOMER CUST_SALESMANID_FN CUSTOMER CUST_STATUS_S CUSTOMER CUST_NAME_S CUSTOMER CUST_ALIAS_S CUSTOMER CUST_CREDHOLD_S 7 record(s) selected.
Some INFORMATION_SCHEMA views contained in the SYSSTAT schema are updateable as shown in Table 13-7.
|
INFORMATION_SCHEMA Views |
Description |
|---|---|
|
SYSSTAT.COLUMNS |
Contains information about columns for each table. |
|
SYSSTAT.INDEXES |
Contains information about indices created for the database tables. |
|
SYSSTAT.COLDIST |
Contains detailed statistics about column usage. |
|
SYSSTAT.TABLES |
Contains information about the database tables. |
|
SYSSTAT.FUNCTIONS |
Contains information about user-defined functions. |
| Tip |
For the sake of compatibility with the DB2 Universal Database for OS/390, IBM maintains the SYSDUMMY1 catalog table in the SYSCAT schema. This table consists of one row and one column (IBMREQ) of the CHAR(1) data type. See Chapter 8 for more information on the use of this table. |
Obtaining information about INFORMATION_SCHEMA objects
The DESCRIBE TABLE <table_name> command can be used to obtain information about the internal structure of the INFORMATION_SCHEMA objects in DB2. For example:
db2 => describe table syscat.views Column Type Type name Length Scale Nulls name schema --------------- ------------- --------- ------- ------ ----- VIEWSCHEMA SYSIBM VARCHAR 128 0 No VIEWNAME SYSIBM VARCHAR 128 0 No DEFINER SYSIBM VARCHAR 128 0 No SEQNO SYSIBM INTEGER 4 0 No VIEWCHECK SYSIBM CHARACTER 1 0 No READONLY SYSIBM CHARACTER 1 0 No VALID SYSIBM CHARACTER 1 0 No QUALIFIER SYSIBM VARCHAR 128 0 No FUNC_PATH SYSIBM VARCHAR 254 0 No TEXT SYSIBM CLOB 65536 0 No 10 record(s) selected.
