-DISPLAY DATABASE command (Db2)

The -DISPLAY DATABASE command displays status information about Db2 databases.

The DISPLAY DATABASE command displays information about the status of the following objects:
  • Db2 databases
  • Table spaces
  • Tables in segmented table spaces
  • XML table spaces
  • LOB table spaces
  • Index spaces within a database
  • Indexes on auxiliary tables
  • Partitions of partitioned table spaces
  • Partitions of index spaces

DISPLAY DATABASE RESTRICT indicates if a table space, index space, or partition is in any pending status. Use the ADVISORY option without the RESTRICT option to display any objects that are in an advisory pending status, such as the informational COPY-pending status or auxiliary warning advisory status.

In a data sharing environment, the command can be issued from any Db2 subsystem in the group that has access to the database.

Abbreviation: -DIS DB

Environment for -DISPLAY DATABASE

This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI).

Data sharing scope: Group

Authorization for -DISPLAY DATABASE

To execute this command, you must use a privilege set of the process that includes one of the following authorities. For a database privilege or authority, it must be held for each database that is specified.

  • DISPLAYDB privilege
  • DISPLAY privilege
  • DBMAINT authority
  • DBCTRL authority
  • DBADM authority
  • System DBADM authority
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

For implicitly created databases, the database privilege or authority can be held on the implicitly created database or on DSNDB04. If the DISPLAY DATABASE command is issued on specific table spaces or index spaces in an implicitly created database, ownership of the table spaces is sufficient to display status information about them. This means that the owner can display information about an implicitly created table space or index space if the command explicitly specifies that table space or index space name.

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. Start of changeA logged-on z/OS user ID must be defined in RACF or a similar security server.End of change

Syntax for -DISPLAY DATABASE

Read syntax diagramSkip visual syntax diagram DISPLAY DATABASE (,database-name*dbname1: dbname2dbname**dbname*dbname**dbstring1*dbstring2*)USECLAIMERSLOCKSLPLWEPRSPACENAM(,space-name*spacename1: spacename2spacename**spacename*spacename**spacestring1*spacestring2*)USECLAIMERSLOCKSLPLWEPRONLYONLY1PART(,integerinteger1: integer2)OVERVIEW2LRSN3LIMIT(50integer*) AFTER ACTIVE restrict-block ADVISORY(,ICOPY AUXWARBDPAREO*AREOR)
Notes:
  • 1 If you specify the ONLY option without the SPACENAM() keyword, only the LIMIT, AFTER, and RESTRICT keywords apply.
  • 2 The OVERVIEW keyword cannot be specified with any other keywords except SPACENAM, LIMIT, and AFTER.
  • 3 The LRSN keyword cannot be specified with any other keywords except SPACENAM and PART. Use the LRSN keyword only under the direction of IBM® Support.

restrict block:

Read syntax diagramSkip visual syntax diagramRESTRICT(,ACHKPCHKPCOPYGRECPLPLPRORBDPRECPREORPRORREPLSTOPUTUTROUTRWUTUTUT*WEPR)

Option descriptions for -DISPLAY DATABASE

( database-name )
Identifies one or more databases whose status is to be displayed. The following variations are accepted:
(database-name, ...)
Identifies one or more database names, separated by commas or blanks.
(*)
All databases that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.
(dbname1:dbname2)
All databases whose names, in UNICODE, are between dbname1 and dbname2 inclusive.
(dbname*)
All databases whose names begin with the string dbname that contains 1–7 characters.
(*dbname)
All databases whose names end with the string dbname that contains 1–7 characters.
(*dbname*)
All databases whose names contain the string dbname,where dbname that contains 1–6 characters.
(*dbstring1*dbstring2*)
All databases whose names contain the strings dbstring1 and dbstring2 that together contain a total of 2–5 characters.
SPACENAM
Specifies what space to display. If you use SPACENAM, you must also specify the corresponding database name. If (*) is used to specify multiple databases, SPACENAM(*) can be specified to display all objects in these databases.

Abbreviation: SPACE, SP

(spacename, ...)
One ore more index space names, separated by commas or blanks.
(*)
All table spaces or index spaces that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.
(spacename1:spacename2)
All table spaces or index spaces whose names, in UNICODE, are between spacename1 and spacename2 inclusive
(spacename*)
All table spaces or index spaces whose names begin with the string spacename that contains 1–7 characters.
(*spacename)
All table spaces or index spaces whose names end with the string spacename that contains 1–7 characters.
(*spacename*)
All table spaces or index spaces whose names contain the string spacename that contains 1–6 characters.
(*spacestring1*spacestring2*)
All table spaces or index spaces whose names contain the strings spacestring1 and spacestring2 that together contain a total of 2–5 characters.
USE
Displays the following information:
  • The applications and subsystems of the database or space that have internal Db2 resources allocated
  • The applications and subsystems of the database or space on whose behalf locks for the space are held or waited for
  • 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
CLAIMERS
Displays 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 their associated claims
  • The agent token for the claimer, if the claimer is local

CLAIMERS overrides both LOCKS and USE. If you specify CLAIMERS, any references to LOCKS or USE are ignored.

LOCKS
Displays the following information:
  • The applications and subsystems on whose behalf locks are held, waited on, or retained for the database or space
  • The transaction locks for all 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 that are associated with them
  • The retained locks for a resource
  • The page set or partition physical locks (P-locks) for a resource
  • The agent token for the lock holder, if the lock holder is local

LOCKS overrides USE. If both LOCKS and USE are specified, USE is ignored.

LPL
Displays logical page list entries.
WEPR
Displays write error page range information.
ONLY
Displays information about the specified object.
without SPACENAM() keyword
Displays only database information. Db2 does not display information for the spaces within the database you specified with the DISPLAY DATABASE command. If you specify ONLY, the following keywords are valid:
  • RESTRICT
  • LIMIT
  • AFTER
with SPACENAM() keyword
Displays the table spaces or indexes that have information requested by the DISPLAY DATABASE command. If you specify SPACENAM() ONLY, you must also specify one of the following keywords:
  • USE
  • CLAIMERS
  • LOCKS
  • LPL
  • WEPR

Db2 displays tables with table locks when you specify both the LOCKS and ONLY keywords.

PART ( integer , ...)
Indicates the partition number of one or more partitions whose status is to be displayed. The integer specified must identify a valid partition number for the corresponding space name and database name. integer can be written to designate one of the following values:
  • A list of one or more partitions
  • A range of all partition numbers that collate greater than or equal to integer1 and less than or equal to integer2
  • A combination of lists and ranges
OVERVIEW
Displays each object in the database on its own line, providing an easy way to see all objects in the database.

OVERVIEW limits the display to only the space names and space types that exist in the specified databases. The number of parts is displayed for any partitioned spaces.

