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.
Authorization for SELECT for Db2 directory 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.
The following table lists restrictions on SQL statements that reference directory tables:
| 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 (basic) | ON table-name cannot specify a directory table. |
| CREATE TRIGGER (advanced) | 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. |
| TRANSFER OWNERSHIP | TABLE 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.
Db2 directory table spaces and indexes
The following table lists the table space and indexes for each directory table and lists the index fields for each index. The indexes are in ascending order.
The directory table space, tables, and indexes are primarily intended for use by Db2 and are therefore subject to change.
|
TABLE SPACE
DSNDB01. … |
TABLE
SYSIBM. … |
INDEX
SYSIBM. … |
INDEX FIELDS |
|---|---|---|---|
| DBD01 | DBDR | DSNDB01X | DBID.SECTION |
| SYSDBDXA | SYSDBD_DATA | DSNDB1XA | DBD_DATA |
| SCT02 | SCTR | DSNSCT02 |
SCTNAME.SCTSEC.
SPTSEQ |
| SPT01 | SPTR | DSNSPT01 |
SPTPID.SPTSEC.
SPTSEQ |
| DSNSPT02 |
version.SPTID.
SPTSEC.SPTSEQ |
||
| SYSSPUXA | SYSSPTSEC_DATA | DSNSPDXA | SPTSEC_DATA |
| SYSSPUXB | SYSSPTSEC_EXPL | DSNSPEXA | SPTSEC_EXPL |
| SYSLGRNX | SYSLGRNX | DSNLLX01 |
LGRDBID.LGRPSID.
LGRPART.LGRMEMB. LGRSLRSN |
| DSNLLX02 |
LGRDBID.LGRPSID.
LGRSLRSN |
||
| SYSUTILX | SYSUTIL | DSNLUX01 | USUUID |
| SYSUTILX | DSNLUX02 |
UTILID.SEQNO
|
SYSIBM.DBDR table
The DBDR table stores one row for each DBD section. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| INTEGER | Not used | S | |
| DBID | SMALLINT | DBID of the database | S |
| SECTION | SMALLINT | DBD section number | S |
| DBD_ROWID | ROWID | ID that is used to support the DBD_DATA column | S |
| DBD_DATA | BLOB(2G) | DBD data for the section | I |
SYSIBM.SYSDBD_DATA table
The SYSDBD_DATA table is an auxiliary table for the SYSIBM.DBDR table. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| DBD_DATA | BLOB(2G) | Contents of the DBD section. | I |
SYSIBM.SCTR table
The SCTR table stores Skeleton Cursor Tables (SKCT) information. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| SCTLL |
CHAR (4)
FOR BIT DATA |
The length of the record. | S |
| SCTNAME |
CHAR (14)
FOR BIT DATA |
The plan name, section number, and sequence number. | S |
| SCTDAT | VARCHAR(4028) | SKCT data. | I |
SYSIBM.SPTR table
The SPTR table stores skeleton package table (SKPT) information. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| SPTLL | INTEGER | The length of the record. | S |
| SPTLOCID | VARCHAR(128) | Package location. | S |
| SPTCOLID | VARCHAR(128) | Package collection ID. | S |
| SPTNAME | VARCHAR(128) | Package name. | S |
| SPTCONID |
CHAR(8)
FOR BIT DATA |
Package consistency token. | S |
| SPTRESV |
CHAR(2)
FOR BIT DATA |
Internal use only. | I |
| SPTSEC |
CHAR(4)
FOR BIT DATA |
Section number. | S |
| SPTSEQ |
CHAR(2)
FOR BIT DATA |
Sequence number within the section. | S |
| SPTBODY | VARCHAR(1) | Reserved for future IBM® use. | I |
| SPTVER | VARCHAR(64) | Package version. | S |
| SPT_ROWID | ROWID | ROWID that Db2 creates for the LOB columns in this table. | S |
| SPT_DATA | BLOB(2G) | Internal use only. | I |
| SPT_EXPLAIN | BLOB(2G) | Internal use only. | I |
SYSIBM.SYSSPTSEC_DATA table
The SYSSPTSEC_DATA table is an auxiliary table that contains package data for the SPTR table. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| SPT_DATA | BLOB(2G) | Contents of the SKPT section | I |
SYSIBM.SYSSPTSEC_EXPL table
The SYSSPTSEC_EXPL table is an auxiliary table that contains static package explain data for the SPTR table. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| SPT_EXPLAIN | BLOB(2G) | Contents of the SKPT section explain block | I |
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. The schema is SYSIBM.
| Column name | Data type | Description | Use |
|---|---|---|---|
| LGRDBID |
CHAR(2)
FOR BIT DATA |
DBID of the modified object | S |
| LGRPSID |
CHAR(2)
FOR BIT DATA |
OBID of the modified object | S |
| LGRUCDT | CHAR(6) | Modification date in the form mmddyy | S |
| LGRUCTM | CHAR(8) | Modification time in the form hhmmssth | S |
| LGRSRBA |
CHAR(10)
FOR BIT DATA |
Starting RBA | S |
| LGRSPBA |
CHAR(10)
FOR BIT DATA |
Stopping RBA | S |
| LGRPART | SMALLINT | Partition number in the table space or index space | S |
| LGRSLRSN |
CHAR(10)
FOR BIT DATA |
Starting LRSN of update log records for data sharing. Otherwise, the system clock value that corresponds to the first update log record. | S |
| LGRELRSN |
CHAR(10)
FOR BIT DATA |
Ending LRSN of update log records for data sharing. Otherwise, the system clock value that corresponds to the last update log record. | S |
| LGRMEMB | CHAR(2) | Data sharing member ID of the modifying Db2 subsystem. X'0000' for a non-data-sharing environment. | S |
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. The schema is SYSIBM.
| Name | Data type | Description | Use |
|---|---|---|---|
| USUUID | CHAR(16) | UTILID value that was passed in a JOB statement parameter | S |
| USUJOBNM | CHAR(8) | Job name from the JOB statement | S |
| USUAUID | CHAR(8) | Authorization ID of the invoker | S |
| USURDATE |
CHAR(4)
FOR BIT DATA |
Date of the utility | S |
| USUREL | CHAR(3) | Utility release level at restart time | S |
| USUIRQD | CHAR(1) | IBM required field | S |
| USULSIZE |
CHAR(4)
FOR BIT DATA |
List size | S |
| USULCUR |
CHAR(4)
FOR BIT DATA |
The object that is currently being processed or was last processed | S |
| USUUTNAM | CHAR(8) | Name of the currently executing utility | S |
| USUPHASE | CHAR(8) | Current phase of the currently executing utility | S |
| USUDSNU |
CHAR(2)
FOR BIT DATA |
Data set or piece number | S |
| USUDSNU2 |
CHAR(2)
FOR BIT DATA |
Ending number of the partition range | S |
| USUSTATU | CHAR(1) | Reserved | I |
| USUTREQ | CHAR(1) | Termination requested (Y or N) | S |
| USUFORCE | CHAR(1) | Element of USO forced (Y or N) | S |
| USURLOK | CHAR(1) | Reload was successful (Y or N) | S |
| USUCMPOK | CHAR(1) | Compatibility check passed (Y or N) | S |
| USURSFLG | BIT(8) | Utility restriction flags | S |
| USURTFLG | BIT(8) | Term settings | S |
| USURSFLG2 | BIT(8) | Utility flags | S |
| USUPOS |
CHAR(4)
FOR BIT DATA |
Relative USM position in the SYSIN DD statement | S |
| USUDONE |
CHAR(8)
FOR BIT DATA |
Number of objects processed | S |
| USUCKSUM |
CHAR(4)
FOR BIT DATA |
USU checksum | S |
| USUDBOB |
CHAR(2)
FOR BIT DATA |
DBID for the table space | S |
| USUPSID |
CHAR(2)
FOR BIT DATA |
PSID for the table space or index space | S |
| USUPSDD |
CHAR(2)
FOR BIT DATA |
Secondary PSID for RECOVER INDEX data page set | S |
| USUCATMGFRM |
CHAR(1)
FOR BIT DATA |
Saved catalog level for the release from which migration is done, from the DBD01 header page | S |
| USUOFLAG |
CHAR(1)
FOR BIT DATA |
Flags for object properties | S |
| USUDBNAM | CHAR(8) | Database name | S |
| USUSPNAM | CHAR(8) | Table space or index space name | S |
| USUMEMBR | CHAR(8) | Member name | S |
| USUOCATR |
CHAR(1)
FOR BIT DATA |
Saved catalog release level, from the DBD01 header page | S |
| USUOCATV |
CHAR(1)
FOR BIT DATA |
Saved catalog version level, from the DBD01 header page | S |
| USUOCATCV |
CHAR(1)
FOR BIT DATA |
Saved migration mode, from the DBD01 header page | S |
| USUOCATH |
CHAR(1)
FOR BIT DATA |
Saved highest version of the catalog | S |
| USUUDA |
CHAR(150)
FOR BIT DATA |
Utility-dependent data | S |
| USURTIME |
CHAR(4)
FOR BIT DATA |
Latest utility start time | S |
| USURLSN |
CHAR(6)
FOR BIT DATA |
Latest utility start LRSN | S |
| USURDATO |
CHAR(4)
FOR BIT DATA |
Original utility start date | S |
| USURTIMO |
CHAR(4)
FOR BIT DATA |
Original utility start time | S |
| USURLSNO |
CHAR(4)
FOR BIT DATA |
Original utility start LRSN | S |
| USUR5 |
CHAR(10)
FOR BIT DATA |
Reserved | I |
| USURCNTR |
CHAR(31)
FOR BIT DATA |
Generic counter or value holder | S |
| USURLSNX |
CHAR(10)
FOR BIT DATA |
Latest utility start LRSN value | S |
| USURLSOX |
CHAR(10)
FOR BIT DATA |
Original utility start LRSN value | S |
| USUR6 | Reserved | I | |
| USUUSTRN |
CHAR(27000)
FOR BIT DATA |
Utility-dependent restart information | S |
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.
| Column name | Data type | Description | Use |
|---|---|---|---|
| UTILID | CHAR(16) | The utility ID that identifies the parent record in SYSIBM.SYSUTIL | S |
| SEQNO | SMALLINT | The sequence number of this row | S |
| CHAR(12) | Reserved | I | |
| CHECKPOINT | VARCHAR(32000) | The overflow checkpoint/restart information | S |