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
- Name of input statistics unload file or name of the directory containing the input statistics unload file (maximum of 58 bytes)
- Output range (output start date/time to output end date/time)
- Edit period (collection start time to collection end time)
- 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).
- 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).
- Key value update locks count
This is the number of times an update lock (EX) was acquired for a key value.
- Key value deadlocks count
This is the number of times deadlock resulted from acquisition of an update lock (EX) for a key value.
- 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.
- 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.
- 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.
- 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.
- 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).
- Number of levels affected by split#2
- 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.
- Server name
This is the name of the server requesting output of index statistical information.
- 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.
- 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.
- Name of the control statement file (up to 58 bytes)
This is the name of the control statement file specified by the -d option.
- 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
- Items 13 and 14 are displayed as many times as there are split levels.
- 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.
- 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.
- The following explains the levels affected by split processing:
![[Figure]](figure/zu140010.gif)
- 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:
- The server with a name specified both in the -s option and in the control statement file
- All servers specified in the control statement file that are not specified in the -s option