The OVERVIEW keyword cannot be specified with any other keywords except SPACENAM, LIMIT, and AFTER.

LIMIT
Limits the number of messages to be displayed by the command.
( integer )
Is the maximum number of messages that are to be displayed. The default is 50 . The maximum number of messages that can be displayed is limited by the space available.
( * )
Limits the display to the space available.
AFTER
Displays the following information:
  • If only a database name is used, AFTER continues the display of all other databases whose names collate greater than that name.
  • If SPACENAM and a table space or index space name are used, AFTER continues the display to all other table spaces or index spaces in the same database whose names collate greater than that name.

AFTER cannot be used with more than one database name, table space name, or index space name, or with any pattern-matching character (*) within a database name, table space name, or index space name.

ACTIVE
Limits the display to table spaces or index spaces that have had internal Db2 resources allocated to applications and are in a started state or to databases that contain such spaces.

Abbreviation: A

Default: Using neither ACTIVE nor RESTRICT displays information on all databases defined to Db2.

RESTRICT
Limits the display to databases, table spaces, or indexes in a restricted status. This includes those page sets that have logical page list entries. Specifying one or more keywords further limits the display to the named objects only.

Abbreviation: RES

Use of a database is restricted if the database is in any of the following situations:
  • It is started for read-only processing.
  • It is started for read-or-replication-only processing.
  • It is started for utility-only processing.
  • It is stopped.
Use of a table space or index space is restricted if the table space or index space is in any of the following situations:
  • It is in one of the three situations listed previously.
  • It is being processed by a utility.
  • Start of changeFL 506 It is in COPY-pending, CHECK-pending, RECOVER-pending, group buffer pool RECOVER-pending, auxiliary CHECK-pending, REORG-pending, or REBUILD-pending empty status.End of change
  • It contains a page error range.
  • It contains pages in the logical page list (LPL).
Specify one or more of the following keywords to limit objects that are to be listed.
ACHKP
Displays objects in the auxiliary warning advisory status.
CHKP
Display objects that are in CHECK-pending status.
COPY
Display objects that are in COPY-pending status.
GRECP
Displays objects that are in group buffer pool RECOVER-pending status.
LPL
Displays logical page list entries.
PRO
Displays table space partitions that are in Persistent Read Only (PRO) restricted status.
RBDP
Start of changeDisplays index objects that are in REBUILD- or RECOVER-pending status. This includes the restricted statuses RBDP, RBDPM, RBDP*, PSRBDP, LPL, and WEPR.End of change
RECP
Displays objects that are in RECOVER-pending status, including the restricted status RECP, RECP*, LPL, and WEPR (write error page range).
REORP
Displays objects that are in REORG-pending status.
RO
Displays objects that are in read-only mode.
RREPL
Displays objects that are in read-or-replication-only mode.
STOP
Displays objects that are stopped, including the restricted statuses STOP, STOPE, STOPP, and LSTOP.
UT
Displays objects that are in utility access mode.
UTRO
Display objects that are serialized for utility access and available for read-only access.
UTRW
Display objects that are serialized for utility access and available for read-write access.
UTUT
Displays objects that are serialized for utility access and unavailable.
UT*
Displays objects that are in any utility access mode: UT, UTRW, UTRO, or UTUT.
WEPR
Displays write error page range information.
ADVISORY
Limits the display to indexes and table spaces to which read-write access is allowed, but for which some action is recommended.

Abbreviation: ADV

Use the DISPLAY DATABASE ADVISORY command without the RESTRICT option to determine when:
  • An index space is in the informational COPY-pending (ICOPY) advisory status.
  • A base table space or LOB table space is in the auxiliary warning (AUXW) advisory status.
  • An index space is in the REBUILD-pending (ARBDP) advisory status.
  • An index space is in the REORG (AREO*) advisory status.
  • A table space or an index space is in the REORG(AREOR) advisory status.
Specify one or more of the following keywords to limit the objects listed.
AUXW
Displays objects that are in the auxiliary warning advisory status.
ICOPY
Displays objects that are in the informational COPY-pending advisory status.
ARBDP
Displays objects that are in the advisory REBUILD-pending status.
AREO*
Displays objects that are in the advisory REORG-pending status.
AREOR
Displays objects that are in the advisory REORG-pending status.
LRSN
Use the LRSN option only under the direction of IBM Support. See Db2 commands for troubleshooting for details.

Usage notes for -DISPLAY DATABASE

Displaying Db2 catalog tables
You can always display the Db2 catalog tables. However, if a table space in the catalog containing information about user databases or user table spaces is stopped, those databases or table spaces cannot be displayed. Trying to display them will cause an error.

Start of changeIf you issue DISPLAY DATABASE LOCKS on the catalog (DSNDB06), you might see a lock held on SYSTSTSP with the correlation ID 020.DBCMD_06 or 020.DBCMD_07. This information indicates the lock that DISPLAY DATABASE itself needs and is normal.End of change

Displaying restricted and advisory status objects
To display all resources that are in restricted status, you must issue the DISPLAY DATABASE command twice. To display table spaces and indexes in restricted status, use the SPACENAM parameter with RESTRICT. To display databases in restricted status, do not use the SPACENAM parameter. Spaces could be unavailable even if they show RW mode if the database is in restricted status.

To display all resources that are in advisory status, issue the DISPLAY DATABASE ADVISORY command without the RESTRICT option.

Communications Database and Resource Limit Facility:
If the command specifies a table space or index space in the communications database or in the active resource limit facility database, the USE option displays the names of all members of the data sharing group that are using the specified table space or index space. Knowing which other members of the data sharing group might be using these spaces is useful when considering whether to drop table spaces and index spaces in the communications database and the resource limit facility database.
Displaying logical partitions:
If you issue DISPLAY DATABASE with the PART parameter for a logical partition of a type 2 index, Db2 does not display physical claimers and physical locks in the output. Nonpartitioned indexes are displayed with a type of 'IX' and with partition numbers displayed either as 'L' followed by a four-digit number, or as 'L*.' When the information for all the logical partitions is the same, partition numbers are displayed as 'L*.' If there is unique information to be displayed for a particular logical partition, L is used with that partition number to represent the logical part.
Displaying databases for declared temporary tables
DISPLAY DATABASE can display information about databases that are created with the AS TEMP option and the associated table spaces, but does not display information for declared temporary tables or index spaces that the database contains.
Displaying data-partitioned secondary indexes (DPSIs)
DISPLAY DATABASE can display information about data-partitioned secondary indexes. DPSIs are displayed with a type of 'IX'. The partition number is displayed as 'D' followed by the four-digit partition number, ranging from 0001 to 4096.
Displaying XML table spaces
DISPLAY DATABASE can display information about the status of XML table spaces. XML table spaces are displayed with a type of 'XS'.
Displaying clone table information
The information about base table objects and their clones is automatically displayed if a clone table exists. The information for both base objects and clones is displayed because clone objects can have different states than their base counterparts. DISPLAY indicates the current base and clone data set instance numbers.

