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

Oct 26, 2005

Page 1 of 4

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.

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.

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.