DB2 for OS/390

Display database -- Fields and controls

Restricted databases and spaces

Options for space information to be displayed

Maximum number of messages

Type the maximum number of messages that you want to see, or accept the default value. Each informational line of the database status output begins with a DB2 for OS/390 message number, of the form DSNTxxxI. The maximum number of messages that can be displayed is limited by the space available. You can enter an asterisk (*) to set the limit at the maximum.

Active spaces

Select to display active databases and table spaces. This limits the database status display to table spaces or index spaces that have had internal DB2 resources allocated to applications and that are in a started state or to databases that contain such spaces.

Restricted databases and spaces

Select to include those table spaces or indexes that are in a restricted status. This includes those page sets that have logical page list entries.

Use of a table space or index space is restricted if:

It is started for read-only processing
It is started for utility-only processing
It is stopped
It is being processed by a utility
It is in copy pending, check pending, recover pending, or group recover pending status
It contains a page error range
It contains pages in the logical page list (LPL)

Display

Click the Display push button to generate new output based on the options you selected in this window.

Options for space information to be displayed

Default

A default display consists of an identifier (the name of the database in which the table space exists), the status of the table space and the database, the DBD length, and the error page ranges if available. The error page ranges are indicated by PHYERRLO and PHYERRHI and notify you of any physical I/O errors encountered on a data set. In the output, data-set-name is the name of the data set with errors. The variables PHYERRLO and PHYERRHI identify the range of physical pages, within the data set, that were being read when the I/O errors occurred. PHYERRLO is an 8 digit hexadecimal number representing the lowest page found in error, and PHYERRHI is the highest page found in error. Physical page numbers are assigned in relationship to the beginning of the data set in which the pages reside.

Page sets (table spaces or index spaces) are made up of supporting data sets. Physical pages are pages in supporting data sets that are numbered from the beginning of the particular data set in which they reside. The output might refer to physical pages in a data set that support (are part of) some page set. As seen in the following example, the PIECE column is a 3 digit integer that is a unique identifier for the data set supporting the page set that contains physical I/O errors.

11:44:32 DSNT360I - ****************************************************

11:44:32 DSNT361I - *  DISPLAY DATABASE SUMMARY

11:44:32            *          GLOBAL

11:44:32 DSNT360I - ****************************************************

11:44:32 DSNT362I -     DATABASE = DBPARTS  STATUS = RW

              DBD LENGTH = yyyy

11:44:32 DSNT397I -

NAME     TYPE PART STATUS           PHYERRLO  PHYERRHI CATALOG  PIECE

-------- ---- ---- ---------------- --------  -------- -------- -----

TSPART01 TS   001  RW,UTRO          00000002  00000004 DSNCAT   000

TSPART01 TS   002  RW,UTRO          00000009  00000013 DSNCAT   001

TSPART01 TS   003  RO

TSPART01 TS   004  STOP

TSPART01 TS   005  UT

******* DISPLAY OF DATABASE DBPARTS ENDED       **********************

11:45:15 DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION

The STATUS column shows at least one of the following:

CHKP: The object (a table space or a partition within a table space) is in the check pending state.
COPY: The object (a table space or a partition within a table space) is in the copy pending state. An image copy is required for this object.
GRECP: The object (a table space, table space partition, index space, index partition, or logical index partition) is in the group buffer pool recovery pending state. You'll see this only when DB2 is part of a data sharing group.
LPL: The object (a table space, table space partition, index space, index partition, or logical index partition) has pages on a logical page list.
PSRCP: The index space is in a page set recovery pending state.
RECP: The object (a table space, table space partition, index space, index partition, or logical index partition) is in the recovery pending state. If an asterisk appears (RECP*), it indicates that a logical index partition is in RECP, but that the entire index is inaccessible to SQL requests.
RO: The object (database, table space, table space partition, index space, index partition, or logical index partition) is started for read-only activity.
RW: The object (database, table space, table space partition, index space, index partition, or logical index partition) is started for read and write activity.
STOP: The object (database, table space, table space partition, index space, index partition, or logical index partition) is stopped.
STOPE: The table space or index space was implicitly stopped because DB2 detected an invalid log RBA or LRSN in one of its pages.
STOPP: A stop is pending for the object (database, table space, table space partition, index space, index partition, or logical index partition).
UT: The object (database, table space, table space partition, index space, index partition, or logical index partition) is started for utility processing only.
UTRO: A utility is in process on an object (table space, table space partition, index space, index partition, or logical index partition) that allows only RO access.
UTRW: A utility is in process on an object (table space, table space partition, index space, index partition, or logical index partition) that allows only RW access.
UTUT: A utility is in process on an object (table space, table space partition, index space, index partition, or logical index partition) that allows only UT access.

Usage

Selecting the usage radio button displays information about who and what is using the database. For example:

The applications and subsystems of the database or space that has internal DB2 resources allocated.
The applications and subsystems of the database or space on whose behalf locks for the space are held or waited upon.
The connection IDs, correlation IDs, and authorization IDs for all applications allocated to spaces and partitions whose statuses are displayed.
The LUWID and location of any remote threads accessing the local database.

An example of the output for partitioned table space SALES_HX in database DSN8D51A with usage specified:

11:44:32 DSNT360I - ****************************************************

11:44:32 DSNT361I - *  DISPLAY DATABASE SUMMARY

11:44:32            *        GLOBAL USE

11:44:32 DSNT360I - ****************************************************