Base table objects that have been cloned are noted with the character 'B' in the TYPE column of the DISPLAY output. Cloned objects are noted with the character 'C' in the TYPE column. Immediately following the 'B' or 'C' character is the data set instance number. The data set number is always a '1' or '2'. All the base table objects have the same data set instance number. All of the clone table objects have the same instance number, which is different than the base table objects' instance number. Data set instance number associations change during each data exchange.

You can also query the INSTANCE column of the SYSTABLESPACE catalog table to determine the current instance number associated with a particular base table.

If you drop a cloned table of a base table with a instance number '2' then the DISPLAY output of the base table still indicates that a clone once existed. The DISPLAY command output still shows 'B2' in the TYPE column to indicate that the base object is using instance number '2'. If you drop a cloned table and the base object instance number is '1' then the DISPLAY command outputs information that does not indicate that a clone ever existed. There is no 'B' character or instance number in the TYPE column.

Trace information for data sharing members
When this command with group scope is issued in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.

Output for -DISPLAY DATABASE

The DSNT361I message displays output from the DISPLAY DATABASE command. The output consists of information about the requested set of databases, table spaces, and index spaces.

The output from this DISPLAY DATABSE command has the following general structure. In your output, some of these lines might be included more than once and some might not be included at all. Which lines are included depends on the situation, as noted in the descriptions of the variables in this output.

DSNT360I - ***********************************************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    report-type-list
DSNT360I - ***********************************************************
DSNT362I -     DATABASE = database-name  STATUS = database-status
               DBD LENGTH = dbd-length
DSNT363I - PHYSICAL ERROR PAGE RANGE FOR DATA SET data-set-name  LOW=lowphy, HIGH=hiphy
DSNT397I -

After line DSNT397I, information about the requested table spaces, index spaces, and partitions is displayed. The general format depends on which options are specified in the DISPLAY DATABASE command, as follows.

OVERVIEW option
When the OVERVIEW option is specified on the DISPLAY DATABASE command, the output that follows line DSNT397I has the following general format:
NAME     TYPE PARTS
-------- ---- -----
space    type parts
space    type parts
space    type parts
CLAIMERS option
When the CLAIMERS option is specified on the DISPLAY DATABASE command, the output that follows line DSNT397I has the following general format:
NAME     TYPE PART  STATUS            CONNID   CORRID       CLAIMINFO
-------- ---- ----- ----------------- -------- ------------ --------
space    type part  status            conn-id  corr-id      claims
----                status-2          pages-2
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      claims
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      claims
DSNT352I - AGENT TOKEN agent token
USE option
When the USE option is specified on the DISPLAY DATABASE command, the output that follows line DSNT397I has the following general format:
NAME     TYPE PART  STATUS            CONNID   CORRID       USERID
-------- ---- ----- ----------------- -------- ------------ --------
space    type part  status            conn-id  corr-id      user-id 
----                status-2          pages-2
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      user-id 
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      user-id
LOCKS option
When the LOCKS option is specified on the DISPLAY DATABASE command, the output that follows line DSNT397I has the following general format:
NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
space    type part  status            conn-id  corr-id      lock
----                status-2          pages-2
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      lock
     -                   MEMBER NAME member-name
space    type part  status            conn-id  corr-id      lock
DSNT352I - AGENT TOKEN agent token
LPL option
When the LPL option is specified on the DISPLAY DATABASE command, the output that follows line DSNT397I has the following general format:
NAME     TYPE PART  STATUS            LPL PAGES
-------- ---- ----- ----------------- ------------------
space    type part  status            LPL-pages
----                status-2          pages-2
space    type part  status            LPL-pages
space    type part  status            LPL-pages
When WEPR, LOCKS, LPL, CLAIMERS, and USE are omitted
When the DISPLAY DATABASE command is issued without the WEPR, LOCKS, LPL, CLAIMERS, and USE options, the output that follows line DSNT397I has the following general format:
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
space    type part  status            lo-page  hi-page  cat-name piece
----                status-2          pages-2
space    type part  status            lo-page  hi-page  cat-name piece
     -THRU part
space    type part  status            lo-page  hi-page  cat-name piece

The DISPLAY DATABASE output ends with the following lines:

******* DISPLAY OF DATABASE name action ****************************
DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION

#db2z_cmd_displaydatabase__d407e2918 might also be included in your output if a problem occurred.

report-type-list
The options that were specified on the DISPLAY DATABASE command. Each option is delimited by a space. Possible values include one or more of the following options:
ACTIVE
The output includes all allocated databases.
GLOBAL
The output includes all requested database information.

report-type-list is GLOBAL if the DISPLAY DATABASE command does not include the ACTIVE, DISABLED, and RESTRICT options.

LOCKS
The output includes all connection identifiers and correlation identifiers of all applications that are holding, retaining, or waiting for locks on the specified table spaces or index spaces.
RESTRICT
The output includes all databases with restricted usage. Databases in RO and started UT mode are listed.
USE
The output includes the connection identifiers and correlation identifiers of all allocated applications.
CLAIMERS
The output includes the connection identifiers and correlation identifiers of all applications that are holding claims on the specified database.
LPL
The output includes the page number ranges of the pages that are included in the logical page list for the table space or index space.
OVERVIEW
The output includes all table spaces and index spaces that are contained within the databases that are specified.
database-name
The name of the database.
database- status
The status of the database. The value can be one or more of the following strings delimited by a comma. The maximum length of the string is 18 characters.
RO
The database is started for read activity only.
RW
The database is started for read and write activity.
STOP
The database is stopped.
STOPP
A stop operation for this database is in progress, is pending or has failed.

If the object seems to be in a persistent STOPP status, check whether the stop operation is still in progress. You can check this progress by looking at message DSNI003I or the output from the DISPLAY THREAD command with the TYPE(SYSTEM) option.

  • Message DSNI003I lists objects that could not be stopped.
  • In the DISPLAY THREAD output, look for message DSNV501I with STOP DATABASE listed as the command that is executing. If you see one of these messages, the STOP DATABASE command is still in progress for the thread that is identified in DSNV402I.

If the STOP operation timed out or failed, resolve the error by using the START DATABASE command.

For more information, see:

