IBM Security Directory Server, Version 6.3.1

Running reorgchk on a DB2 database

You can retrieve database statistics and use the values to determine whether tables, indexes, or both must be reorganized.

About this task

The DB2® database can become suboptimal and the server performance can degrade, when many updates are made to database. You must run the DB2 reorgchk to update the database statistics.

Procedure

Log in with the directory server instance owner credentials.
Connect to the database.
```
db2 connect to ldapdb2
```
where, ldapdb2 is the database name.

Run the reorgchk command.

db2 reorgchk update statistics on table all

To generate an output file with the database statistics, run the reorgchk command and redirect the output to a file. If you plan to run the reorg command, you must save the database statistics that are generated from the reorgchk command.

db2 reorgchk update statistics on table all > reorgchk.out

A sample database statistics from the reorgchk command.

db2 => reorgchk current statistics on table all

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

--------------------------------------------------------------------------------

LDAPDB2   ACLPERM              2       0    1    1     138    0   -   100   ---

LDAPDB2   ACLPROP              2       0    1    1      40    0   -   100   ---

LDAPDB2   ALIASEDOBJECT        -       -    -    -       -    -   -     -   ---

LDAPDB2   AUDIT                1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITADD             1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITBIND            1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITDELETE          1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITEXTOPEVENT      1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITFAILEDOPONLY    1       0    1    1      18    0   -   100   ---

LDAPDB2   AUDITLOG             1       0    1    1      77    0   -   100   ---

...

SYSIBM    SYSINDEXCOLUSE     480       0    6    6   22560    0 100   100   ---

SYSIBM    SYSINDEXES         216     114   14   28  162216   52 100    50   *-*

...

SYSIBM    SYSPLAN             79       0    6    6   41554    0 100   100   ---

SYSIBM    SYSPLANAUTH        157       0    3    3    9106    0 100   100   ---

SYSIBM    SYSPLANDEP          35       0    1    2    5985    0 100    50   --*


--------------------------------------------------------------------------------


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: LDAPDB2.ACLPERM
LDAPDB2 ACLPERM_INDEX             2     1     1      6     2  100  -   -  ---
Table: LDAPDB2.ACLPROP
LDAPDB2 ACLPROP_INDEX             2     1     1      6     2  100  -   -  ---
Table: LDAPDB2.ALIASEDOBJECT
LDAPDB2 ALIASEDOBJECT             -     -     -      -     -    -  -   -  ---
LDAPDB2 ALIASEDOBJECTI            -     -     -      -     -    -  -   -  ---
LDAPDB2 RALIASEDOBJECT            -     -     -      -     -    -  -   -  ---
Table: LDAPDB2.AUDIT
LDAPDB2 AUDITI                    1     1     1      4     1  100  -   -  ---
Table: LDAPDB2.AUDITADD
LDAPDB2 AUDITADDI                 1     1     1      4     1  100  -   -  ---
Table: LDAPDB2.AUDITBIND
LDAPDB2 AUDITBINDI                1     1     1      4     1  100  -   -  ---
Table: LDAPDB2.AUDITDELETE
LDAPDB2 AUDITDELETEI              1     1     1      4     1  100  -   -  ---
Table: LDAPDB2.AUDITEXTOPEVENT
...
Table: LDAPDB2.SN
LDAPDB2  RSN                  25012   148     2     14 25012   99 90   0  ---
LDAPDB2  SN                   25012   200     3     12 25012   99 61 119  --*
LDAPDB2  SNI                  25012    84     2      4 25012   99 87   1  ---
...
Table: LDAPDB2.TITLE
LDAPDB2  TITLEI                   -     -     -      -     -    -  -   -  ---
Table: LDAPDB2.UID
LDAPDB2  RUID                 25013   243     3     17 25013    0 62  79  *--
LDAPDB2  UID                  25013   273     3     17 25013  100 55  79  ---
LDAPDB2  UIDI                 25013    84     2      4 25012  100 87   1  ---
Table: LDAPDB2.UNIQUEMEMBER
LDAPDB2  RUNIQUEMEMBER        10015   224     3     47 10015    1 60  44  *--
LDAPDB2  UNIQUEMEMBER         10015   284     3     47 10015  100 47  44  -*-
LDAPDB2  UNIQUEMEMBERI        10015    14     2      4     7  100 69   8  ---

...
Table: SYSIBM.SYSFUNCTIONS
SYSIBM   IBM127                 141     1     1     13   141   65  -   -  *--
SYSIBM   IBM25                  141     2     2     34   141  100 72  60  ---
SYSIBM   IBM26                  141     2     2     32   141   78 68  63  *--
SYSIBM   IBM27                  141     1     1     23    68   80  -   -  *--
SYSIBM   IBM28                  141     1     1     12     2   99  -   -  ---
SYSIBM   IBM29                  141     1     1      4   141  100  -   -  ---
SYSIBM   IBM30                  141     3     2     59   141   78 76  38  *--
SYSIBM   IBM55                  141     2     2     34   141   99 72  60  ---
...
--------------------------------------------------------------------------------


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.

Feedback