Top / Installmemo / DB2 / reorg

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS

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: 2334, today: 1, yesterday: 0