WCS V5.1 Performance Tuning

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.