11:44:32 DSNT362I -     DATABASE = DBPARTS  STATUS = RW

    DBD LENGTH = yyyy

11:44:32 DSNT397I -

NAME     TYPE PART STATUS           CONNID   CORRID       USERID

-------- ---- ---- ---------------- -------- ------------ ----------

SALES_HX TS   002  RW               BATCH    TSOUSER1     BAT1

DB2NET.LUND0.143992156557=1 ACCESSING DATA FOR USIBMSTODB22

SALES_HX TS   005  RW               IMSA     0012IMSPSB01 BAT2

SALEX_HX TS   067  RO               BATCH    TSOUSER1     BAT1

******* DISPLAY OF DATABASE DSN8D51A ENDED       **********************

11:45:15 DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION

Locks

The Locks radio button tells you which programs are holding locks on the database or spaces. In particular, you can obtain the following information:

The applications and subsystems on whose behalf locks are held, waited upon, or retained for the database or space
The transaction locks for all databases, table spaces, tables, index spaces, and partitions whose statuses are displayed
The connection IDs, correlation IDs, and authorization IDs for all applications allocated to spaces whose statuses are displayed
The LUWID and location of any remote threads accessing the local database
The drain locks for a resource held by running jobs
The logical partitions that have drain locks and the drain locks associated with them
The retained locks for a resource
The page set or partition physical locks (P-locks) for a resource

The LOCKINFO describes the type and duration of locks used by corresponding agents identified by their connection and correlation IDs (for transaction locks, which are also called L-locks) or used by corresponding subsystems identified by their DB2 data sharing member names (for physical locks, which are called P-locks, and retained locks).

Multiple locks held by an agent are listed on multiple lines of the report followed by any locks the agent is currently awaiting. Multiple P-locks or retained locks held by a DB2 data sharing member are listed on multiple lines of the report followed by any locks the member is currently awaiting.

The LOCKINFO consists of a 'lock status', followed by a dash '-', a 'lock state', a 'lock type', and a 'lock duration' delimited by commas.

The 'lock status' is a value of:

H The lock is held by the agent or the DB2 member.
W The agent or the member is awaiting the lock.
R The lock is retained.

The 'lock state' describes the mode of lock as follows:

For L-locks and drain locks:

IS A lock with read intentions.
IX A lock with update intentions.
S A lock with read-only capability.
U A lock with update capability.
SIX A lock with a protocol that does not lock a page while reading, but locks the page with update intention while updating.
X An exclusive lock.

For P-locks:

IS This DB2 member has R/O interest. The page set or partition is group buffer pool (GBP) dependent.
IX This DB2 member has R/W interest. The page set or partition is GBP-dependent.
S This DB2 member has R/O interest. The page set or partition is not GBP-dependent.
SIX This DB2 member has R/W interest. The page set or partition is GBP-dependent.
NSU This DB2 member has R/W interest. The page set or partition is GBP-dependent.
X This DB2 member has R/W interest. The page set or partition is not GBP-dependent.

The 'lock type' describes the type of locking as follows:

S A table space L-lock.
T A table L-lock.
C A cursor-stability read drain lock.
R A repeatable-read drain lock.
W A write drain lock.
P A partition L-lock.
PP A page set or partition P-lock.

If the 'lock status' is H, the 'lock duration' describes the duration of the lock:

A The lock is freed at deallocation.
C The lock is freed at commit.
H The lock is freed when all cursors are closed.
M The lock is freed by the system.
P The lock is freed when the plan is complete.
I The lock is a page set or partition P-lock. It is released when the page set or partition data set is closed.

If the 'lock status' is W, the 'lock duration' is a 2-digit number indicating the waiter's position in the local DB2 system's lock waiting queue. When this lock is acquired, its duration is the same as all locks held by the user.

For a P-lock, the lock is owned by the system, not the user, and its duration is not related to the duration of the locks that are owned by the user.

Retained locks do not have a lock duration.

There are no explicit index space locks. However, all index spaces on tables are implicitly locked at the same level as the table spaces containing those tables. P-locks are acquired by DB2 on index spaces as well as on table spaces.

Current claims

The Current claims radio button displays information about the claims that are on certain table spaces, index spaces, and partitions.

The column headers in this command are as follows:

NAME TYPE PART STATUS CONNID CORRID CLAIMINFO

CLAIMINFO is the type and duration of currently held claims. The claim is composed of a claim class descriptor and a claim duration descriptor.

The claim class descriptor has one of the following values:

CS Cursor stability claim class
RR Repeatable-read claim class
WR Write claim class

The claim duration descriptor describes the duration of the claims as follows:

A The claim is held until deallocation.
C The claim is held until the next commit point.
H The claim is held across commit.

A claim is a notification to DB2 that an object is being accessed. When an application first accesses an object, within a unit of work, it makes a claim on the object. It releases the claim at the next commit point. A claim indicates to DB2 that there is activity on or interest in a particular page set or partition. Claims prevent drains from occurring until the claim is released. By selecting the Current claims radio button you can obtain the following information:

The claims on all table spaces, index spaces and partitions whose statuses are displayed
The LUWID and location of any remote threads accessing the local database
The connection IDs, correlation IDs, and authorization IDs for all applications allocated to spaces whose statuses are displayed
The logical partitions that have logical claims and the claims associated with them.

Logical page list entries

Logical page list entries shows a list of pages in error that cannot be referenced by applications until the pages are recovered.