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
Procedure
- Log in with the directory server instance owner credentials.
- Connect to the database.
where, ldapdb2 is the database name.db2 connect to ldapdb2 - 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.outA 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.