-DISPLAY STATS command (Db2)

The Db2 command DISPLAY STATS displays statistics about the use of resources by Db2 for certain processes.

Abbreviation: -DIS STATS

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

To execute this command, you must use a privilege set of the process that includes one of the following privileges or authorities:

DISPLAY privilege
System DBADM authority
SYSOPR authority
SYSCTRL authority
SYSADM authority

Syntax

index-traverse-count-spec

Option descriptions

INDEXMEMORYUSAGE

Specifies that statistics about the current amount of memory that Db2 uses for fast traversal of indexes are displayed.

Abbreviations: IDXMEMUSE or IMU

INDEXTRAVERSECOUNT

Specifies the display of the FTB factor for a specific index, or for a specified number of indexes with the highest FTB factors in descending order. End of change

The following variations are accepted:

Abbreviations: IDXTRAVCNT or ITC

DBNAME

Specifies one or more databases for which index traverse counts are displayed.

Abbreviation: DBN

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 one or more indexes for which the index traverse counts are displayed.

Abbreviations: SPACE or SP

The following variations are accepted:

(spacename, ...): One or more named index space names, with comma or blank separators.
(*): All index spaces that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.
(spacename1:spacename2): All index spaces whose names, in UNICODE, are between spacename1 and spacename2 inclusive.
(spacename*): All index spaces whose names begin with the string spacename that contains 1–7 characters.
(*spacename): All index spaces whose names end with the string spacename that contains 1–7 characters.
(*spacename*): All index spaces whose names contain the string spacename that contains 1–6 characters.
(*spacestring1*spacestring2*): All index spaces whose names contain the strings spacestring1 and spacestring2 that together contain 2–5 characters.

PART

Indicates the partition number of one or more partitions to display.

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 integer 1 and less than or equal to integer2
A combination of lists and ranges

LOGREADERTASKS

Specifies that statistics about the log reading task (SRB) are displayed.

Abbreviations: LOGREADER or LRT

SCOPE(GROUP): Specifies the scope of the command when LOGREADERTASKS is specified. Displays information for log reader tasks that are running on all active members of the data sharing group. SCOPE(GROUP) cannot be specified when INDEXMEMORYUSAGE or INDEXTRAVERSECOUNT are specified. The option is ignored in a non-data-sharing environment.

LIMIT

Limits the number of messages that are to be displayed.

integer: The maximum number of messages that are to be displayed. This value must be in the range 1–9999. The maximum number of messages that can be displayed is limited by the space that is available.
( * ): Limits the display to the number of messages that fit in the available space.

Output

Message DSNT783I indicates the beginning of the output of the command when INDEXMEMORYUSAGE or an equivalent abbreviation is specified.
Message DSNT788I indicates the beginning of the output of the command when LOGREADERTASKS or an equivalent abbreviation is specified.
Message DSNT830I indicates the beginning of the output of the command when INDEXTRAVERSECOUNT or an equivalent abbreviation is specified.

Example: Displaying information about the use of fast index traversal

You can use the DISPLAY STATS command to display information about all indexes for which Db2 is using fast index traversal (sometimes called fast traverse blocks or FTB).Suppose that you issue the following command:

-DISPLAY STATS(INDEXMEMORYUSAGE)

In the Db2 subsystem on which you issue the command, the only index for which fast index traversal is being used has five levels of index pages, one partition, and uses 18339 KB of memory for fast index traversal.

The output is similar to the following output:


DSNT783I -
DBID  PSID   DBNAME   IX-SPACE LVL PART SIZE(KB)
----- ----- -------- -------- --- ---- --------
00278 00005 DB1      IX1      005 0001 00018339
******* DISPLAY OF STATS ENDED **************
DSN9022I - DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

Now suppose that you run the -DISPLAY STATS(INDEXMEMORYUSAGE) command on a Db2 subsystem on which fast index traversal is being used on five partitions of an index, and the number of levels of index pages and the amount of memory that is being used for fast index traversal is the same for all partitions.

The output is similar to the following output:


DSNT783I -
DBID PSID DBNAME   IX-SPACE LVL PART SIZE(KB)
---- ---- -------- -------- --- ---- --------
0017 0005 DB1      IX1      003 0006 00000061
    -THRU                       0010         
