Find out how DB2 stores metadata information about database instances. You can use this information to learn more about your database objects, making database development easier and more efficient.

by Shibu Kalluvila Raj

Oct 26, 2005

Page 3 of 4

SYSCAT.INDEXES

The SYSCAT.INDEXES catalog view is used to store information about the indexes defined for a given table. If there is an index defined for a table in the database, then a row will be created in this catalog view for each of those indexes. Database developers can use this catalog view to find information about the indexes for a given table. Table 4 below lists some of the columns available in this catalog view.

Column Name

Data Type

Description

INDSCHEMA

VARCHAR(128)

Name of the schema on which the index is defined

INDNAME

VARCHAR(18)

Index name

DEFINER

VARCHAR(128)

User who created the index

TABSCHEMA

VARCHAR(128)

Stores the schema name of the table on which the index is defined

TABNAME

VARCHAR(128)

Stores the name of the table for which index is defined

COLNAMES

VARCHAR(640)

List of columns in the index

UNIQUERULE

CHAR(1)

Determines whether the index is unique or not:

D = means duplicate allowed

P = means primary index

U = means unique index

INDEXTYPE

CHAR(4)

CLUS = means clustered index

REG = means regular index

DIM = means dimension block index

BLOK = means block index

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 4. Columns Available in SYSCAT.INDEXES Catalog View

SYSCAT.INDEXCOLUSE

The SYSCAT.INDEXCOLUSE catalog view is used to store information about all the columns that participate in an index. If you know the index name, you can use this catalog view to find out its participating columns, its sequence, and its sort order. This catalog view is different from the SYSCAT.INDEXES in the sense that it contains information about only the columns in the index, its sequence, and the order of columns in the index, while the former carries more information such as the type of index, index rules, etc.

Table 5 lists some of the columns available in this catalog view.

Column Name

Data Type

Description

INDSCHEMA

VARCHAR(128)

Name of the schema on which the index is defined

INDNAME

VARCHAR(18)

Index name

COLNAME

VARCHAR(128)

User who created the index

COLSEQ

SMALLINT

Stores the schema name of the table on which the index is defined

COLORDER

CHAR(1)

Order of value in the column:

A = means ascending

D = mean descending

I = means include  ordering ignored

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 5. Columns Available in SYSCAT.COLUSE Catalog View

SYSCAT.TRIGGERS

The SYSCAT.TRIGGERS catalog view is used to store information about the triggers defined in a database. If you know the trigger name, you can query this catalog view to find information about that trigger.

Table 6 lists some of the columns available in this catalog view.

Column Name

Data Type

Description

TRIGSCHEMA

VARCHAR(128)

Name of the schema on which the trigger is defined

TRIGNAME

VARCHAR(18)

Trigger name

DEFINER

VARCHAR(128)

User who created the index

TABSCHEMA

VARCHAR(128)

Stores the schema name of the table for which the trigger is defined

TABNAME

VARCHAR(128)

Name of table for which the trigger is defined

TRIGTIME

CHAR(1)

A = means after trigger

B = means before trigger

I = means instead of trigger

TRIGEVENET

CHAR(1)

Event for which the trigger is defined:

I = means INSERT

D = means DELETE

U = means UPDATE

GRANULARITY

CHAR(1)

Determines whether the trigger is executed per statement or per row:

S = means once per statement

R = means once per row

TEXT

CLOB(64K)

Full text of the trigger statement

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 6. Columns Available in SYSCAT.TRIGGERS Catalog View

Apart from the above list, other catalog views that carry useful information for database developers include the following:

SYSCAT.CHECKS  contains information about the check constraints in a table