-DISPLAY DATABASE (Db2)

The Db2 command DISPLAY DATABASE 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

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

The DISPLAY privilege allows you to display status information for any database. The resulting display lists those databases for which the primary authorization ID or any of the secondary authorization IDs has the DISPLAYDB privilege. Error messages are produced for those databases specified over which the set of privileges does not include one of the following privileges or authorities:

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 change A logged-on z/OS user ID must be defined in RACF or a similar security server. End of change

Syntax

Notes:

¹ If you specify the ONLY option without the SPACENAM() keyword, only the LIMIT, AFTER, and RESTRICT keywords apply.
² The OVERVIEW keyword cannot be specified with any other keywords except SPACENAM, LIMIT, and AFTER.
³ 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:

Option descriptions

( 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.
It is in COPY-pending, CHECK-pending, RECOVER-pending, group buffer pool RECOVER-pending, auxiliary CHECK-pending, or REORG-pending status.
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: Displays index objects that are in REBUILD- or RECOVER-pending status. This includes the restricted statuses RBDP, RBDP*, PSRBDP, LPL, and WEPR.
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

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 change If 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

Message DSNT361I indicates the beginning of the output of the command.

Examples

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      **********************