UT
The database is started for utility processing only.
UTRO
A utility that allows only RO access is in process on the database. If the utility was canceled before the object was drained, the object can allow SQL access, because the object was not altered by the utility.
UTRW
A utility that allows RW access is in process on the database.
UTUT
A utility that allows only UT access is in process on the database. If the utility was canceled before the object was drained, the object can allow SQL access, because the object was not altered by the utility.
dbd-length
The length of the database descriptor (DBD) in bytes.
DSNT363I - PHYSICAL ERROR PAGE RANGE FOR DATA SET data-set-name LOW=lowphy, HIGH=hiphy

This line lists any physical I/O errors on a data set. This information is displayed if such errors exist and the SPACENAM option was specified on the DISPLAY DATABASE command.

This message refers to physical pages in a data set that supports a page set. Page sets (table spaces or index spaces) are made up of supporting data sets. Physical pages are pages in supporting data sets. Physical pages are numbered from the beginning of the particular data set in which they reside.

data-set-name

The name of the data set with errors.

lowphy
The first page number in the range of physical pages that were being read when the I/O errors occurred. These physical pages are within the identified data set.
hiphy
The last page number in the range of physical pages that were being read when the I/O errors occurred.
space
The table space name, index space name, or table object ID (OBID).

The line that begins with space describes the status of a table space or index space in the specified database. (The database is listed in the preceding DSNT362I line.) This line is repeated as many times as needed to satisfy the DISPLAY DATABASE request.

The line that begins with space, the header line that precedes it, and the DISPLAY OF DATABASE line are always produced with a multiple line WTO and are never interrupted.

type
The type of object. type can have one of the following values:
TS
Table space
IX
Index space
LS
LOB table space
TB
Table
XS
XML table space

If the object is involved in cloning, type is appended with a B (for a base object) or a C (for a clone object) and a data set instance number. For example, a base object that is involved with cloning has B2 appended to the type indicator if it is represented by an instance number 2 data set. A base object that is no longer involved with cloning has no suffix.

Look at the CLONE column in the SYSIBM.SYSTABLESPACE catalog table to determine whether a table space and its objects are involved in cloning. A value of Y in the CLONE column indicates that cloning is active. The value in the INSTANCE column indicates the data set instance number for the current base objects.

For more information, see SYSTABLESPACE catalog table.

parts
The number of partitions in the table space, index space, LOB table space, or XML table space. This value is blank for a simple table space, simple index space, or segmented table space.
part
The partition number. This value is blank for a nonpartitioned table space or an index on a nonpartitioned table space. For nonpartitioned secondary indexes, this value is the logical partition number preceded by the character L (for example, L0001). For data-partitioned secondary indexes, this value is preceded by the character D (for example, D0001).

If this column is blank or contains L* for a partitioned object, the data that is reported in that line of output applies to the entire table space or index object.

status
The status of the table space or index space. Possible values are blank, or one or more of the following values, delimited by commas. The maximum length of the string is 17 characters.
ACHKP
The table space is in auxiliary CHECK-pending status. See Auxiliary CHECK-pending status.
ARBDP
The index space is in advisory REBUILD-pending status. This status is set when indexes with varying length columns are changed from VARYING NO to VARYING YES.
AREO*
The table space is in advisory REORG-pending status. See REORG-pending status.
AREST
The table space, index space, or physical partition of a table space or index space is in an advisory restart-pending status. If backout processing for the object is not already in progress, start backout processing. Either issue the RECOVER POSTPONED command or restart the Db2 subsystem with the subsystem parameter LBACKOUT set to AUTO.See LIMIT BACKOUT field (LBACKOUT subsystem parameter).
AUXW
The table space is in auxiliary warning status. See Auxiliary warning status.
CHKP
The table space or partition within a table space is in CHECK-pending status. See CHECK-pending status.
COPY
The table space or partition within a table space is in COPY-pending status. See COPY-pending status.
DBETE
Either one of the following situations exist:
  • The table space, partition within a table space, index space, index partition, or logical index partition is in DBETE status. The error occurred when the DBET states were being modified during log apply or must-complete processing. This condition prohibited Db2 from successfully updating the DBET states.
  • Db2 encountered an unexpected page set access error during restart, unrelated to processing a DBET exception state.
See DBETE status.
GRECP
The table space, table space partition, index space, or index space partition is in group buffer pool RECOVER-pending status. See Group buffer pool RECOVER-pending status and Data sharing status descriptions.

This status value is valid only on spaces. It does not apply to databases.

ICOPY
The object is in informational COPY-pending status. See Data sharing status descriptions and Informational COPY-pending status.
LPL
The object has pages or ranges of pages that are unavailable because of logical or physical damage. See Removing pages from the logical page list.
LSTOP
The logical partition is stopped.
PRO
The table space partition is in Persistent Read Only restricted status. See PRO restricted status..
PSRBD
The index space is in page set REBUILD-pending status. See REBUILD-pending status.
RBDP
The index space, index partition, or logical index partition is in REBUILD-pending status. See REBUILD-pending status.

For logical partitions, the RBDP status can be displayed as RBDP*.

RBDP*
The object is in REBUILD-pending status. See REBUILD-pending status..

The entire index is inaccessible to SQL applications. However, only the logical partition must be rebuilt.

RECP
The table space, table space partition, index space, index partition, or logical index partition is in RECOVER-pending status. See RECOVER-pending status.
REFP
The table space, table space partition, index space, or index space partition is in REFRESH-pending status. See REFRESH-pending status.
REORP
The table space or table space partition is in REORG-pending status. See REORG-pending status.
RESTP
The object is in the restart-pending status. See Restart-pending status.
RO
The table space, table space partition, index space, or index space partition is started for read-only activity.
RW
The table space, table space partition, index space, or index space partition is started for read and write activity.
STOP
The able space, table space partition, index space, or index space partition is stopped.
STOPE
The table space or index space was implicitly stopped because the log RBA in a page had a problem. Message DSNT500I or DSNT501I is issued when the error is detected to indicate the inconsistency. See DSNT500I or DSNT501I .
STOPP
A stop is pending for the database, table space, table space partition, index space, or index space partition.
UT
The table space, table space partition, index space, or index space partition is started for utility processing only.
UTRO
A utility that allows only RO access is in process on the table space, table space partition, index space, or index space partition.
UTRW
A utility that allows RW access is in process on the table space, table space partition, index space, or index space partition.
UTUT
A utility that allows only UT access is in process on the table space, table space partition, index space, or index space partition.
WEPR
This object has pages or ranges of pages that are unavailable because of physical damage. To reset this status, use either the RECOVER utility with the ERROR RANGE option or the LOAD utility with the REPLACE option.

If the status for a nonpartitioned index is blank, the status information is displayed at the logical partition level. If type is TB, status is blank.

In the final summary line for partitioned objects where space is either blank or L*, status can be only blank, DBETE or DBETE,RECP.

