14.3.8 Index statistical information

The following shows the statistical information about the index:

pdstedit VV-RR          ***** INDEX INFORMATION *****
INPUT          :/tmp/pdstj1 [1]
OUTPUT RANGE   :**/**/** **:**:** - **/**/** **:**:** [2]
CONTROL FILE     :CONTROL1 [18]
                                     [19]
FILE GROUP     :SERV1  : FILE1 FILE2 FILE3 FILE4 FILE5
                        FILE6 FILE7 FILE8 FILE9 FILE10
-----------------------------------------------------------------------------
EDIT TIME 1996/10/30 00:00:00 - 1996/10/31 00:00:00 [3]
            [4]   [5]   [6]   [7]   [8]   [9]   [10]  [11]
   *LN1*    C_SLK W_SLK XLOCK DEADL UNQCK UNQER REPOS REPG
            ----- ----- ----- ----- ----- ----- ----- -----
            [12]  [13] [14]
   *LN2*    SP_NM LVL ( NUM )
            ----- ---  -----
SERVER : sds [15]
*INDEX_ID :     196611 [16]
 *RDID    :          3 [17]
   *LN1*    4.32k     0     0     0     0     0     0     0
   *LN2*        0
*INDEX_ID :     196612
 *RDID    :          3
   *LN1*    5.31k     0     0     0     0     0     0     0
   *LN2*        0
*INDEX_ID :     196613
 *RDID    :          3
   *LN1*    4.64k     0 1.35k     0   684     0     0     0
   *LN2*        0
*INDEX_ID :     196614
 *RDID    :          3
   *LN1*       12     0 2.03k     0   684     0     0     0
   *LN2*        0
*INDEX_ID :     196615
 *RDID    :          3
   *LN1*    4.86k     0 1.94k     0     0     0     0     0
   *LN2*        0
-----------------------------------------------------------------------------
FILE KIND   LOG KIND   FIRST                 LAST                         NUM
STJ         idx        1996/10/30 10:38:19   1996/10/30 12:31:45           44
FJ          idx        ****/**/** **:**:**   ****/**/** **:**:**            0
-----------------------------------------------------------------------------

NO FILE KIND:LOG FILE NAME
 LOG KIND             FIRST                 LAST                         NUM
1 STJ      :/tmp/pdstj1
 sys                  1996/10/30 10:37:33   1996/10/30 12:33:07          112
 uap                  1996/10/30 10:36:38   1996/10/30 12:34:04         1854
 sql                  1996/10/30 10:36:37   1996/10/30 12:34:03        50971
 sop                  ****/**/** **:**:**   ****/**/** **:**:**            0
 dop                  ****/**/** **:**:**   ****/**/** **:**:**            0
 pcd                  ****/**/** **:**:**   ****/**/** **:**:**            0
 buf                  1996/10/30 10:38:19   1996/10/30 12:31:45           44
 fil                  1996/10/30 10:38:19   1996/10/30 12:31:45          440
 dfw                  1996/10/30 10:38:12   1996/10/30 12:33:31          103
 idx                  1996/10/30 10:38:19   1996/10/30 12:31:45           44

Explanation
  1. Name of input statistics unload file or name of the directory containing the input statistics unload file (maximum of 58 bytes)
  2. Output range (output start date/time to output end date/time)
  3. Edit period (collection start time to collection end time)
  4. Key value reference locks count in CHECK mode
    This is the number of times reference lock (PR) was acquired for a key value in the CHECK mode (if another user has a lock, this mode does not wait until the lock is released).
  5. Key value reference locks count in WAIT mode
    This is the number of times reference lock (PR) was acquired for a key value in the WAIT mode (if another user has a lock, this mode waits until the lock is released).
  6. Key value update locks count
    This is the number of times an update lock (EX) was acquired for a key value.
  7. Key value deadlocks count
    This is the number of times deadlock resulted from acquisition of an update lock (EX) for a key value.
  8. Unique checks count
    This is the number of times unique value checking was performed by the INSERT or UPDATE statement. This information is obtained only for a unique index.
  9. Unique errors count
    This is the number of times unique value checking performed by the INSERT or UPDATE statement resulted in an error because a key with the same value was found. This information is obtained only for a unique index.
    If this value is large, you need to reevaluate whether the unique index is required.
  10. Repositioning count (current position)
    This is the number of times repositioning (correction) was performed because the position of the referenced key value was changed by an addition or deletion made to another key value in the same page by another user.
  11. Repositioning count (current page)
    This is the number of times repositioning (correction) was performed, because the page containing the searched key value changed due to a page split caused by another user.
  12. Splits count (index split processing)#2
    This is the number of times split (index split processing)#1 occurred.
    If this value is large, you need to increase the percentage of PCTFREE for the index. If you are adding a large amount of data, you should use the database load utility (pdload).
  13. Number of levels affected by split#2
  14. Splits count for each level affected by split processing
    This is the sum of the splits counts of the upper-level pages for each level affected by split processing.
    If the splits count is large at a higher level, the key value may be too large for the size of the index page or the percentage of PCTFREE may be too large for the index.
  15. Server name
    This is the name of the server requesting output of index statistical information.
  16. Index ID
    This is the number of the index for which statistical information was edited.
    You can obtain the index name by searching the data dictionary table (SQL_INDEXES table) on the basis of the index number displayed.
  17. RDAREA ID
    This is the number of the RDAREA storing the index for which statistical information was edited.
    You can obtain the RDAREA name by searching the data dictionary table (SQL_RDAREAS table) on the basis of the RDAREA number displayed.
  18. Name of the control statement file (up to 58 bytes)
    This is the name of the control statement file specified by the -d option.
  19. Server name and file group name
    These are the server name in the control statement file specified by the -d option (name of server to be analyzed#3) and the corresponding file group names.
Notes
  1. Items 13 and 14 are displayed as many times as there are split levels.
  2. Items 12 through 14 are displayed only for an unload log file or if a system log file was read with the -d option specified.
  3. Items 4 through 11 display total values between each set of synchronization points. Because the maximum value between synchronization points is 65,535, the actual count might be greater than the value displayed.
  4. The following explains the levels affected by split processing:

    [Figure]

Explanation
The first split occurs on a leaf page. As a result of this split, a new page is created. If this split processing is completed after the new page is registered in the upper level page (intermediate page), this split affects level 2 (LVL), so the number of levels affected by the split is set to 2 (NUM).
If the split affects the page above index level 2, another new page is registered in the upper level page. If there is no space in this page, another split occurs on the upper level page. In this case, the number of levels affected by the split (LVL) becomes 3, and the count (NUM) is set to 3.

#1: A split is processing that divides the contents of a page and then adds the latter half of the contents to an unused page; this occurs when there is not enough space to add a key value in a used page in the index storage RDAREA.

#2: This information is displayed only when system log is read.

#3: The following servers are subject to analysis: