Db2のとても基本的な機能・仕組みについて自分自身の備忘録としてちょこちょこ残していこうと思います

今回DBの運用する上で、REORGはデータの詰め直しをしてくれるから大事！ということは知っていても、なんで必要なの？どんな効果があるのか？ということを改めて再度整理してみました。

なんでREORGは必要なの？

DB上のデータは、最初にデータを入れた時は綺麗に隙間がない状態でデータが入っています。
そんな最初は綺麗に入っているデータも更新、削除が繰り返されていくと、ページの中でも使われていない箇所が出てきて、本来必要とするディスク以上のスペースが使われてしまったり、追加・更新によって順番が揃っていない状態でデータが格納されている状態になります。
こ状態のままにしておくと表スペースの使用率が上がる、アクセスパフォーマンスの劣化が起こる可能性があります。
それをデータの詰め替え、空きスペースの解放、データの並び替えを行うのがREORGの役割なのです！

REORGをするとどんな効果があるのか？

具体的にREORGの効果を考えていきます。大きく３つの効果があると言われます。
1:フラグメンテーションの解消
2:オーバーフロー・レコードの解消
3:索引のキー順でデータを配置

1:フラグメンテーションの解消

データが削除されると、そのデータが格納されているページはどんどん使われていないスペースが所々に増えていきます。この状態をフラグメンテーションと言います。１つのページに所々に虫食いがあるイメージです。REORGではそのスペースを解放して、データがページの最初から隙間なく入るようにデータの詰め直しをします。ディスクの使用率も下げることが期待できます。
その際に所々空いていたスペースはまとめられて、その空きスペースは新たしいデータの格納スペースとして使えるようになります。

2:オーバーフロー・レコードの解消

可変長列が現在値よりも長い値で更新された場合、もともとデータが入っていたスペースには更新値は格納することができません。この場合、別の格納できるスペースに新たな値を格納します。そして元のデータ格納されていた場所にはtumbstoneという新たな格納位置を示す情報が格納されます。

イメージとしては郵便屋さんが昔の住所に手紙を届けにきた時に備えて、新しい住所の情報を前の住所に張り紙しておくイメージですね。現実世界では恐ろしくてそんなことはできませんけど

この状態ではデータにアクセスをするために、元のデータ格納位置→新しいデータ格納位置の順番に２段階のアクセスをする必要があるため、アクセスパフォーマンスの観点では良い状態ではありませんこのようにデータが格納されている行をオーバーフローレコードと言います。そのためREORGによってこのオーバーフローレコードを解消することができます。最初から新しい住所に郵便屋さんが向かえるように住所変更届を出すイメージしょうか。

3:索引のキー順でデータを配置

REORGによって、特定のキーの値の順番でデータを並び替えることができます。
もちろんSQLで検索キーとして指定される列に索引が作成されていれば、索引を経由して基本的にはアクセスが行われますので、データがその順番に並んでないからといって、著しくパフォーマンスが悪くなるとは一概には言えません。
ただ、特にデータの範囲を指定してデータの検索を行う場合（SQLのBETWEEN句など）はデータ自体も索引の順番で並べてある方が、データをまとめて取得することができるので検索のパフォーマンス向上が望めます。

この３つがREORGの主な効果になります。
REORGではディスク使用率・表へのアクセスパフォーマンスの観点で効果があるということがお分りいただけたのではないでしょうか？

REORGの種類

Db2のREORGにはクラッシックREORGとインプレイスREORGの２種類のREORGがあります。
オフラインREORGとオンラインREORGとも言われます。
ここからは２つのREORGの違いについて整理してみます。

1:クラッシックREORG(オフライン REORG)

シャドウコピーが表スペースまたは一時表に元表と同じ表のスペースに作成され、
REORG完了後はそのシャドウコピーと元表が置きかわります。
個人的には高速道路の一部区間を通行禁止にして工事を行うイメージです。車が高速道路に入ってくるのを禁止するのと同じようにクラッシックREORGではアプリケーションからのアクセスを禁止します。

＜クラッシックREORGのメリット＞

・インプレイスREORGより高速に再編成することが可能
・ログ・レコードはほとんど生成されず、消費されるログ・スペースは小さい
・REORG対象表に定義されている索引の再作成も行われるため、索引の再作成が不要

＜クラッシックREORGのデメリット＞

・REORGの実行中の表は更新が不可能、一部のフェーズでのみ読み取りアクセスのみ可能
　参照可能なタイミングは限られるため表へのアクセスは停止することが一般的
・シャドー・コピーのためのストレージ・スペースが必要(データ量の2~3倍確保する必要がある）

2:インプレイスREORG(オンラインREORG)

シャドーコピーを作成せず、空きスペースを利用してREORGを行います。
これは先ほどの高速道路の例えでいうと、区間を通行禁止にするのではなく一部の車線だけを封鎖して工事しているイメージです。車は通れるけど、車線が制限されている分車線が混んでしまうのと同じように、アプリからのアクセスも可能であるが、少なからずパフォーマンスに影響が起こり得ます。

＜インプレイスREORGのメリット＞

・再編成中の表へのアクセスが可能（だだしアクセス不可になるタイミングが一部ある）
・再編成処理の一時停止(PAUSE)、再開(RESUME)、停止(STOP)が可能
・再編成の為の一時作業領域が削減される

＜インプレイスREORGのデメリット＞

・クラシックREORGよりパフォーマンスが低い(実行時間が長い)
・クラシックREORGよりログ・スペースが大きくなる。
　（再編成される行の数、索引の数、索引キーのサイズ、表の最初の編成状態により変動）
・REORG対象表に定義されている索引の再作成もは行われないため、索引の再作成が必要

REORGが必要な状態なのか確認する方法

REORGCHKコマンド

Db2ではその表がREORGが必要な表であるか確認するREORGCHKコマンドが用意されています。
シンタックス例は以下の通り
以下の例ではCURRENT STATISTICSオプションを使用した例になります。
CURRENT STATISTICSオプションでは現在カタログ表に格納されている統計情報をもとにチェックを行います。
UPDATE STATISTICSオプションでは、REORGCHKコマンド実行時にRUNSTATSが実行され最新の統計情報を取得します。その情報をもとにチェックを行います。

REORGCHK CURRENT STATISTICS ON TABLE テーブル名

REORGCHK CURRENT UPDATE ON TABLE テーブル名

REORGCHKコマンドの出力結果の見方

表とその表に定義されている索引についてのチェック結果が出力されます。
REORGが必要であると判定されると、出力結果のREORG列（一番右の列）に*(アスタリスク)が表示されます。
今回の例は----と出力されているのでREORGは不要ということになります。
REORG列に*が多く表示されるほど、REORGの実行が推奨されます。

==> db2 reorgchk current statistics on table TEST1

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: DB2INST1.TEST1
                                5000      0    152    152      -   595000   0 100 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  PCT_PAGES_SAVED  F4  F5  F6  F7  F8 REORG
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.TEST1
Index: SYSIBM.SQL180828143445580
                               5000    21     0    2     0    5000            4             4                710                 710                0 100 103   -   0   0 -----
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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.

その他参考リンク

パフォーマンス維持のための運用 (REORG と RUNSTATS) 編

Knowledge Center：オフライン表再編成のパフォーマンスの改善

Knowledge Center：表を再編成する方式の選択

Knowledge Center：オンライン表再編成のロックおよび並行性に関する考慮事項

REORGCHKコマンド