DB2 provides 3 command tools, reorganizations, and analysis of Table: ReorgchkReorgrunStat collects statistics for objects in (1), where these statistics can be used to diagnose objects of physical storage. See the specific content: http://blog.9cbs.net/dlinger/archive/2004/10/12/133178.aspx
2.Reorgchk, physical storage of diagnostic objects
>> - REORGCHK ---- --------------------------- ------------------------- << | | | | | |.-User ------- .-- | '- - CURRENT - --- Statistics ---' '-on Table - - System ----- -' All ------ '-Table- Name- '
ON SCHEMA options are added to the UDB V8 version.
Update Statistics: Insert RunStats first analyze Table, then diagnose if you need to reorganize TableCurrent Statistics: Use an existing statistics to diagnose whether you need to restructuring Table
On Table User: All Tableon Table System: Systems of User: All Tableon Table Table-name of the current DB: Specify names
We analyze sysibm.system.com, see the specific execution results:
DB2 => Reorgchk Update Statistics on Table System.SYSTABLES
Execute RunStats ....
Table statistics:
F1: 100 * Overflow / Card <5f2: 100 * (Effective Space Utilization of Data Pages)> 70F3: 100 * (Required Pages / Total Pages)> 80
Schema Name Card OV NP FP ACTBLK TSIZE F1 F2 F3 REORG ------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- Sysibm Systables 2988 0 290 290 - 4774824 0 100 100 --------------------------------------- -------------------------------------------------- - Index statistics:
F4: Clusterratio or normal ClusterFactor> 80F5: 100 * (Keys * (isize 9) ((NLEAF - NUM EMPTY LEAFS) * Indexpagesize> 50F6: (100 - PctFree) * (IndexPageSize - 96) / (ISIZE 12)) ** (NLEVELS - 2) * (Indexpagesize - 96) / (Keys * (isize 9) * 5) <100F7: 100 * (Numrids deleded / (Numrids deleted card) <20F8: 100 * (Num Empty Leafs / NLEAF) <20
Schema Name Card Leaf Eleaf Lvls Isize NDEL Keys F4 F5 F6 F7 F8 REORG ----------------------------------- -------------------------------------------------- ------------ Table: sysibm.systablessysibm IBM00 2988 55 0 2 27 42 2988 95 47 3 1 0 - * --- Sysibm IBM137 2988 18 0 2 4 52 2845 98 51 9 1 0 - ---- Sysibm IBM21 2988 5 0 2 3 26 3 99 73 24 0 0 ----- SYSIBM IBM22 2988 5 0 2 3 22 1 100 72 24 0 0 ----- Sysibm IBM23 2988 5 0 2 3 2 1 100 7224 0 0 ----- Sysibm IBM78 2988 62 0 2 33 17 2988 95 49 2 0 0 - * ----------------------- -------------------------------------------------- --------------------------- Clusterratio or normal clusterf Actor (f4) will indicate an index requires REORG, which is not in the same sequence as the base form. When multiple indexes are defined in the table, one or more indexs may be marked as needed as needed. Specifies the most important index of the REORG order.
The name of the table using the Organize By clause and the corresponding dimensional index definition has a '*' suffix. The base of the base of the dimension is equivalent to the "active block" statistics.
First explain the above information:
CARD: The number of data lines in the base table: Number of migration rows NP (NPAGES): Number of FP (FPAGES) containing data: The table of the table Tsize: Table's actual data size, Bytes are units. TablePagesize: Table Page Sizef1: Handling Overflow Row. When the overflow exceeds 5% of the total number of times, the report recommends reorganizing Table. F2: Treatment of spatial usage. When the TSIZE is less than or equal to 70% of the total space equal to the table, the report recommends the TABLE. F3: Treat a blank page. The so-called blank page is no page with no data. When the blank page is more than 20% of the blank page, the report recommends reorganizing Table.
LEAF: The number of INDEXs LVLS (Levels): Index Series ISize: INDEX average leader. Keys: The only value of the value of indexpagesize: INDEX Page SizePctFree: Index Page Reserved Space (%) F4: Cluster Factor F5: Space Reserved for Index Key. This value should be less than 50%. Should be less than 20% of the total, otherwise the report recommends recombinant F8: the number of pseudo-empty pages. It should be less than 20% of the total, otherwise the report recommends recombination of Index We have "*" in the value of REORG in the report to determine whether Table or Index is reorganized. Of course, the valve valve of F1-F8 is only a suggenerative value.
Part III, we will introduce how to reorganize the data in DB2.