status-2
More statuses for the listed object. status-2 is displayed if all of the status information cannot fit on the preceding line.
THRU part
This line indicates that a range of partitions share the same status information.
part
The last partition in a range of partitions that share the same status information as the partition listed in the preceding line.
This line can be displayed only when the preceding line does not contain information for the following values:
  • lo-page
  • hi-page
  • cat-name
  • piece
conn-id
A connection identifier for the thread. conn-id can have one of the following values:
BATCH
The thread is from a batch region.
TSO
The thread is from a TSO terminal.
UTILITY
The thread is from a utility.
IMS identifier
The thread is from an IMS terminal.
CICS identifier
The thread is from a CICS terminal.
CONSOLE
The thread is from the console.
the connection name of the thread at the requesting location
The thread is a distributed database access thread that uses system-directed access and or application-directed access from a Version 3 or later Db2 requester.
SERVER
The thread is a distributed database access thread that uses application-directed access from a non-Db2 requester or from a Db2 Version 2 Release 3 requester.

If the DISPLAY DATABASE command included the LOCKS option and the displayed lock is a P-lock or a retained lock (either L-lock or P-lock), a conn-id value is not displayed. For page set P-locks, the Db2 member name is used to identify the lock.

corr-id
A correlation identifier of the thread that is associated with the space name. corr-id can have one of the following values:
job name
The JCL job name. This value is displayed if the thread is from a batch region.
TSO logon identifier
The TSO logon user ID. This value is displayed if the thread is from a TSO terminal.
pst#.psbname
This value is displayed if the thread is from an IMS terminal.
entry ID.thread number.tran id
This value is displayed if the thread is from a CICS terminal. See Db2 thread identification.
CONSOLE
This value is displayed if the thread is from the console.
0nn
This value is displayed if the thread is for a system agent. nn is a number.
028.DBAAnn
This value is displayed if the thread is a distributed database access thread that is being created. nn ranges from 01 to 99. This value indicates that the database access agent (DBAA) is in its connection processing. This processing consists of establishing the DBAA thread and validating and verifying the user's ID.

All RACF® requests are serialized on a single Db2 RACF service task, which might increase the validation time when multiple DBAAs are being created concurrently.

the correlation ID of the thread at the requesting location.
This value is displayed for an existing distributed database access thread that uses system-directed access or application-direct access from a Db2 requester.
extnam
The first 12 characters in the DDM external name (EXTNAM) parameter of the DDM EXCSAT command that is received as part of the SQL CONNECT. This value is displayed for an existing distributed database access thread that uses application-directed access from a requester that is not Db2.

If the DISPLAY DATABASE command included the LOCKS option and the displayed lock is a P-lock or a retained lock (either L-lock or P-lock), a corr-id value is not displayed. For page set P-locks, the Db2 member name is used to identify the lock.

claims
The type and duration of currently held claims. The claim consists 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 and has one of the following values:

A
The claim is held until deallocation.
C
The claim is held until the next commit point.
H
The claim is held across commit.
agent token
Holders or claimers. This information is displayed if the LOCKS option or the CLAIMERS option was specified on the DISPLAY DATABASE command and the agent information is available.

You can cancel the lock holder or claimer by using the CANCEL THREAD command. Alternatively, you can gather more information about the holder or claimer by using the DISPLAY THREAD command.

user-id
The user ID, which can be one of the following values:
TSO logon identifier
The TSO logon user ID. This value is displayed if the thread is from a TSO terminal.
AXBUSER
This value is displayed if the thread is from an IMS message-driven region and RACF is used.
sign-on ID or LTERM
The user ID that was used to sign on the terminal that is attached to IMS or the logical terminal (LTERM) value. This value is displayed if the thread is from an IMS message-drive region.
PSB name
The program specification block (PSB) name. This value is displayed if the thread is from an IMS nonmessage-driven region and RACF is not used.
user-id, term id, or tran id
The user ID, terminal ID, transaction ID, or other ID, as specified in the resource control table (RCT) if the thread is from CICS.
MEMBER NAME member-name

This line is displayed for each thread in a data sharing environment.

member-name
The name of the Db2 member that is accessing this object.

If the characters (CO) follow the member name, this member is the castout owner for the page set or partition. The castout owner for a page set or partition is the Db2 member that is responsible for doing all of the castout I/O for that page set or partition.

lock
A description of the following types of locks and the duration of the locks:
  • Transaction locks (also called L-locks) are identified by the connection ID and correlation ID of their corresponding agents.

    Multiple locks that are held by an agent are listed on multiple lines of the report followed by any locks on which the agent is waiting.

  • Physical locks (P-locks) and retained locks are identified by their Db2 data sharing member names.

    Multiple P-locks or retained locks that are held by a Db2 data sharing member are listed on multiple lines of the report. The output then lists any locks on which the member is waiting.

lock consists of the following information:

lock status-lock state,lock type,lock duration
lock status
The status of the lock, which can be one of the following values:
H
The lock is held by the agent or the Db2 member.
W
The agent or the member is waiting for the lock.
R
The lock is retained.
lock state
The mode of the lock as follows:
  • For L-locks and drain locks, lock state has one of the following values:
    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 table space or table lock with a protocol that does not lock a page while reading it, but does lock the page with update intention while updating it.
    X
    An exclusive lock.
  • For L-locks on LOB table spaces, lock state has one of the following values:
    IS
    A lock with intent to reserve space within the LOB table space
    IX
    A lock with intent to insert LOBs in the LOB table space.
    S
    A lock that reserves space within the LOB table space.
    SIX
    A lock that allows the owner to read and update LOBs.
    X
    An exclusive lock.
  • For P-locks, lock state has one of the following values:
    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.
lock type
lock type describes the type of locking and has one of the following values:
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
Start of changeUEnd of change
Start of changeA utility and SQL mass delete lockEnd of change
lock duration
lock duration describes the duration of the lock as follows:
  • If lock status has a value of H, lock duration has one of the following values:
    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 has a value of W, lock duration is a two-digit number that indicates the position of the waiter in the lock waiting queue for the local Db2 system.

    After the agent acquires the lock, the lock status value becomes H, and the lock duration value becomes one of the lock duration values that are listed for a lock status of H.

  • 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.

No explicit index space locks exist. However, all index spaces on tables are implicitly locked with transaction locks at the same level as the table spaces that contain those tables. P-locks are acquired by Db2 on index spaces and on table spaces.

LPL-pages
The pages in the specified space that are unavailable because of logical or physical damage.
pages-2
More pages that are in the logical page list (LPL) for the listed object. pages-2 is displayed if the list of pages cannot fit on the preceding line.
LPL pages can be listed in one of the following two formats:
  • Single pages, which are represented by the page number
  • A range of pages, which are represented by the starting page number followed by a dash and the ending page number in the range