******* DISPLAY OF STATS ENDED **************
DSN9022I - DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

Examples: Displaying information about the number of index traversals

Example 1

Display the traverse count and FTB factor for a specific index partition

-DISPLAY STATS(INDEXTRAVERSECOUNT) DBNAME(DB1) SPACENAM(IX1) PART(1)

The example command returns output similar to:

DBID PSID DBNAME   IX-SPACE LVL PART TRAV. COUNT FTB FACTOR 
---- ---- -------- -------- --- ---- ----------- -----------  
0017 0005 DB1      IX1      003 0001 00000000999 -0000000001 
******* DISPLAY OF STATS ENDED *****************************

Example 2

Display the five index spaces with the highest FTB factors:

-DISPLAY STATS(ITC) DBNAME(DB1) LIMIT(5)

The example command returns output similar the to the following output, with the indexes in descending order starting with the highest FTB factor:

Output (traverse counts are in descending order)
DBID PSID DBNAME   IX-SPACE LVL PART TRAV. COUNT FTB FACTOR 
---- ---- -------- -------- --- ---- ----------- ----------- 
0017 0005 DB1      IX2.     003 0008 00000030021 00000030021 
0017 0005 DB1      IX2      003 0016 00000029999 00000029999 
0017 0005 DB1      IX3      003 0001 00000000999 00000000999 
0017 0005 DB1      IX1      003 0001 00000050099 00000000000 
0017 0005 DB1      IX2      003 0010 00000050001 -0000000001 
******* DISPLAY OF STATS ENDED *****************************

Examples: Displaying information about log reading tasks

Example: Display information about the status of log reading tasks on a local Db2 subsystem:

-DISPLAY STATS(LRT)

The output of the DISPLAY STATS(LOGREADERTASKS) command is similar to the following output:

DSNT788I  -DB2A 
SESSIONID        STATUS     CURR. POSITION       NUM RECS   AGE
  CORR-ID        LOCATION 
---------------- ---------- -------------------- ---------- ------
EE00982796560801 READING    0000000000018898F400 32071      89s
  014.LGRTSK01   SVLSSC0A
EE00DAC3E7842E02 SUSP EOS   00000000000188CA2280 30         6s
  014.LGRTSK02   DG653758
******* DISPLAY OF STATS ENDED     *********************************
DSN9022I  -DB2A DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

See message DSNT788I for a description of the information that is returned.

Example: Display information about log reader tasks running on all active members of the data sharing group:

-DISPLAY STATS(LRT) SCOPE(GROUP)

The output of the DISPLAY STATS(LOGREADERTASKS) command with the SCOPE(GROUP) option is similar to the following output:

DSNT788I  -DB2A 
SESSIONID        STATUS     CURR. POSITION       NUM RECS   AGE
  CORR-ID        LOCATION 
---------------- ---------- -------------------- ---------- ------
EE00982796560801 READING    00D9EE0DADE987256200 32071      89s
  014.LGRTSK01   SVLSSC0A
EE00DAC3E7842E03 SUSP EOS   00D9EE0DADE9872CA200 30         6s
  014.LGRTSK03   DG653758
******* DISPLAY OF STATS ENDED     *********************************
DSN9035I  -DB2A BEGIN OF DISPLAY FOR MEMBER: DB2B
---------------------------------------------------           
DSNT791I  -DB2B NO LOG READER TASK STATISTICS AVAILABLE       
--------END OF DISPLAY FOR MEMBER: DB2B    --------           
DSN9035I  -DB2A BEGIN OF DISPLAY FOR MEMBER: DB2C
---------------------------------------------------           
SESSIONID        STATUS     CURR. POSITION       NUM RECS   AGE
  CORR-ID        LOCATION 
---------------- ---------- -------------------- ---------- ------
EE00DAC2F9812803 SUSP EOS   00D9EE0DADE9872CA200 30         6s
  014.LGRTSK03   DG658121
******* DISPLAY OF STATS ENDED     *********************************
--------END OF DISPLAY FOR MEMBER: DB2C    --------                 
DSN9022I  -DB2A DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

See message DSNT788I for a description of the information that is returned.