-DISPLAY STATS command (Db2)
The Db2 command DISPLAY STATS displays statistics about the use of resources by Db2 for certain processes.
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
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. 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.
- 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
- 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 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
orFTB
).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
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 *****************************
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.