Archived content

Archive date: 2019-09-04

This content is no longer being updated or maintained. The content is provided “as is.” Given the rapid evolution of technology, some content, steps, or illustrations may have changed.

Introduction

Almost everything that is known about a database can be found in a metadata repository called the system catalog. Metadata is information about the data in a database. It is maintained separately from the data itself. The system catalog describes the logical and physical structure of the data. The DB2 UDB system catalog (or simply "catalog") consists of a number of tables and views that are maintained by the database manager. A set of catalog tables and views is created whenever a database is created. The catalog describes the database objects, such as tables, columns, and indexes, and contains information about the types of access that users have to these objects. The catalog tables grow as database objects and privileges are added to the database. When an object is created, altered, or dropped, the database manager inserts, updates, or deletes rows in the catalog tables that describe the object and its relationships to other objects.

Of course, you cannot explicitly create or drop these system catalog tables, but you can query and view their content. There is a wealth of useful information tucked away in these views, information that can help you to understand how your database operates. In this article, I will show you, by way of working examples, how to retrieve useful information from the DB2 UDB system catalog.

System catalog base tables (SYSIBM schema)

Catalog tables are created under the SYSIBM schema, and are stored in the SYSCATSPACE table space. The catalog tables for partitioned databases reside only on the partition from which the CREATE DATABASE command was issued. Some catalog tables have parent-child relationships. For example, SYSIBM.SYSCOLAUTH, which details column-level privileges, is a child of SYSIBM.SYSTABAUTH, which records table-level privileges.

Many of the tables that make up the system catalog store information about database objects, and the names of those tables identify the objects that they describe; for example, SYSINDEXES, SYSTRIGGERS, and SYSVIEWS. The catalog tables can be classified into broad categories based on the type of data that they store. For example:

Authorization data is recorded in tables such as SYSDBAUTH (for database-level privileges), SYSTBSPACEAUTH (for privileges on table spaces), SYSTABAUTH (for privileges on tables and views), and SYSCOLAUTH (for column-level privileges), among others.
Data type and routine data is recorded in tables such as SYSDATATYPES (for both built-in and user-defined data types), SYSROUTINES (for functions or procedures), and SYSROUTINEPARMS (for parameters that are part of the routines listed in SYSROUTINES), among others.
Constraint data is recorded in tables such as SYSCHECKS (for check constraints), SYSRELS (for foreign key constraints), and SYSKEYCOLUSE (for columns that participate in a primary key, unique, or foreign key constraint), among others. For more information about constraints, see DB2 Constraints.
Dependency data is recorded in tables such as SYSCONSTDEP (for dependencies of a constraint on some other object), SYSDEPENDENCIES (for dependencies of a trigger, function, index, or index extension on some other object), and SYSVIEWDEP (for dependencies of a view on some other object), among others.
Storage management data is recorded in tables such as SYSTABLESPACES (for table spaces), and SYSTABLES (for the table spaces that are associated with a specific table), among others. For more information about table spaces, see Table space states.
Database partition data is recorded in tables such as SYSNODEGROUPS (for database partition groups) and SYSPARTITIONMAPS (for the partitioning maps that associate hashed key values with database partitions), among others.

The database manager creates and maintains two sets of system catalog views that are defined on top of the base system catalog tables. One set of read-only views is created under the SYSCAT schema, and a smaller set of updatable views is created under the SYSSTAT schema. A catalog view can be based on one or more catalog tables, and the view column names are often different than their counterparts in the catalog table. To understand this better, let's look at a couple of examples. An example of a simple view that is based on only one table is SYSCAT.KEYCOLUSE. This view is based on the SYSIBM.SYSKEYCOLUSE catalog table which, you will recall, is used to store information about the columns that participate in a primary key, unique, or foreign key constraint. Here is the view definition:

Listing 1. Listing 1. Definition of the SYSCAT.KEYCOLUSE catalog view

create view syscat.keycoluse (constname, tabschema, tabname, colname, colseq)
  as select constname, tbcreator, tbname, colname, colseq
  from sysibm.syskeycoluse

In this case, all five table columns appear in the view, but some of their names are different. This is done for consistency and clarity only.

An example of a more complex view that is based on more than one table is SYSCAT.STATEMENTS. This view, which contains information about each SQL statement in each package in the database, is based on the SYSIBM.SYSPLAN and SYSIBM.SYSSTMT catalog tables. Here is the view definition:

Listing 2. Listing 2. Definition of the SYSCAT.STATEMENTS catalog view

create view syscat.statements (pkgschema, pkgname, unique_id, version,
    stmtno, sectno, seqno, text)
  as select s.plcreator, s.plname, s.unique_id,
    (select p.pkgversion from sysibm.sysplan p
     where s.plcreator = p.creator
       and s.plname = p.name
       and s.unique_id = p.unique_id),
    s.stmtno, s.sectno, 1, s.text
  from sysibm.sysstmt s