lo-page
A hexadecimal number that identifies the lowest page number that contains a physical I/O error.

This value is displayed only if a data set for the specified table space or index space has physical I/O errors. Otherwise, if no physical I/O errors exist, this column is blank.

hi-page
A hexadecimal number that identifies the highest page number that contains an I/O error.

This value is displayed only if a data set for the specified table space or index space has physical I/O errors. Otherwise, if no physical I/O errors exist, this column is blank.

cat-name
The catalog that owns the data set with the I/O errors.

This value is displayed only if a data set for the specified table space or index space has physical I/O errors. Otherwise, if no physical I/O errors exist, this column is blank.

piece
An integer number that identifies the piece within the table space or index space with the error.

This value is displayed only if a data set for the specified table space or index space has physical I/O errors. Otherwise, if no physical I/O errors exist, this column is blank.

You can use the piece number to construct the name of the data set for the space that contains the physical error as follows: 

cat-name.DSNDBC.database-name.
space-name.I0001.Axxxx

Where xxxx is the piece number plus one and database-name is the name that is displayed in the preceding DSNT362I message. If the table space or index space is partitioned, the partition number is equal to the piece number plus one.

DISPLAY OF DATABASE name action
This line indicates the end of the DISPLAY DATABASE output.
name
The name of the database.
action
An indication of whether the output is complete. action can have one of the following values:
CONTINUES
The remaining table space and index space names within this database are continued to a new display.
TERMINATED
The remaining table space and index space names within this database are terminated.
ENDS
The display for this database is complete.

Error messages for DISPLAY DATABASE commands

DSNT399I INVALID space-name
See DSNT399I.
DSNT602I NO SPACES FOUND
See DSNT602I.
DSNT603I spacetype name NOT type
See DSNT603I.
DSNT604I NO INFORMATION AVAILABLE
See DSNT604I.

Examples for -DISPLAY DATABASE

Example: Displaying information about a single table space
The following command displays information about table space TBS33 in database CB3. The USE option causes connection-name (CONNID), correlation-id (CORRID), and authorization-ID (USERID) information to be displayed.
-DISPLAY DATABASE(CB3) SPACENAM(TBS33) USE

The output is similar to this output:

DSNT360I - ***********************************************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    GLOBAL USE
DSNT360I - ***********************************************************
DSNT362I -     DATABASE = CB3  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            CONNID   CORRID       USERID
-------- ---- ----- ----------------- -------- ------------ --------
TBS33    TS    0001 RW                LSS001   DSN2SQL      SYSADM
    -THRU      0004 
TBS33    TS

******* DISPLAY OF DATABASE CB3      ENDED      **********************
DSN9022I . DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Display information about database locks
The following command display information about table space TBS33 in database CB3. The LOCKS option displays lock information for table spaces and tables specified; LUWIDs and locations of any remote threads; and connection-name, correlation-id, and authorization ID information. 
-DISPLAY DATABASE(CB3) SPACENAM(TBS33) LOCKS

The output is similar to this output:

DSNT360I - ***********************************************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    GLOBAL LOCKS
DSNT360I - ***********************************************************
DSNT362I -     DATABASE = CB3  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
TBS33    TS    0001 RW  
    -THRU      0003 
TBS33    TS    0004 RW                LSS004   DSN2SQL      H-IS,S,C
TBS33    TS    0004 RW                LSS005   DSN2SQL      H-IS,S,C
TBS33    TS
******* DISPLAY OF DATABASE CB3      ENDED      **********************
Example: Displaying information about claimers
The following command displays information about table space TBS33 in database CB3. The CLAIMERS option displays claim types and durations; LUWIDs and locations of any remote threads; and connection-name, correlation-id, and authorization ID information.
-DISPLAY DATABASE(CB3) SPACENAM(TBS33) CLAIMERS

The output is similar to this output:

DSNT360I - ***********************************************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    GLOBAL CLAIMERS
DSNT360I - ***********************************************************
DSNT362I -     DATABASE = CB3  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            CONNID   CORRID      CLAIMINFO
-------- ---- ----- ----------------- -------- ------------ --------
TBS33    TS    0001 RW
    -THRU      0003 
TBS33    TS    0004 RW                LSS001   DSN2SQL      (RR,C)
TBS33    TS    0004 RW                LSS001   DSN2SQL      (WR,C)
TBS33    TS
******* DISPLAY OF DATABASE CB3      ENDED    *********************
Example: Displaying information about locks in a data sharing environment
The following command displays information about locks that are held for a table space. The database is in a data sharing environment. The application that is identified as LSS001 on member DB1G has locked partitions 1 and 2. LSS002 on member DB2G has locked partitions 1 and 3. Partition 4 has no locks held on it.
-DISPLAY DATABASE(DSN8D13A) SPACENAM(TSPART) LOCKS

The output is similar to this output:


NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
TSPART   TS    0001 RO                LSS001   DSN2SQL      H-IS,P,C
     -                   MEMBER NAME DB1G
TSPART   TS    0001 RO                                      H-S,PP,I
     -                   MEMBER NAME DB1G
TSPART   TS    0001 RO                LSS002   DSN2SQL      H-IS,P,C
     -                   MEMBER NAME DB2G
TSPART   TS    0001 RO                                      H-S,PP,I
     -                   MEMBER NAME DB2G
TSPART   TS    0002 RW                LSS001   DSN2SQL      H-IS,P,C
     -                   MEMBER NAME DB1G
TSPART   TS    0002 RW                                      H-S,PP,I
     -                   MEMBER NAME DB1G
TSPART   TS    0003 RW                LSS002   DSN2SQL      H-IS,P,C
     -                   MEMBER NAME DB2G
TSPART   TS    0003 RW                                      H-S,PP,I
     -                   MEMBER NAME DB2G
TSPART   TS    0004 RW
TSPART   TS

If Db2 cannot selectively lock the partitions, it must lock all of the partitions and the display looks similar to the following output. The LOCKINFO field shows a value of S, indicating that this is a table space lock. If partitions are held in different statuses, those statuses are listed below the table space locks.


NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
TSPART   TS                           LSS001   DSN2SQL      H-IS,S,C
     -                   MEMBER NAME DB1G
TSPART   TS                           LSS002   DSN2SQL      H-IS,S,C
     -                   MEMBER NAME DB2G
TSPART   TS    0001 RO                                      H-S,PP,I
     -                   MEMBER NAME DB1G
TSPART   TS    0002 RW                                      H-S,PP,I
     -                   MEMBER NAME DB2G
TSPART   TS    0003 RW                                      H-S,PP,I
     -                   MEMBER NAME DB2G
