DB2 System Catalog Views: Everything You Need to Know About Your DB Objects

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

ny database engine, whether it is IBM DB2 UDB, Oracle, or Microsoft SQL Server, depends on stored metadata for the normal operation of a database instance. In DB2 UDB, this metadata information is stored as database tables and views called system catalog views. Application developers can use this metadata to find out more about a database object and its state, which is very useful when developing applications.

Metadata information about database instances is required for the smooth operation of any database system. The database system uses this information when serving user requests, in the form of either DML statements or calls from database utilities. For database administrators, the metadata helps in the tuning and optimization of a database instance. For database developers, this information helps in finding the logical and physical structure of database objects such as tables, views, columns, indexes, triggers, and many others. Most of the graphical database development tools available in the market use metadata information to display details about database objects. IBM's DB2 Development Center IDE is an example of such a tool.

This article explains how the IBM DB2 UDB Ver. 8.2 (DB2) database stores metadata information pertaining to an instance. It elaborates on how DB2 stores the metadata about database objects such as tables, views, indexes, and triggers. Further, it explains how database developers can use this metadata information to find out the logical and physical structure, as well as the state and validity, of database objects. Knowing how metadata is stored is very useful when you need the details of an object in the database but have limited or no access to graphical tools.

 

System Catalog Views in DB2

DB2 uses a set of tables and views called system catalog views to store metadata about objects. These system catalog views are created along with the database instance, and users are not allowed to physically create or drop them. However, if sufficient privileges are granted, users can retrieve any information they want, just like tables and views. DB2 database manager uses this information while serving requests from users. Moreover, it also ensures the accuracy of the metadata.

IBM DB2 database manager provides two types of catalog views for every database instance:

1. SYSCAT views – These views are created as read-only under the SYSCAT schema. They store the information needed to define the logical and physical structure of objects in the database. Select privilege is granted by default, for public in this schema.
2. SYSSTAT views – These are updatable views under the SYSSTAT schema. They store statistics about the database objects that the database manager uses while processing user requests in the form of queries or database utility calls.

The following sections describe some of the catalog views available in DB2 under the SYSCAT schema, which stores the metadata about database objects.

 

SYSCAT.TABLES

The SYSCAT.TABLES catalog view stores information about database objects created in a database, such as tables, views, nicknames, and aliases. Anytime these database objects are created, the database manager inserts a row in this catalog view. Database developers can use this information to determine the existence of a database object, its status, the number of columns in the table/view, the number of columns in the primary key, the primary index ID, the number of unique indices, and much more.

Table 1 provides some of the columns available in this catalog view.

 

Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	Stores the schema name on which the database object is defined
TABNAME	VARCHAR(128)	Stores the name of the database object, such as table, view, nickname, or an alias
TYPE	CHAR(1)	Identifies the database object as a table, view, alias, or a nickname (The type value 'T' means table; 'V' means view; 'N' means nickname; and 'A' means alias.)
COLCOUNT	SMALLINT	Number of columns in the table or view
KEYCOLUMNS	SMALLINT	Number of columns that constitute the primary key
KEYINDEXID	SMALLINT	Index ID for the primary key
KEYUNIQUE	SMALLINT	Number of unique constraints in the table or view