User-oriented catalog views (SYSCAT schema)

The SYSCAT schema contains useful read-only views of the catalog tables. SELECT privilege on all views in SYSCAT is granted to PUBLIC, and you are encouraged to interact with the system catalog only through these views and the updatable views in SYSSTAT (more about that later).

The following code establishes a connection to the SAMPLE database and returns a list of all the catalog views in the SYSCAT schema:

Listing 3. Listing 3. Identifying all the catalog views in the SYSCAT schema

connect to sample

list tables for schema syscat

  or, alternatively:

select tabname from syscat.tables where tabschema = 'SYSCAT'

connect reset

To construct meaningful queries against the catalog views, you will need to know about the columns in the available views, as well as the names and purpose of the views themselves. The DB2 UDB system catalog views are described in the DB2 documentation. You can also examine the catalog views in the DB2 Control Center (see Figure 1).

Figure 1. The views that are associated with a particular database appear in the contents pane of the DB2 Control Center with Views selected in the object tree. The list has been filtered on schema SYSCAT.

The Open View window lets you examine the view contents in detail, and is a convenient way to browse the view columns (Figure 2).

Figure 2. The Open View window provides a convenient way to see a view's data.

If you're interested, you can even access the view definition itself, by selecting Alter from the pop-up menu (Figure 3).

Figure 3. The Alter View window lets you see the view definition.

Retrieving authorization data

As mentioned earlier, no single system catalog view contains all the available authorization data. Although metadata about authorities and privileges is contained in several catalog views, a single query can be constructed to, for example, retrieve all the authorization IDs that hold privileges. We'll exploit the UNION operator to accomplish this, and we'll use the SUBSTR built-in function (here and in other examples) to help format the result set:

Listing 4. Listing 4. Identifying the authorization IDs that hold privileges

select distinct substr(grantee,1,16) as grantee, granteetype, 'Database'
  from syscat.dbauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Table space'
  from syscat.tbspaceauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Schema'
  from syscat.schemaauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Table'
  from syscat.tabauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Index'
  from syscat.indexauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Column'
  from syscat.colauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Package'
  from syscat.packageauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Routine'
  from syscat.routineauth
union
select distinct substr(grantee,1,16) as grantee, granteetype, 'Server'
  from syscat.passthruauth
  order by grantee, granteetype


GRANTEE          GRANTEETYPE 3
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
MELNYK           U           Database
MELNYK           U           Index
MELNYK           U           Package
MELNYK           U           Table
MELNYK           U           Table space
PUBLIC           G           Database
PUBLIC           G           Package
PUBLIC           G           Routine
PUBLIC           G           Schema
PUBLIC           G           Table
PUBLIC           G           Table space

  11 record(s) selected.

There are many other queries that you can construct to retrieve authorization data. Here are two more examples:

Listing 5. Listing 5. Retrieving authorization data

connect to sample

Retrieve all authorization names that have explicitly been granted DBADM authority:

select distinct grantee from syscat.dbauth
  where dbadmauth = 'Y'

Retrieve a list of the table privileges that you have granted to other users:

select * from syscat.tabauth where grantor = user

connect reset

Retrieving data type and routine data

You can use the system catalog views to easily retrieve information about user-defined data types or routines. For example:

Listing 6. Listing 6. Retrieving data type and routine data

connect to sample

Retrieve information about all user‑defined types (that is, types whose schema
  is not SYSIBM):

select typeschema, typename, sourcename, metatype from syscat.datatypes
  where typeschema != 'SYSIBM'

Retrieve information about all user‑defined routines (that is, routines whose schema
  name does not begin with SQL or SYS):

select routineschema, routinename, routinetype, origin, language, text
  from syscat.routines
  where substr(routineschema,1,3) != 'SQL' and substr(routineschema,1,3) != 'SYS'

connect reset

Retrieving constraint data

You can also use the system catalog views to retrieve information about NOT NULL, unique, primary key, foreign key, and table check constraints. For example:

Listing 7. Listing 7. Retrieving constraint data

connect to sample

Retrieve a list of all the columns that cannot have a null value, in tables
  that were created by user MELNYK:

select substr(tabname,1,16) as tabname, substr(colname,1,16) as colname, nulls
  from syscat.columns
  where tabschema = 'MELNYK' and nulls = 'N'

Retrieve a list of tables that have unique constraints (other than primary key)
  defined on them:

"select substr(tabschema,1,16) as tabschema, substr(tabname,1,16) as tabname, keyunique
  from syscat.tables
  where keyunique > 0"

Retrieve all primary keys that are defined for the SAMPLE database. KEYSEQ contains
  a non‑null value if a column is part of the primary key for the table to which it
  belongs. The value represents the column's numerical position within the primary key:

select substr(tabschema, 1, 16) as tabschema, substr(tabname, 1, 16) as tabname,
    substr(colname, 1, 16) as colname, keyseq
  from syscat.columns
  where keyseq is not null

Retrieve a list of all the columns that participate in a key defined by a unique,
  primary key, or foreign key constraint. Identify the constraint name and type,
  as well as the numeric position of the column in the key (initial position is 1):

select substr(k.constname,1,20) as constname, t.type, substr(k.tabname,1,20) as tabname,
    substr(k.colname,1,16) as colname, k.colseq
  from syscat.keycoluse k, syscat.tabconst t
  where k.constname = t.constname

Retrieve a list of each table check constraint:

select substr(constname,1,20) as constname, substr(tabname,1,20) as tabname,
    substr(text,1,32) as text
  from syscat.checks

connect reset

Retrieving dependency data

You can use the system catalog views to retrieve information about the dependencies that objects have on one another.

Listing 8. Listing 8. Retrieving dependency data

connect to sample

Retrieve a list of all the tables on which the SYSCAT.COLUMNS view depends:

select distinct substr(a.tabschema,1,16) as tabschema,
    substr(a.tabname,1,16) as tabname, a.type, substr(a.tbspace,1,16) as tbspace
  from syscat.tables a, syscat.viewdep b
  where a.type = 'T'
    and a.tabname = b.bname
    and a.tabschema = b.bschema
    and b.btype = 'T'
    and b.viewname = 'COLUMNS'
    and b.viewschema = 'SYSCAT'

connect reset


TABSCHEMA        TABNAME          TYPE TBSPACE

‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
SYSIBM           SYSCHECKS        T    SYSCATSPACE
SYSIBM           SYSCOLCHECKS     T    SYSCATSPACE
SYSIBM           SYSCOLPROPERTIES T    SYSCATSPACE
SYSIBM           SYSCOLUMNS       T    SYSCATSPACE

  4 record(s) selected.

This query is essentially the same as the underlying query generated by the DB2 Control Center when you select the Show Related action against the SYSCAT.COLUMNS view (Figures 4 and 5).

Figure 4. The Show Related notebook lets you see the first-level dependency relationships between the view and other objects. In this case, we see a list of the catalog tables on which the SYSCAT.COLUMNS view depends.

Figure 5. The Show SQL window gives you the underlying query for the list of dependencies shown in Figure 4.

Retrieving storage management data

You can use the system catalog views to retrieve information about storage management objects such as table spaces.

Listing 9. Listing 9. Retrieving storage management data

connect to sample

Retrieve information about table spaces associated with the tables
  that were created by user MELNYK:

select substr(t.tabname, 1, 12) as tabname, t.tbspaceid as tsp_id,
    s.tbspacetype as tsp_type, s.datatype, s.extentsize, s.pagesize, s.dbpgname
  from syscat.tables t, syscat.tablespaces s
  where tabschema = 'MELNYK' and type = 'T' and t.tbspaceid = s.tbspaceid

connect reset


TABNAME      TSP_ID TSP_TYPE DATATYPE EXTENTSIZE  PAGESIZE    DBPGNAME
‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
CL_SCHED          2 S        A                 32        4096 IBMDEFAULTGROUP
DEPARTMENT        2 S        A                 32        4096 IBMDEFAULTGROUP
EMP_ACT           2 S        A                 32        4096 IBMDEFAULTGROUP
EMP_PHOTO         2 S        A                 32        4096 IBMDEFAULTGROUP
EMP_RESUME        2 S        A                 32        4096 IBMDEFAULTGROUP
EMPLOYEE          2 S        A                 32        4096 IBMDEFAULTGROUP
IN_TRAY           2 S        A                 32        4096 IBMDEFAULTGROUP
ORG               2 S        A                 32        4096 IBMDEFAULTGROUP
PROJECT           2 S        A                 32        4096 IBMDEFAULTGROUP
SALES             2 S        A                 32        4096 IBMDEFAULTGROUP
STAFF             2 S        A                 32        4096 IBMDEFAULTGROUP

  11 record(s) selected.

Retrieving database partition data

You can also use the system catalog views to retrieve information about database partitions and database partition groups.

Listing 10. Listing 10. Retrieving database partition data

connect to sample

Retrieve information about each database partition and the database partition group
  to which it belongs:

select n.dbpgname, substr(n.definer, 1, 16) as definer, n.pmap_id, n.create_time,
    d.dbpartitionnum, d.in_use
  from syscat.dbpartitiongroups n, syscat.dbpartitiongroupdef d
  where n.dbpgname = d.dbpgname

connect reset


