Chapter 4. Database tuning 67
We recommend that this command is used regularly on tables with a lot of
updates, as well as tables after large data loads, such as tables associated with
product data. When planning batch jobs to perform runstats, it is important to
research the most commonly updated tables for your WebSphere Commerce
Suite instance. From our testing, we produced Figure 4-3 on page 71, which
shows the most active tables in our test database.
4.6.2 reorg
reorg is used to reorganize tables by eliminating fragmented data, and
compacting data. You have the option of physically ordering the data in the table
by a named index, or simply compacting the data without any reordering. Reorg
requires a temporary space to store the data being reconstructed before it
replaces the existing table. The default is to use the same tablespace as the
table being reconstructed, or you can specify a specific tablespace for the
temporary storage. This is especially important for large tables, as the command
will fail if the temporary table fills the available space. Also, distributing the I/O
over two or more physical devices will accelerate the process.
To find out if your tables need reorganizing, use the reorgchk command.
reorgchk runs several tests against the database tables and their indexes.
Tables failing to meet any one of the test criteria are candidates for
reorganization. reorgchk produces a detailed report showing this information. To
run reorgchk on all the user and system tables:
db2 reorgchk current statistics on table all
Alternatively, you can specify the
update flag. This causes runstats to be run on
the database tables before reorgchk determines if tables need reorganizing or
not. To do this you must use this command:
db2 reorgchk update statistics on table all
The advantage of updating the statistics is that the database manager will have
the most up-to-date information for calculating if tables need reorganizing.
Example 4-5 on page 68 is an example of part of the output from reorgchk. In
this case, the results show that the CATENTDESC, CATGPENREL and
SUBORDERS tables should be reorganized. This is indicated by the asterisks in
the right hand column.
Note: Running statistics on many tables can take time, especially if the tables
are large.
68 WCS V5.1 Performance Tuning
Example 4-5 Sample output of reorgchk
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
--------------------------------------------------------------------------------
DB2RT ACCCUSTEXC - - - - - - - - ---
DB2RT ADDRBOOK 15061 0 185 185 737989 0 99 100 ---
DB2RT ADDRESS 25066 43 2441 2441 9424816 0 96 100 ---
DB2RT CATENTDESC 11126 4586 655 1072 4405896 41 100 61 *-*
DB2RT CATENTREL 53 0 2 2 5671 0 100 100 ---
DB2RT CATENTRY 11064 0 460 460 1803432 0 97 100 ---
DB2RT WTAXINFO - - - - - - - - ---
DB2RT ZIPCODE - - - - - - - - ---
--------------------------------------------------------------------------------
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: DB2RT.ACCCMDGRP
DB2RT P_ACCCMDGRP 881 3 2 4 881 100 86 34 ---
DB2RT UI_ACCCMDGRP 881 22 2 86 881 84 91 4 ---
Table: DB2RT.CATENTDESC
DB2RT P_CATENTRYDESC 11126 62 2 12 11126 99 87 1 ---
able: DB2RT.CATGPENREL
DB2RT P_CATGPENREL 11063 99 3 24 11063 5 87 112 *-*
Table: DB2RT.CATGROUP
DB2RT P_CATGROUP 1013 5 2 8 1013 100 79 22 ---
DB2RT UI_CATGROUP 1013 13 2 38 1013 98 87 7 ---
Table: DB2RT.SUBORDERS
DB2RT I_SUBORDERS1 5583 17 2 13 2205 48 85 6 *--
DB2RT I_SUBORDERS2 5583 26 2 8 5583 98 83 4 ---
DB2RT P_SUBORDERS 5583 25 2 8 5583 99 87 4 ---
--------------------------------------------------------------------------------
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.

Get WCS V5.1 Performance Tuning now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.