Calculates statistics on the database to determine if tables need to be reorganized.
Scope
This command can be issued from any node in the db2nodes.cfg file. It can be used to update table and index statistics in the catalogs.
Authorization
One of the following:
Required Connection
Database
Command Syntax
>>-REORGCHK----+----------------------------+------------------->
| .-UPDATE--. |
'--+-CURRENT-+---STATISTICS--'
>-----+---------------------------+----------------------------><
| .-USER-------. |
'-ON TABLE--+-SYSTEM-----+--'
+-ALL--------+
'-table-name-'
Command Parameters
If a table partition exists on the node where REORGCHK has been issued, RUNSTATS executes on this node. If a table partition does not exist on this node, the request is sent to the first node in the nodegroup that holds a partition for the table. RUNSTATS then executes on that node.
| Note: | For typed tables, the specified table name must be the name of the hierarchy's root table. |
Examples
The following shows sample output from the command
db2 reorgchk update statistics on table system
run against the SAMPLE database:
Doing RUNSTATS .... Table statistics: F1: 100*OVERFLOW/CARD < 5 F2: 100*TSIZE / ((FPAGES-1) * (TABLEPAGESIZE-76)) > 70 F3: 100*NPAGES/FPAGES > 80 CREATOR NAME CARD OV NP FP TSIZE F1 F2 F3 REORG ------------------------------------------------------------------------------- SYSIBM SYSCHECKS - - - - - - - - --- SYSIBM SYSCOLAUTH - - - - - - - - --- SYSIBM SYSCOLCHECKS - - - - - - - - --- SYSIBM SYSCOLDIST - - - - - - - - --- SYSIBM SYSCOLUMNS 735 0 25 25 92610 0 95 100 --- SYSIBM SYSCONSTDEP - - - - - - - - --- SYSIBM SYSDATATYPES 13 0 1 1 1027 0 - 100 --- SYSIBM SYSDBAUTH 3 0 1 1 90 0 - 100 --- SYSIBM SYSEVENTMONITORS - - - - - - - - --- SYSIBM SYSEVENTS - - - - - - - - --- SYSIBM SYSFUNCPARMS 254 0 6 6 21590 0 100 100 --- SYSIBM SYSFUNCTIONS 104 0 8 8 728 0 2 100 -*- SYSIBM SYSINDEXAUTH 2 0 1 1 112 0 - 100 --- SYSIBM SYSINDEXES 57 17 3 5 9063 29 56 60 *** SYSIBM SYSKEYCOLUSE 4 0 1 1 268 0 - 100 --- SYSIBM SYSPLAN 22 0 2 2 154 0 3 100 -*- SYSIBM SYSPLANAUTH 41 0 1 1 1804 0 - 100 --- SYSIBM SYSPLANDEP - - - - - - - - --- SYSIBM SYSRELS - - - - - - - - --- SYSIBM SYSSECTION 4 0 1 1 260 0 - 100 --- SYSIBM SYSSTMT 4 0 1 1 268 0 - 100 --- SYSIBM SYSTABAUTH 68 0 2 2 3944 0 98 100 --- SYSIBM SYSTABCONST 2 0 1 1 132 0 - 100 --- SYSIBM SYSTABLES 69 0 6 6 483 0 2 100 -*- SYSIBM SYSTABLESPACES 3 0 1 1 225 0 - 100 --- SYSIBM SYSTRIGDEP - - - - - - - - --- SYSIBM SYSTRIGGERS - - - - - - - - --- SYSIBM SYSVIEWDEP 42 0 1 1 2646 0 - 100 --- SYSIBM SYSVIEWS 32 0 5 5 3168 0 19 100 -*-
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100*(KEYS*(ISIZE+8)+(CARD-KEYS)*4) / (NLEAF*INDEXPAGESIZE) > 50
F6: (100-PCTFREE)*(INDEXPAGESIZE-96)/(ISIZE+12)**(NLEVELS-2))*(INDEXPAGESIZE-96)/
(KEYS*(ISIZE+8)+(CARD-KEYS)*4) < 100
CREATOR NAME CARD LEAF LVLS ISIZE KEYS F4 F5 F6 REORG
-------------------------------------------------------------------------------
Table: SYSIBM.SYSCHECKS
SYSIBM IBM37 - - - - - - - - ---
Table: SYSIBM.SYSCOLAUTH
SYSIBM IBM42 - - - - - - - - ---
SYSIBM IBM43 - - - - - - - - ---
Table: SYSIBM.SYSCOLCHECKS
SYSIBM IBM38 - - - - - - - - ---
SYSIBM IBM39 - - - - - - - - ---
Table: SYSIBM.SYSCOLDIST
SYSIBM IBM46 - - - - - - - - ---
Table: SYSIBM.SYSCOLUMNS
SYSIBM IBM01 735 12 2 33 735 97 64 11 ---
SYSIBM IBM24 735 1 1 20 10 85 - - ---
Table: SYSIBM.SYSCONSTDEP
SYSIBM IBM44 - - - - - - - - ---
SYSIBM IBM45 - - - - - - - - ---
Table: SYSIBM.SYSDATATYPES
SYSIBM IBM40 13 1 1 20 13 100 - - ---
SYSIBM IBM41 13 1 1 2 13 100 - - ---
Table: SYSIBM.SYSDBAUTH
SYSIBM IBM12 3 1 1 17 3 100 - - ---
Table: SYSIBM.SYSEVENTMONITORS
SYSIBM IBM47 - - - - - - - - ---
Table: SYSIBM.SYSEVENTS
SYSIBM IBM48 - - - - - - - - ---
Table: SYSIBM.SYSFUNCPARMS
SYSIBM IBM31 254 2 2 30 104 100 58 77 ---
SYSIBM IBM32 254 3 2 51 154 96 79 37 ---
SYSIBM IBM33 254 1 1 6 1 100 - - ---
Table: SYSIBM.SYSFUNCTIONS
SYSIBM IBM25 104 1 1 30 104 100 - - ---
SYSIBM IBM26 104 1 1 27 104 86 - - ---
SYSIBM IBM27 104 1 1 18 50 86 - - ---
SYSIBM IBM28 104 1 1 16 2 99 - - ---
SYSIBM IBM29 104 1 1 4 104 100 - - ---
SYSIBM IBM30 104 2 2 53 104 86 79 56 ---
Table: SYSIBM.SYSINDEXAUTH
SYSIBM IBM17 2 1 1 47 2 100 - - ---
SYSIBM IBM18 2 1 1 30 2 100 - - ---
Table: SYSIBM.SYSINDEXES
SYSIBM IBM02 57 1 1 17 57 100 - - ---
SYSIBM IBM03 57 1 1 25 57 100 - - ---
Table: SYSIBM.SYSKEYCOLUSE
SYSIBM IBM35 4 1 1 57 4 100 - - ---
SYSIBM IBM36 4 1 1 44 2 100 - - ---
Table: SYSIBM.SYSPLAN
SYSIBM IBM07 22 1 1 16 22 100 - - ---
SYSIBM IBM19 22 1 1 8 1 100 - - ---
Table: SYSIBM.SYSPLANAUTH
SYSIBM IBM13 41 1 1 33 41 100 - - ---
SYSIBM IBM14 41 1 1 16 22 100 - - ---
Table: SYSIBM.SYSPLANDEP
SYSIBM IBM08 - - - - - - - - ---
SYSIBM IBM09 - - - - - - - - ---
Table: SYSIBM.SYSRELS
SYSIBM IBM20 - - - - - - - - ---
Table: SYSIBM.SYSSECTION
SYSIBM IBM10 4 1 1 20 4 100 - - ---
Table: SYSIBM.SYSSTMT
SYSIBM IBM11 4 1 1 20 4 100 - - ---
Table: SYSIBM.SYSTABAUTH
SYSIBM IBM15 68 1 1 38 68 100 - - ---
SYSIBM IBM16 68 1 1 21 68 100 - - ---
Table: SYSIBM.SYSTABCONST
SYSIBM IBM34 2 1 1 44 2 100 - - ---
Table: SYSIBM.SYSTABLES
SYSIBM IBM00 69 1 1 21 69 95 - - ---
SYSIBM IBM21 69 1 1 12 3 100 - - ---
SYSIBM IBM22 69 1 1 6 1 100 - - ---
SYSIBM IBM23 69 1 1 6 1 100 - - ---
Table: SYSIBM.SYSTABLESPACES
SYSIBM IBM49 3 1 1 14 3 100 - - ---
SYSIBM IBM50 3 1 1 8 1 100 - - ---
Table: SYSIBM.SYSTRIGDEP
SYSIBM IBM51 - - - - - - - - ---
SYSIBM IBM52 - - - - - - - - ---
Table: SYSIBM.SYSTRIGGERS
SYSIBM IBM53 - - - - - - - - ---
SYSIBM IBM54 - - - - - - - - ---
Table: SYSIBM.SYSVIEWDEP
SYSIBM IBM05 42 1 1 42 42 100 - - ---
SYSIBM IBM06 42 1 1 20 32 100 - - ---
Table: SYSIBM.SYSVIEWS
SYSIBM IBM04 32 1 1 20 32 100 - - ---
-------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
The terms for the table statistics (formulas 1-3) mean:
Table reorganization is suggested when the results of the calculations exceed the bounds set by the formula.
For example, --- indicates that, since the formula results of F1, F2, and F3 are within the set bounds of the formula, no table reorganization is suggested. The notation *-* indicates that the results of F1 and F3 suggest table reorganization, even though F2 is still within its set bounds. The notation *-- indicates that F1 is the only formula exceeding its bounds.
| Note: | The table name is truncated to 30 characters, and the ">" symbol in the thirty-first column represents the truncated portion of the table name. |
The terms for the index statistics (formulas 4-6) mean:
Table reorganization is suggested when the results of the calculations exceed the bounds set by the formula.
Usage Notes
This command will not display declared temporary table statistical information.
This utility does not support the use of nicknames.
REORGCHK calculates statistics obtained from six different formulas to determine if performance has deteriorated or can be improved by reorganizing a table.
Attention: These statistics should not be used to determine if empty tables (TSIZE=0) need reorganization. If TSIZE=0 and FPAGE>0, the table needs to be reorganized. If TSIZE=0 and FPAGE=0, no reorganization is necessary.
REORGCHK uses the following formulas to analyze the physical location of rows and the size of the table:
100*OVERFLOW/CARD < 5
The total number of overflow rows in the table should be less than 5 percent of the total number of rows. Overflow rows can be created when rows are updated and the new rows contain more bytes than the old ones (VARCHAR fields), or when columns are added to existing tables.
100*TSIZE / ((FPAGES-1) * (TABLEPAGESIZE-76)) > 70
The table size in bytes (TSIZE) should be more than 70 percent of the total space allocated for the table. (There should be less than 30% free space.) The total space allocated for the table depends upon the page size of the table space in which the table resides (minus an overhead of 76 bytes). Because the last page allocated is not usually filled, 1 is subtracted from FPAGES.
100*NPAGES/FPAGES > 80
The number of pages that contain no rows at all should be less than 20 percent of the total number of pages. (Pages can become empty after rows are deleted.)
REORGCHK uses the following formulas to analyze the relationship of the indexes to the table data:
CLUSTERRATIO or normalized CLUSTERFACTOR > 80
The clustering ratio of an index should be greater than 80 percent. When multiple indexes are defined on one table, some of these indexes have a low cluster ratio. (The index sequence is not the same as the table sequence.) This cannot be avoided. Be sure to specify the most important index when reorganizing the table. The cluster ratio is usually not optimal for indexes that contain many duplicate keys and many entries.
100*(KEYS*(ISIZE+8)+(CARD-KEYS)*4) / (NLEAF*INDEXPAGESIZE) > 50
Less than 50 percent of the space reserved for index entries should be empty (only checked when NLEAF>1).
(100-PCTFREE)*(INDEXPAGESIZE-96)/(ISIZE+12)**(NLEVELS-2))*(INDEXPAGESIZE-96)/ (KEYS*(ISIZE+8)+(CARD-KEYS)*4) < 100
The actual number of index entries should be more than 90% (or 100-PCTFREE) of the number of entries an NLEVELS-1 index tree can handle (only checked if NLEVELS>1).
| Note: | Running statistics on many tables can take time, especially if the tables are large. |
See Also