DB2 directory tables

DB2® for z/OS® maintains a set of tables (in database DSNDB01) called the DB2 directory. Authorized users can query the directory; however, it is primarily intended for use by DB2 and is therefore subject to change.

About these topics

These topics describe the directory tables that allow SELECT operations by describing the columns of those tables.

Users must have one of the following privileges to execute SELECT statements on the directory tables:

Installation SYSADM
SYSADM
SYSCTRL
ACCESSCTRL
DATAACCESS
SECADM
SQLADM
System DBADM
DBADM on DSNDB01
The SELECT privilege on a specific table

All directory tables are qualified by SYSIBM. Do not use this qualifier for user-defined tables.

The directory tables are not, in most cases, updated as part of DB2 operations.

Start of change The following table lists restrictions on SQL statements that reference directory tables: End of change

SQL statement	Restriction
ALTER TABLE	`table-name` cannot specify a directory table.
ALTER TABLESPACE	The table space cannot be altered if it is associated with a directory table.
CREATE INDEX	ON `table-name` or `aux-table-name` cannot specify a directory table.
CREATE TRIGGER	ON `table-name` cannot specify a directory table.
DELETE	FROM `table-name` cannot specify a directory table.
DROP	`table-name` cannot specify a directory table.
INSERT	INTO `table-name` cannot specify a directory table.
MERGE	INTO `table-name` cannot specify a directory table.
RENAME	`source-table-name` cannot specify a directory table.
UPDATE	`table-name` cannot specify a directory table.

Programming interface information

None of the directory table columns are part of the general-use programming interface. The column labeled "Use" in the table that describes the column indicates whether the column is part of the product-sensitive interface or is for internal, IBM use, only. The values that "Use" can assume are as follows:

Value: Meaning
S: Column is part of the product-sensitive interface
I: Column is for internal use only

For columns for which "Use" is I, the name of the column and its description do not appear in the explanation of the column.

Directory table spaces and indexes
DB2 directory tables are contained in certain table spaces and have indexes.
SYSIBM.DBDR table
The DBDR table stores one row for each DBD section.
SYSIBM.SYSDBD_DATA table
The SYSIBM.SYSDBD_DATA table is an auxiliary table for the SYSIBM.DBDR table.
SYSIBM.SPTR table
The SYSIBM.SPTR table stores Skeleton Package Table (SKPT) information
SYSIBM.SYSSPTSEC_DATA table
The SYSIBM.SYSSPTSEC_DATA table is an auxiliary table that contains package data for the SYSIBM.SPTR table.
SYSIBM.SYSSPTSEC_EXPL table
The SYSIBM.SYSSPTSEC_EXPL table is an auxiliary table that contains static package explain data for the SYSIBM.SPTR table.
SYSIBM.SYSLGRNX table
The SYSLGRNX table stores recovery log ranges that record the time an index space defined with COPY YES or a table space was open for updates. This provides an efficient way for DB2 to access the appropriate log records for recovery, rather than having to scan every record in the recovery log for a particular table.
SYSIBM.SYSUTIL table
The SYSUTIL table stores status information about DB2 utilities that are active or stopped. Each record is uniquely identified by the utility identifier. Each row of the table contains the information for one utility execution step. When the utility completes, the corresponding entries in the SYSUTIL table are deleted.
SYSIBM.SYSUTILX table
The SYSUTILX table is a dependent of the SYSUTIL table. A record is created in the SYSUTILX table when the amount of information in the parent record exceeds the record size of SYSUTIL. The rows in SYSUTILX are uniquely identified by the utility identifier and sequence number.

Parent topic: Additional information for DB2 SQL