TSPART   TS    0004 RW
TSPART   TS
Example: Displaying information about table spaces with entries in the logical page list
The following command displays information about table spaces in database DSNDB01 that have entries in the logical page list. The LIMIT parameter limits the number of messages displayed to the space available. 
-DB1G DISPLAY DATABASE(DSNDB01) SPACENAM(*) LIMIT(*) LPL

The output is similar to this output:

***********************************************************
DSNT361I -DB1G *  DISPLAY DATABASE SUMMARY
               *    GLOBAL LPL
DSNT360I -DB1G
***********************************************************
DSNT362I -DB1G    DATABASE = DSNDB01  STATUS = RW
               DBD LENGTH = 8000
DSNT397I -DB1G
NAME     TYPE PART  STATUS            LPL PAGES
-------- ---- ----- ----------------- ------------------
DBD01    TS         RW,LPL,GRECP      000001,000004,00000C,000010
----                                  000039-00003C
BP1TS    TS         RW, LPL           00000002
SPT01    TS         RW
SCT02    TS         RW
SYSLGRNG TS         RW
SYSUTILX TS         RW
SYSLGRNX TS         RW,LPL,GRECP      000000-FFFFFF
DSNSCT02 IX         RW
DSNSPT01 IX         RW
DSNSPT02 IX         RW
DSNLUX01 IX         RW
DSNLUX02 IX         RW
DSNLLX01 IX         RW
DSNLLX02 IX         RW
******* DISPLAY OF DATABASE DSNDB01  ENDED      **********************
DSN9022I -DB1G DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Displaying lock information for partitions in a table space when Db2 cannot do selective partition locking
Suppose that Db2 is unable to selectively lock the partitions of table space TSPART, which is in database DSN8D13A. When you specify the following command, two applications are accessing TSPART, and the partitions have different statuses.
-DB1G DISPLAY DATABASE(DSN8D13A) SPACE(TSPART) PART(1,4) LOCKS

Db2 displays the locks as table space locks, as shown in the following output:

NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
TSPART   TS                           LSS001   DSN2SQL      H-IS,S,C
TSPART   TS                           LSS002   DSN2SQL      H-IS,S,C
TSPART   TS    0001 RO
TSPART   TS    0004 RW
Example: Displaying lock information for partitions in a table space when Db2 can do selective partition locking
Suppose that you have executed the ALTER TABLESPACE statement on table space TSPART so that TSPART is now defined with LOCKPART YES. LOCKPART YES causes Db2 to do selective partition locking on TSPART. When you issue the following command, two applications are accessing TSPART. The application identified by connection ID LSS001 has locked partitions 1 and 2. The application identified by connection ID LSS002 has locked partitions 1 and 3.
-DB1G DISPLAY DATABASE(DSN8D13A) SPACE(TSPART) PART(1:4) LOCKS

Db2 displays the locks as partition locks, as shown in the following output:

NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO
-------- ---- ----- ----------------- -------- ------------ ---------
TSPART   TS    0001 RO                LSS001   DSN2SQL      H-IS,P,C
TSPART   TS    0001 RO                LSS002   DSN2SQL      H-IS,P,C
TSPART   TS    0002 RW                LSS001   DSN2SQL      H-IS,P,C
TSPART   TS    0003 RW                LSS002   DSN2SQL      H-IS,P,C
TSPART   TS    0004 RW
Example: Displaying information about table spaces and index spaces that are in a restrictive status
The following command displays information about all table spaces and index spaces in the range of databases from DBKD0101 to DBKD0106 that are in a restrictive status. The LIMIT parameter limits the number of messages that are displayed to the available space.
-DISPLAY DATABASE(DBKD0101,DBKD0103) SPACENAM(*) RESTRICT LIMIT(*)

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    RESTRICTED
DSNT360I - ***********************************
DSNT362I -     DATABASE = DBKD0101  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
TLKD0101 TS         RW,RESTP
IUKD011A IX         RW,RESTP
IXKD011B IX         RW,RESTP
Example: Displaying information about table spaces that are in an auxiliary warning status or informational status
The following command displays information about all table spaces that have the auxiliary warning advisory status (AUXW), and all index spaces that are in informational COPY-pending status (ICOPY) in database DBIQUQ01.
-DISPLAY DATABASE(DBIQUQ01) SPACENAM(*) LIMIT(*) ADVISORY

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    ADVISORY
DSNT360I - ***********************************
DSNT362I -     DATABASE = DBIQUQ01  STATUS = RW
               DBD LENGTH = 8066
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
TPIQUQ01 TS    0001 RW,AUXW
     -THRU     0004
TPIQUQ01 TS
IAIQUQ01 IX         RW,ICOPY
IAIQUQ02 IX         RW,ICOPY
IAIQUQ03 IX         RW,ICOPY
IAIQUQ04 IX         RW,ICOPY
IPIQUQ01 IX    0001 RW,ICOPY
     -THRU     0004
IPIQUQ01 IX
IUIQUQ03 IX         RW,ICOPY
IXIQUQ02 IX         RW,ICOPY
******* DISPLAY OF DATABASE DBIQUQ01 ENDED      **********************
DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Displaying information about all objects in a database
The following command displays a list of all objects in database DB486A. This example shows five objects in the database. TS486A is a table space with four parts and TS486C is a nonpartitioned table space. IX486A is a nonpartitioned index for table space TS486A, IX486B is a partitioned index with four parts, and IX486C is a nonpartitioned index.
-DISPLAY DATABASE(DB486A) SPACE(*) OVERVIEW

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY 
           *    GLOBAL OVERVIEW
DSNT360I - ***********************************
DSNT362I -     DATABASE = DB486A  STATUS = RW
               DBD LENGTH = 4028
DSNT397I - 
NAME     TYPE PART
-------- ---- -----
TS486A   TS    0004
TS486A   TS
IX486A   IX   L0004
IX486A   IX   L*
IX486A   IX
IX486B   IX    0004
IX486B   IX
TS486C   TS
IX486C   IX
******* DISPLAY OF DATABASE DB486A   ENDED     **********************
DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Displaying table space information when table spaces are stopped
The following command displays information about all table spaces in database DB486B. In table space TS486X, partitions 1 through 6 are stopped. Partition 7 is in UT and COPY status, partition 8 is in STOP status, and partitions 9 and 10 are in RW status.
-DISPLAY DATABASE(DB486B) SPACE(*)

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY 
           *    GLOBAL OVERVIEW
DSNT360I - ***********************************
DSNT362I -     DATABASE = DB486B  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
TS486X   TS    0001 STOP
    - THRU     0006
TS486X   TS    0007 UT,COPY
TS486X   TS    0008 STOP
TS486X   TS    0009 RW
    - THRU     0010
TS486X   TS
IX486X   IX   L0001 RW
IX486X   IX   L0002 LSTOP
    - THRU    L0003
