DB2勉強する

統計情報とは偏り具合などのデータの状態を表す情報のこと。DB2にデータの統計情報を取得させることで実行速度の高いアクセスプランが作られる可能性が高くなる。 統計情報が古い場合に、ソート・オーバーフローが多発し、SQL全体が急激に遅くなる場合があるので気を付けよう。

db2 RUNSTATS ON TABLE ${テーブル名} ON ALL COLUMNS

自動保守になっているか確認

最近のDB2では自動保守設定で2時間に1度、RUNSTATSを実行してくれているので、基本的には手動で実施する必要はない。大量データを操作する場合に手動実行を検討しよう。パラメータ「auto_maint」と「auto_runstats」を確認しておこう。

コマンドの場合

db2 GET DB CFG FOR ${DB名}

SQLの場合

SELECT * FROM SYSIBMADM.DBCFG

分散統計つき(WITH DISTRIBUTION)

ソート・オーバーフローを防ぐために、できるだけ｢WITH DISTRIBUTION｣をつけたほうがよい。

db2 RUNSTATS ON TABLE ${テーブル名} ON ALL COLUMNS WITH DISTRIBUTION

インデックスの統計情報も取得しよう。

db2 RUNSTATS ON TABLE ${テーブル名} WITH DISTRIBUTION AND INDEXES ALL

LIKE検索の対策

｢中間一致｣｢後方一致｣のLIKE検索専用のRUNSTATSがある。LIKE検索が多いカラムに対して実行する。

db2 RUNSTATS ON TABLE ${テーブル名} ON COLUMNS (${カラム名} LIKE STATISTICS)

RUNSTATSに時間がかかる場合

表全体の一部だけをサンプリングして統計情報を取得する方法もある。ただし、精度が低くなる。

rem ページ単位で大まかにサンプリングを行う。(10%)
db2 RUNSTATS ON TABLE ON ALL COLUMNS TABLESAMPLE SYSTEM(10)
rem 行単位で細かくサンプリングを行う。(10%)
db2 RUNSTATS ON TABLE ON ALL COLUMNS TABLESAMPLE BERNOULLI(10)

RUNSTATSの自動保守

ちなみにDB構成パラメータで「auto_runstats」という機能があり、2 時間間隔で自動で非同期統計収集をやってくれている。(RUNSTATSはREORG後ぐらいしかやったことないかも。)

データが増えてくるとデータがフラグメンテーション化する。するとテーブルスキャン時にソート・オーバーフローを発生させやすくなり、DBのSQL全体が遅くなってしまう。フラグメンテーション化を防ぐために定期的にREORG、RUNSTATを実施したほうがよい。

REORGが必要か否かをチェック

"reorgchk"でREORG実行が必要か否かをチェックできる。

db2 reorgchk current statistics on table ${テーブル名}

全テーブルを見たい時は以下のコマンド

db2 reorgchk current statistics on table all

評価はF1～F8の項目で表示される。F1～F3は表をチェック。F4～F8はインデックスをチェックしている。 アスタリスク(*)が表示された場合、REORG実施が推奨されている。

各項目の説明は、DB2のREORGCHKの公式ページを参照ください。

ちなみにREORGCHK時に"UPDATE STATISTICS"を指定すると、統計情報を更新してからチェックする。 "CURRENT STATISTICS"の場合は、統計ｊ情報を更新しない。

REORGCHKをテーブル化して表示したい場合

REORGCHKをテーブルにして出力することもできる。バッチでレポートにする際に便利な機能です。

call SYSPROC.REORGCHK_TB_STATS('S','${スキーマ名}')
call SYSPROC.REORGCHK_IX_STATS('S','${スキーマ名}')

上記を実行すると以下のSQLで表示できる。

select * from SESSION.TB_STATS;
select * from SESSION.IX_STATS;

REORGを実行

実行コマンドは簡単

db2 reorg table ${テーブル名}

並べ替え基準をインデックスにしたい場合。

db2 reorg table ${テーブル名} index ${インデックス名}

インデックスを再編成したい場合

db2 reorg indexes all for table ${テーブル名} 