(* 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 1. Columns Available in SYSCAT.TABLES Catalog View

 

SYSCAT.VIEWS

The SYSCAT.VIEWS catalog view stores one or more rows per view defined in a database instance. Developers can use this catalog view to find out more about the views. The information stored in this view includes view name, view DDL text, and the validity of the view. Table 2 provides a partial list of the columns (which are of interest to this article) available in this catalog view.

 

Column Name	Data Type	Description
VIEWSCHEMA	VARCHAR(128)	Schema name for the view
VIEWNAME	VARCHAR(128)	Name of the view
DEFINER	VARCHAR(128)	User who created the view
VIEWCHECK	CHAR(1)	Type of view checking defined for this view: N = means no check option L = means local check option C = means cascaded check option
READONLY	CHAR(1)	Defines whether the view is read only or not: Y = means read only N = means view is not read only
VALID	CHAR(1)	Determines the validity of the view: Y = means view is valid X = means view is invalid
TEXT	CLOB(64K)	DDL text for view

(* 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 2. Columns Available in SYSCAT.VIEWS Catalog View

 

SYSCAT.COLUMNS

The SYSCAT.COLUMNS catalog view is used to store information about columns in a table. For each table defined in the database, database manager inserts a row in this catalog view for each column defined in the new table. Table 3 provides a partial list of the columns (which are of interest to this article) available in this catalog view.

 

Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	Stores the schema name of the table on which the column is defined
TABNAME	VARCHAR(128)	Stores the name of the table on which the column is defined
COLNAME	VARCHAR(128)	Name of the column
COLNO	SMALLINT	Position of the column in the table
TYPENAME	VARCHAR(18)	Data type of the column
LENGTH	INTEGER	Size of the column
DEFAULT	VARCHAR(254)	Default value for the column, if defined
INDENTITY	CHAR(1)	'Y' – indicates the column as an identity column 'N' – indicates the column as not an identity column
HIDDEN	CHAR(1)	Type of the hidden column: 'S' – means system managed hidden column; blank if the column is not hidden

(* 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 3. Columns Available in SYSCAT.COLUMNS Catalog View

 

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
• SYSCAT.COLAUTH – contains information about users' access rights for a particular column
• SYSCAT.DATATYPE – contains information about the built-in and user-defined data types available in the database
• SYSCAT.DBAUTH – user authorization information for a particular database instance
• SYSCAT.INDEXAUTH – privileges for users on a particular index

 

Put the Views to Use

This section describes some common tasks that can be accomplished by using the catalog views described in the previous section. These examples will familiarize you with the uses of catalog views. You can explore catalog views further by referring to the DB2 documentation.

1. List all the tables in a database (Excluding system-defined tables)
   In DB2, system-defined tables are created in schemas that start with 'SYS'. SYSIBM, SYSCAT, SYSSTAT, and SYSTOOLS are examples of system-defined schemas. The following SQL statement displays all the tables from schemas (other than system-defined ones):

    SELECT 	TABSCHEMA, TABNAME, TYPE, COLCOUNT,  				KEYCOLUMNS, KEYINDEXID, KEYUNIQUE  FROM 	SYSCAT.TABLES  WHERE 	TABSCHEMA NOT LIKE 'SYS%' AND  				TYPE = 'T' 

   Having the type filter as 'T' will return only information about tables in the given database. You can also filter by other database objects like views, nicknames, and aliases by providing the corresponding type values.

 

2. Check whether a given table exists in the database
   Use the SYSTCAT.TABLES catalog view to accomplish this. You can do a select on the catalog view and filter the result by the table name you want to check:

    SELECT 	TABSCHEMA, TABNAME, TYPE, COLCOUNT,  				KEYCOLUMNS, KEYINDEXID, KEYUNIQUE  FROM 	SYSCAT.TABLES  WHERE 	TABSCHEMA NOT LIKE 'SYS%' AND  				TYPE = 'T' AND  				TABNAME = 'TABLE_NAME' 

   Among the selected columns, COLCOUNT gives the number of columns in the tables; KEYCOLUMNS gives the number of columns that constitute the primary key; KEYINDEXID gives the index ID of the primary index; and KEYUNIQUE gives the number of unique constraints in a table.

 

3. List all the columns in a given table (Like the describe command in Oracle)
   To get all the columns in a given table, you can use the SYSCAT.COLUMNS catalog view. The following query will display all the columns in the table provided, as well as the data type of the column, the maximum length of data that can be stored in the column, the default value (if any) for the column, whether the column is in an identity column or not, and whether the column is hidden or not:

    SELECT 	TABSCHEMA, TABNAME, COLNO, COLNAME,  				TYPENAME, LENGTH, DEFAULT, IDENTITY, HIDDEN FROM 	SYSCAT.COLUMNS  WHERE 	TABSCHEMA NOT LIKE 'SYS%' AND  				TABNAME = 'TABLE_NAME'  ORDER BY 	COLNO  

4. List all the indexes available for a given table
   To get all indexes available in a given table, you can use the SYSCAT.INDEXES catalog view. The following query will display the index name, the user who created the index, the names of the columns included in an index, the number of columns in an index, whether an index is primary or not, whether an index is unique or duplicate values are allowed, the index type, etc.:

    SELECT 	INDNAME, DEFINER, TABSCHEMA, TABNAME,  				COLNAMES, COLCOUNT, UNIQUERULE, INDEXTYPE  FROM 	SYSCAT.INDEXES WHERE 	TABSCHEMA NOT LIKE 'SYS%' AND 				TABNAME = 'TABLE_NAME' 

   When you write queries to retrieve data from a table, use all the available indexes to improve performance. For that, you need to know all the columns in an index, the order in which they appear, and the sort order for each of the columns. To get all this information, you can use the SYSCAT.INDEXES and SYSCAT.INDEXCOLUSE catalog views as follows:

    SELECT 	A.INDNAME, A.TABSCHEMA, A.TABNAME,   			  	B.COLNAME, B.COLSEQ, B.COLORDER FROM         	SYSCAT.INDEXES AS A,  			  	SYSCAT.INDEXCOLUSE AS B WHERE  	A.INDSCHEMA = B.INDSCHEMA AND  				A.INDNAME = B.INDNAME AND         	A.TABSCHEMA NOT LIKE 'SYS%' AND  				A.TABNAME = 'TABLE_NAME'  ORDER BY 	A.INDNAME, B.COLSEQ 

5. List all the triggers for a given table and the trigger definition statements
   Information about triggers is stored in the SYSCAT.TRIGGERS catalog view. By querying this catalog view, you can retrieve the necessary information about a trigger, including its name, granularity, validity, creation time, trigger event, trigger time, and trigger definition statement text. The following SQL shows one such example:

    SELECT 	TRIGNAME, TABNAME, TRIGTIME, TRIGEVENT, GRANULARITY, VALID, CREATE_TIME, TEXT FROM 	SYSCAT.TRIGGERS  WHERE 	TABSCHEMA NOT LIKE 'SYS%' AND 				TABNAME = 'TABLE_NAME' ORDER BY 	TABNAME 

6. List all the views and view definition statements
   The information about views is stored in the SYSCAT.VIEWS catalog view. By querying this catalog view, you can retrieve all the information pertaining to a view. The following SQL shows how to retrieve the view name, the user who created the view, the check option for the view, the read and write properties of the view, as well as the validity and the view creation SQL:

    SELECT 	VIEWSCHEMA, VIEWNAME, VIEWCHECK, READONLY,  				VALID, TEXT  FROM 	SYSCAT.VIEWS WHERE	VIEWSCHEMA NOT LIKE 'SYS%' AND 				VIEWBAME = 'VIEW_NAME' 

The preceding examples described how to retrieve and use metadata information about database objects such as tables, indexes, views, columns, and triggers.