-DISPLAY STATS command (Db2)

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

Abbreviation: -DIS STATS

Environment for -DISPLAY STATS

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 STATS

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 for -DISPLAY STATS

index-traverse-count-spec

Option descriptions for -DISPLAY STATS

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 for -DISPLAY STATS

The beginning of DISPLAY STATS command output is indicated by different messages depending on the option keyword specified in the command.

Message DSNT783I indicates the beginning of the output of the command when INDEXMEMORYUSAGE or an equivalent abbreviation is specified. See Output for -DISPLAY STATS(INDEXMEMORYUSAGE) commands (DSNT783I).
Message DSNT788I indicates the beginning of the output of the command when LOGREADERTASKS or an equivalent abbreviation is specified. See Output for -DISPLAY STATS(LOGREADERTASKS) commands (DSNT788I).
Message DSNT830I indicates the beginning of the output of the command when INDEXTRAVERSECOUNT or an equivalent abbreviation is specified. See Output for -DISPLAY STATS(INDEXTRAVERSECOUNT) (DSNT830I).

Output for -DISPLAY STATS(INDEXMEMORYUSAGE) commands (DSNT783I)

The DSNT783I message displays output from the DISPLAY STATS(INDEXMEMORYUSAGE) command. The output provides the following information about indexes for which fast index traversal (FTB) is being used:

DBID  PSID  DBNAME   IX-SPACE LVL PART SIZE(KB)
----- ----- -------- -------- --- ---- --------
00279 00005 DB1      IX1      003 0001 00000816
******* DISPLAY OF STATS ENDED **************

The lines of output are in descending order, from the index that uses the largest amount of memory for fast index traversal, to the index that uses the smallest amount of memory for fast index traversal.

DBID: The internal identifier of the database that contains the index.
PSID: The page set descriptor of the index space.
DBNAME: The name of the database that contains the index.
IX-SPACE: The name of the index space.
LVL: The number of index levels in the index for which fast index traversal is being used. The FTBs (fast traverse blocks) for fast index traversal contain information for root and non-leaf index pages, but not for leaf pages.
PART: The index partition number.
SIZE(KB): The amount of memory that is used for fast index traversal, in kilobytes.

Output for multiple contiguous index partitions with the same memory usage statistics

When n partitions of an index have identical memory usage statistics, a line is displayed for the first partition, and lines for the rest of the partitions are omitted from the output. Those lines are replaced with the following output:

-THRU n

n is the number of the last partition with identical memory usage statistics.

Status of the output

The output continues or ends with the following message:

******* DISPLAY OF STATS status ***********************************

status

Possible values are:

CONTINUED: Indicates that more information will be displayed.
TERMINATED: Indicates that all information has been displayed.

Example for displaying information about memory use for fast index traversal (INDEXMEMORYUSAGE)

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)
```
Assume that the only index for which fast index traversal is being used in the Db2 subsystem where you issue the command has five levels of index pages, one partition, and uses 18339 KB of memory for fast index traversal. The result 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 result 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
```

Output for -DISPLAY STATS(LOGREADERTASKS) commands (DSNT788I)

The DSNT788I message is issued in response to the DISPLAY STATS(LOGREADERTASKS) command. It provides information about all current log reading task sessions in the following format:

DSNT788I  -SSID 
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  -SSID DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

SESSIONID

The unique session ID that identifies the log reading task.

STATUS

The current status of the log reading tasks:

RUNNING: The log reading task is currently reading log records.
READING: The log reading task is waiting for log records to be received and merged.
SUSP READ: The internal buffer is full because data was not received quickly enough.
SUSP EOS: The end of the log has been reached. The log reading task is waiting for new log records.

CURR. POSITION

The current RBA or LRSN position of the log reading task within the log record.

NUM RECS

The number of records that have been received.

AGE

The amount of time that has elapsed since the log reading task was started.

CORR-ID

The correlation ID that identifies the system task. CORR-ID starts with 014.LGRTSK and is appended with a 2-digit number to make it unique.

LOCATION

The name of the IBM® Db2 Analytics Accelerator for z/OS or the IBM Db2 for z/OS Data Gate instance that initiated the asynchronous log reader task. If the name is not available due to a transmission error, LOCATION will show the IP address of the remote requester.

Examples of displaying information about log reading tasks (LOGREADERTASKS)

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.

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.

Output for -DISPLAY STATS(INDEXTRAVERSECOUNT) (DSNT830I)

The DSNT830I message displays output from the DISPLAY STATS(INDEXTRAVERSECOUNT) command.

The output provides the following information about the index traverse count and FTB factor for one or more specified indexes. If multiple lines are included they are in descending order starting with the index with the highest FTB factor.

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

******* DISPLAY OF STATS ENDED ******************

DBID: The internal identifier of the database that contains the index.
PSID: The page set descriptor of the index space.
DBNAME: The name of the database that contains the index.
IX-SPACE: The name of the index space.
LVL: The number of index levels in the index for which fast index traversal is being used. The FTBs (fast traverse blocks) for fast index traversal contain information for root and non-leaf index pages, but not for leaf pages.
PART: The index partition number.
TRAV. COUNT: The number of index traversals for the index space as a moving average.
FTB FACTOR: Db2 calculates the FTB factor value from the index traversal count and other factors, such as index page splits, and internally applies its as a weight for evaluating whether specific indexes can benefit from index traversal.

Examples of displaying information about the number of index traversals (INDEXTRAVERSECOUNT)

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

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