-DISPLAY DATABASE command (Db2)
The -DISPLAY DATABASE command displays status information about Db2 databases.
- 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. A logged-on z/OS user ID must be defined in RACF or a similar security server.
Syntax for -DISPLAY DATABASE
- 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:
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.
FL 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.
- 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, RBDPM, 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 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.
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.
- 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.
- 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.
- 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.
- For L-locks and drain locks, lock state has one of the following values:
- 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
U
A utility and SQL mass delete lock
- 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.
- If lock status has a value of H, lock duration has one of the following values:
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.
- Transaction locks (also called L-locks) are identified by the connection ID and correlation ID of their corresponding agents.
- 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
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 **********************