IX486X   IX   L0004 LSTOP
    - THRU    L0010
IX486X   IX   L*
IX486Y   IX    0001 RW
    - THRU     0010
IX486Y   IX
IX486Z   IX         RW
******* DISPLAY OF DATABASE DB486B   ENDED     **********************
DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Displaying information about all indexes in a database
The following command displays information about all indexes in the DBKD0101 database. INDEX2 contains information to be displayed at a logical level. Partitions 0001 and 0002 of INDEX3 are data-partitioned secondary indexes, as indicated by 'D' in the partition number.
-DISPLAY DATABASE(DBKD0101) SPACENAM(INDEX*)

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    RESTRICTED
DSNT360I - ***********************************
DSNT362I -     DATABASE = DBKD0101  STATUS = RW
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
INDEX1   IX    0001 RW
    -THRU      0002
INDEX1   IX
INDEX2   IX   L*    RW
INDEX3   IX   D0001 RW
    -THRU     D0002
INDEX3   IX
INDEX4   IX   L0001 RECP
INDEX4   IX   L0002 RW
INDEX4   IX
Example: Displaying information about all table spaces in a database that are in an advisory status
The following command displays information about all table spaces in the DBKD0103 database that are in the advisory REBUILD-pending status (ARBDP) and the advisory REORG-status (AREO*). Limit the number of messages that are displayed to the available space. Assume that you specify the following command:
-DISPLAY DATABASE(DBKD0103) SPACENAM(*) LIMIT(*) ADVISORY(ARBDP,AREO*)

The output is similar to the following output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    ADVISORY
DSNT360I - ***********************************
DSNT362I -     DATABASE = DBKD0103  STATUS = RW
               DBD LENGTH = 16142
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
PIX      IX    0001 RW,ARBDP,AREO*
    -THRU      0007
PIX      IX
Example: Displaying information about table space partitions
The following command displays information about table space DB2TSP in database Db2. The PART option includes both lists and ranges to display a very specific set of partitions. The table space underwent a single ROTATE operation before the final partitions were added.
-DISPLAY DATABASE(DB2) SPACENAM(DB2TSP) PART(1,2,4:6,9,10:12)

The output is similar to the following output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    GLOBAL
DSNT360I - ***********************************
DSNT362I -      DATABASE = DB2     STATUS = RW
                DBD LENGTH = 4028 
DSNT397I - 
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
DB2TSP   TS    0001 RW,AREO*
DB2TSP   TS    0002 RW,AREO*
DB2TSP   TS    0004 RW
    -THRU      0006
DB2TSP   TS    0009 RW
    -THRU      0012
******* DISPLAY OF DATABASE DB2 ENDED     ****************************
DSN9022I - DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
Example: Displaying information about a table space that is in informational COPY-pending status
The following command displays information about table spaces that are in an advisory status. The output includes any table spaces that are in the informational COPY-pending status.
-DISPLAY DATABASE(DBIQUQ01) SPACENAM(*) ADVISORY

The output is similar to this output:

DSNT360I - ***********************************
DSNT362I - DATABASE = DBIQUQ01 STATUS = RW
           DBD LENGTH = 8066
DSNT397I -
NAME     TYPE PART STATUS             HYERRLO  PHYERRHI CATALOG  PIECE
-------- ---- ---- ------------------ -------- -------- -------- -----
TPIQUQ01 TS    001 RW,AUXW
    -THRU      003 
TPIQUQ01 TS    004 RW,ICOPY
TPIQUQ01 TS
Example: Displaying information about clone tables
Suppose that some databases whose names begin with MYDB have clone objects. The following command displays information about base table and clone table objects, as well as objects that are not involved with cloning.
-DISPLAY DATABASE(MYDB*) SPACENAM(*) LIMIT(*)

The output is similar to this output:

DSNT360I - ***********************************
DSNT361I - *  DISPLAY DATABASE SUMMARY
           *    GLOBAL
DSNT360I - ***********************************
DSNT362I -     DATABASE = MYDBX  STATUS = STOP
               DBD LENGTH = 4028
DSNT397I -
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
CHKOUT2  TSB1  001      RW
    -THRU      003      
CHKOUT2  TSB1
XDEPT1   IXB1  001      RECP
XDEPT1   IXB1  002      RW
    -THRU      003     
XDEPT1   IXB1
CHKOUT2  TSC2  001      RO
CHKOUT2  TSC2  002      RW
    -THRU      003      
CHKOUT2  TSC2
XDEPT1   IXC2  001      ICOPY
XDEPT1   IXC2  002      RW
    -THRU      003
XDEPT1   IXC2
CHKOUT3  TS    001      RO
    -THRU      003
CHKOUT3  TS
XDEPT3   IX    001      RW
    -THRU      002
XDEPT3   IX    003      RO
XDEPT3   IX
Example: Displaying information about all objects that are in restricted status, when table spaces are in the DBETE status
The following command displays all objects that are in a restricted status.
-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT

When you do not specify specific database names, for objects that are in DBETE status, DISPLAY DATABASE displays the PSID values instead of database names and space names, as shown in the following output:

DSNT360I  @ ***********************************                         
DSNT361I  @ *  DISPLAY DATABASE SUMMARY 371                             
            *    RESTRICTED                                             
DSNT360I  @ ***********************************                         
DSNT362I  @     DATABASE = DB65309  STATUS = RW 373                     
               DBD LENGTH = 8066                                        
DSNT397I  @ 374                                                         
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE  
-------- ---- ----- ----------------- -------- -------- -------- -----  
****0002 UN         RW,DBETE,RECP                                       
****0005 UN         RW,DBETE,RECP                                       
****0007 UN         RW,DBETE,RECP                                       
******* DISPLAY OF DATABASE DB65309  ENDED      **********************
Example: Displaying information about specific objects that are in restricted status, when table spaces or index spaces are in the DBETE status
The following command displays objects in database DB65309 that are in a restricted status.
-DISPLAY DATABASE(DB65309) SPACENAM(*) RESTRICT

When you specify specific database names, for objects that are in DBETE status, DISPLAY DATABASE displays the object names, as shown in the following output:

DSNT360I  @ ***********************************                       
DSNT361I  @ *  DISPLAY DATABASE SUMMARY 395                           
            *    RESTRICTED                                           
DSNT360I  @ ***********************************                       
DSNT362I  @     DATABASE = DB65309  STATUS = RW 397                   
               DBD LENGTH = 8066                                      
DSNT397I  @ 398                                                       
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
PS65309G UN         RW,DBETE,RECP                                     
IP65309G UN         RW,DBETE,RECP                                     
IX65309G UN    L*   RW,DBETE,RECP                                     
******* DISPLAY OF DATABASE DB65309  ENDED      **********************