DBPGNAME           DEFINER    PMAP_ID CREATE_TIME                DBPARTITIONNUM IN_USE
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑... ‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑
IBMCATGROUP        SYSIBM           0 2004‑10‑18‑08.27.54.045000              0 Y
IBMDEFAULTGROUP    SYSIBM           1 2004‑10‑18‑08.27.54.125000              0 Y

  2 record(s) selected.

Updatable catalog views (SYSSTAT schema)

The SYSSTAT schema contains a small number of updatable views that are based on the system catalog tables. These views include columns containing statistical information that is useful to the query optimizer. The optimizer uses information about the distribution of data in specific table or index columns if those columns are involved in row selection or table joins; it uses this information to compare the costs of different data access plans for specific queries.

You might be interested in changing some of these statistical values to influence the optimizer or to investigate database performance in a development or test environment. In fact, you can use SQL UPDATE statements to change the values of statistical columns in updatable catalog views. If you hold CONTROL privilege on a table, you can update values that pertain to that table, but if you hold explicit DBADM authority on the database, you can change any updatable column.

The runstats utility can be used to update statistics in system catalog tables to facilitate the query optimization process. It is recommended that you invoke the RUNSTATS command before manually updating any statistics, so that your starting point accurately reflects the current state.

A more detailed discussion of how to use the updatable catalog views to optimize performance is beyond the scope of this article. You can find more information on this topic in the DB2 UDB product library.

Controling access to the system catalog

When a database is created, SELECT privilege on the system catalog views is granted to PUBLIC (Figure 6). If your database contains sensitive data, you might want to limit access to the catalog views, because the catalog describes every object in the database. After revoking the SELECT privilege from PUBLIC, you can grant this privilege to specific users, as necessary. You must hold DBADM or SYSADM authority to grant or revoke the SELECT privilege on system catalog views.

Figure 6. The View Privileges notebook lets you see or change the privileges that are held on the view.

Consider a view that includes the name of every table on which a user's authorization ID has been explicitly granted the SELECT privilege. The code in Listing 11 creates such a view, named MYSELECTS. This view is based on another view, the SYSCAT.TABAUTH catalog view. We are specifying a grantee type of U (for user; the alternative is G for group) and a grantee value of USER (referring to the special register that specifies the run-time authorization ID). We are also filtering on rows in which a SELECT privilege flag has been set to YES.

Once the MYSELECTS view has been created, we can construct a query that retrieves data from both this view and the SYSCAT.TABLES view. This kind of query, which retrieves data from two or more tables simultaneously, is known as a join. Our join retrieves table schema and name values from the MYSELECTS view, and corresponding table types (such as V for view) from the SYSCAT.TABLES view. In this case, the query returns one row, corresponding to the view we have just created; this is the only table or view on which the authorization ID MELNYK has been explicitly granted the SELECT privilege.

Finally, we can make the MYSELECTS view available to every authorization ID, and revoke the SELECT privilege on the base view (SYSCAT.TABAUTH) from PUBLIC. A simple query against the base view verifies that both user MELNYK and PUBLIC hold the SELECT privilege on the MYSELECTS view; MELNYK, because that ID is the view creator, and PUBLIC, because the SELECT privilege was granted to PUBLIC explicitly.

Listing 11. Creating and using a view that is based on the SYSCAT.TABAUTH system catalog view

create view myselects as
  select tabschema, tabname
    from syscat.tabauth
    where granteetype = 'U'
      and grantee = user
      and selectauth = 'Y'

select m.tabschema, m.tabname, t.type
  from myselects m, syscat.tables t
  where m.tabschema = t.tabschema
    and m.tabname = t.tabname


TABSCHEMA       TABNAME       TYPE
‑‑‑‑‑‑‑‑‑‑‑‑... ‑‑‑‑‑‑‑‑‑‑... ‑‑‑‑
MELNYK          MYSELECTS     V

  1 record(s) selected.


grant select on table myselects to public

revoke select on table syscat.tabauth from public

select tabschema, tabname, selectauth, grantee
  from syscat.tabauth
  where tabname = 'MYSELECTS'


TABSCHEMA       TABNAME       SELECTAUTH GRANTEE
‑‑‑‑‑‑‑‑‑‑‑‑... ‑‑‑‑‑‑‑‑‑‑... ‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑...
MELNYK          MYSELECTS     Y          MELNYK
MELNYK          MYSELECTS     Y          PUBLIC

  2 record(s) selected.

Summary

The DB2 UDB system catalog is a collection of tables that describe database objects. DB2 UDB provides a large number of views that you can query for information that will help you to understand the workings of your database. You can build individual queries or scripts to do this, or you can easily access the views through the DB2 Control Center. This article gives you a variety of examples of useful queries, but we have only scratched the surface. I encourage you to explore the catalog to further your understanding of what is available and how to retrieve the information that you need.

How to get useful information from the DB2 UDB system catalog

How to get useful information from the DB2 UDB system catalog