インプレースとシャドーコピー

REORGは通常「シャドーコピー」方式となっており、一時領域を設けて、そこにデータをコピーしながら再編成を行う。

"INPLACE"を指定すると、一時領域を使わず、自身の領域を使って並べ替える。オンライン中でも実施できるが、時間がかり、インデックスの再編成はされない。また、トランザクション中はログが大量に出力される。

db2 reorg table ${テーブル名} inplace

REORGの状況を確認

どこまでREORGが進んでいるのか状況が気になる場合は以下のSQLを実行する。 REORG_MAX_COUNTERとREORG_CURRENT_COUNTERで進捗状況がわかる。

select
  REORG_CURRENT_COUNTER
  , REORG_MAX_COUNTER
  , REORG_STATUS
  , REORG_COMPLETION 
from
  SYSIBMADM.SNAPTAB_REORG

REORGのペンディングを確認

REORGを中断させてしまうことによって 対象テーブルがペンディング状態となり、SQLを受け付けなくなることがある。まずはペンディング状態になっていないか確認しよう。

select * from SYSIBMADM.ADMINTABINFO WHERE REORG_PENDING = 'Y'

ペンディング状態のテーブルはオフライン形式のREORGを実行することで回復できる。

オフラインREORGならハードディスクを余分に

オフラインREORGは一時領域を設けてREORGを実施するため、ディスク容量が足らないとエラーが発生してしまう。(SQL0289N,SQL0968C)。別ディスク作ったコンテナを保持する一時表領域を指定して対応する必要がある。

db2 reorg table ${テーブル名} use ${表領域名}

REORGして空きディスク容量を増やしたい場合

REORGしただけだとフリーページが増えるだけで、空き容量は増えない。 解放させたければ別途コマンドを実行する必要がある。以下は「再利用可能ストレージが有効な DMS 自動ストレージ表スペースの場合 」

ALTER TABLESPACE ${表領域名} REDUCE MAX

詳細は、公式の「表スペース・コンテナーに確保された領域を解放する方法」を参考にしよう。

リモートからCLPで接続したい場合、カタログ登録を行う必要がある。もちろんODBCやJDBC接続には必要ない。

ノード、データベース一覧

まずは登録済みのノードや、データベースを確認しよう。

db2 LIST NODE DIRECTORY SHOW DETAIL

db2 LIST DATABASE DIRECTORY SHOW DETAIL

ノード登録

ホスト名やIPアドレスを指定する。ノード名を決めよう。ノード名はわかりやすい名前にしたほうがよい。

db2 CATALOG TCPIP NODE ${ノード名} REMOTE ${ホスト名} SERVER ${ポート番号}

データベース名登録

続いて接続先のデータベースに別名を登録する。ノード名と同様にわかりやすい名前にする。

db2 CATALOG DATABASE ${DB名} AS ${別名} AT NODE ${ノード名}

リモートのDB2クライアントから、CLPで接続できるか確認

db2 CONNECT TO ${別名} user ${ユーザ名} USING '${パスワード}'

ノード、データベースを再登録したい場合

再登録する際は、一度UNCATALOGで削除したほうがよい。キャッシュが残ってうまくいかない。

db2 UNCATALOG NODE ${ノード名}

db2 UNCATALOG DATABASE ${別名}

トランザクションログは、データの変更履歴と考えてよい。 使い道を理解しておこう。

トランザクション・ログの用途

ロールバックさせるため。

同じみ。

バックアップからロールフォワードさせて、特定時間の状態に戻すため。

トランザクションログを残しておく(｢アーカイブ化｣と呼ぶ)ことで、バックアップデータにトランザクション・ログを適用し、特定の時間まで戻すことができる。

※｢アーカイブロギング｣に設定しておかないといけない。デフォルトは循環ロギング。

ログの設定

"GET DB CFG"を実施してパラメータを確認しておこう。

db2 GET DB CFG FOR ${DB名}

NEWLOGPATH

トランザクションログを保管するディレクトリの変更先を指定。表領域とは別に用意されたRAID構成の書き込み性能の高いデバイスに格納したほうがよい。

