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

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

    Share the Post:
    Share on facebook
    Share on twitter
    Share on linkedin

    Overview

    Recent Articles: