reorgはデータベース内の不要な領域除去やデータの再配置、インデックスの再作成によりパフォーマンスの向上を行う。
- 統計情報
- オプティマイザーがデータに到達するために最適なアクセスパスを判断する元となる情報
データベースの統計情報の更新および統計情報の確認 †
reorgが必要なテーブルは、reorgchkの結果欄、REORGに「*」が表示される。
db2 => reorgchk update statistics on table all db2 => reorgchk current statistics on table all Table statistics: F1: 100 * OVERFLOW / CARD < 5 F2: 100 * (Effective Space Utilization of Data Pages) > 70 F3: 100 * (Required Pages / Total Pages) > 80 SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG ---------------------------------------------------------------------------------------- Table: SCHEMA.TABLENAME SCHEMA TABLENAME 2.7e+07 0 4e+05 4e+05 - 2.42e+08 0 13 100 -*- 〜中略〜 Table: SYSIBM.SYSXMLSTATS SYSIBM SYSXMLSTATS - - - - - - - - - --- ---------------------------------------------------------------------------------------- Index statistics: F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80 F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE)) F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100 F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20 F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20 SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG ------------------------------------------------------------------------------------------------- Table: SCHEMA.TABLE SYSIBM SQL080811073550260 3e+07 2e+05 0 4 20 0 3e+07 56 87 5 0 0 *---- 〜中略〜 Table: SYSIBM.SYSXMLSTATS SYSIBM IBM184 - - - - - - - - - - - - ----- ------------------------------------------------------------------------------------------------- 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. Tables defined using the ORGANIZE BY clause and the corresponding dimension indexes have a '*' suffix to their names. The cardinality of a dimension index is equal to the Active blocks statistic of the table.
tableのreorg †
db2 => reorg table SCHEMA.TABLENAME use 利用するテンポラリテーブルスペース名 DB20000I The REORG command completed successfully.
INDEXの再生成 †
db2 => reorg indexes all for table SCHEMA.TABLENAME DB20000I The REORG command completed successfully.
統計情報の再収集 †
db2 => runstats on table SCHEMA.TABLENAME on key columns and indexes all DB20000I The RUNSTATS command completed successfully.
再バインドの実施 †
$ db2rbind DBNAME -l LOGFILE ALL Rebind done successfully for database 'DBNAME'.
Counter: 2324,
today: 1,
yesterday: 1