LOGFILESIZE

トランザクションログの１つあたりのファイルサイズ。単位は4KBなので、デフォルトの｢1024｣の場合は、4MBとなる。

LOGPRIMARY

トランザクション・ログの個数。1次ログと呼ばれる。デフォルトは｢8｣なので、初期の最大トランザクションログのサイズは、4MB×8=32MB。

LOGSECOND

2次ログと呼ばれる。 LOGPRIMARYが不足した場合、拡張で作成されるトランザクション・ファイル数の最大値を設定。デフォルトは｢4｣なので16MB。LOGSECONDはオンライン中に変更可。LOGFILESIZE、LOGPRIMARYはDB2の再活動化が必要。

LOGARCHMETH1

アーカイブログを保存するディレクトリ。アーカイブログとは、トランザクションログをアーカイブ化したもの。 設定することで、トランザクションログ管理が｢循環ロギング｣から｢アーカイブロギング｣に切り替わり、ポイントインタイムリストアが可能になる。

db2 UPDATE DB CFG ${データベース名} USING NEWLOGPATH E:\dblog
db2 UPDATE DB CFG ${データベース名} USING LOGARCHMETH1 DISK:F:\dbArchivelog

トランザクション・ログの使用率を参照してLOGFILESIZE値を決める

モニター表関数のMON_GET_TRANSACTION_LOGを見て確認する方法がある。 クエリー発行時点の値なので、定期的に実行する必要がある。

select
  MEMBER
  , case 
    when TOTAL_LOG_AVAILABLE < 100 
      then null 
    else cast( 
      100 * TOTAL_LOG_USED / TOTAL_LOG_AVAILABLE as decimal (4, 1)
    ) 
    end as LOG_USED_PERCENT 
from
  table (MON_GET_TRANSACTION_LOG (- 1)) as t 
order by
  member asc

「SQL0964C データベースのトランザクション・ログがいっぱいです。 」の対策

保守作業で大量のデータをDELETEしたり、INSERTする際にトランザクションログがフルになってしまい、エラーになってしまうことがある。

(対策1)トランザクションを一時的に止める。

トランザクションを一時的に止めて、DMLを実行する。commitされるまでトランザクションがOFFのままとなる。

※CLPから実行する場合、デフォルトでは、即時コミットのため"+c"をつけて実施しよう。

db2 +c alter table ${テーブル名} activate not logged initially
db2 +c insert into ・・・
db2 +c delete from  ・・・
db2 commit

またLOADで実施してもトランザクションが発生しない。ファイルからLOADするのが鉄板だが、別テーブルのデータをLOADすることも可能。

db2 DECLARE C1 CURSOR FOR SELECT * FROM ${テーブル名}
db2 LOAD FROM C1 OF CURSOR MESSAGES ${ログ名} INSERT INTO ${テーブル名}

※トランザクションログをアーカイブ化している場合、オンラインバックアップが無効になるため、注意が必要。バックアップペンディングになっているかもしれない。

ちなみに失敗したらテーブルをDROPするしかないので、事前にバックアップをとっておいたほうがよい。

(対策2)2次ログ数を増やす。

2次ログ数はオンライン中でも実施可能。

まずは事前にLOGSECONDの設定値を確認。

db2 GET DB CFG FOR ${DB名}

LOGSECONDを変更する。

db2 update db cfg for ${DB名} using LOGSECOND 32 IMMEDIATE 

DML実施後、元に戻しておこう。

2次ログ数を増やすほうがトランザクションを維持できるため安全な策と言える。

ただしログ数が足りず、時間のかかるDMLに再度失敗。そして時間のかかるロールバックとTRY&ERROR地獄になることもある。目安は「行長×行数」の2,3割り増しぐらいで。

(おまけ)DELETEで小分けして削除したい場合

一部のデータを削除したいが、件数が多くて「SQL0964C 」になってしまうことがある。FETCH FIRSTで削除件数を制限させながら削除することもできる。

delete
from
    (
        select
            *
        from
            ${テーブル名}
        WHERE
            ${検索条件} FETCH FIRST ${件数} ROWS ONLY