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.

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.

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.

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.

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.

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.

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, HIDDENFROM 	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.INDEXESWHERE 	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.COLORDERFROM        	SYSCAT.INDEXES AS A, 			  	SYSCAT.INDEXCOLUSE AS BWHERE  	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, TEXTFROM 	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.VIEWSWHERE	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.

Easy Database Development

Knowledge of these catalog views provides a wealth of information about database objects, their logical and physical structures, and their states, which makes database development easier and more efficient.