インフラエンジニアとしてDB2を管理しているのですが、突然開発チームから「100万行のテーブルは消せるのに、数千行のテーブルデータがエラーになって消せないの！助けて！」というこで調べた経緯とかをまとめます。
- 目次 -
事象と原因
どんな事象か
「SQL0964C、トランザクションログがいっぱいです」のエラーで削除ができない状態でした。
他には
・五千件程のテーブルのデータを一括DELETEしたらエラーになった。
・その前に別テーブルで数万件のデータは消せた。
・このテーブルは小さいのでログフルにはならないはず
とのことだったので、１回に消すデータを減らして都度commitを入れてもだめ。
１回の削除を100件くらい減らしてもエラーになる。
こんな感じで少ないデータの削除なのにログフルになったことがありました。
原因
結論を先にまとめるのですが、私のところで起きた問題の原因としては、参照制約の削除ルールが「CASCADE」だったため、想定以上の削除が実行されていたようです。
この削除ルールの「CASCADE」は、親テーブル「A」のレコードが削除されると、それに紐づく子テーブルのデータも削除する指定です。
この制約があるため、テーブル「A」の１レコードを削除しようとしても、テーブル「B」で削除するレコードを参照しているのが３レコードあったとしたら、実際は４レコードの削除になる。
ということになります。
さらにテーブル「B」をテーブル「C」が参照していたら。。。と芋づる式に削除するレコードが増えてしまいます。
これを確認するコマンドは以下のような感じです。
$ db2 "SELECT
CONSTNAME AS CONSTRAINT_NAME,
TABSCHEMA AS CHILD_SCHEMA,
TABNAME AS CHILD_TABLE,
CASE DELETERULE
WHEN 'C' THEN 'CASCADE'
WHEN 'N' THEN 'SET NULL'
WHEN 'R' THEN 'RESTRICT'
ELSE DELETERULE
END AS DELETE_RULE
FROM SYSCAT.REFERENCES
WHERE REFTABSCHEMA = '親テーブルのスキーマ名' AND REFTABNAME = '親テーブルのテーブル名' "
実行するとこんな感じの結果が出力されます。「DELETE_RULE」の結果を確認します。
| CONSTRAINT_NAME | CHILD_SCHEMA | CHILD_TABLE | DELETE_RULE |
| SQL231027123456789 | db2sampl | ORDER_DETAILS | CASCADE |
「CASCADE」 は上で書いた通り、親テーブルの値を削除すると、子テーブルの値も削除します。
他に
「SET NULL」は親テーブルのレコードを削除すると、参照するカラムはNULLが入ります。
「RESTRICT」は参照している小テーブルがあると、削除が失敗します。
対応
無難なのが参照している子テーブルから削除していく方法かと思います。
それぞれの環境によってベストな方法は変わるかと思います。
全削除するならloadコマンドを制約無視させるとかでも良いかもしれません。
状態確認とその他の要因
状態確認
実際には、結論に至るまで色々確認しましたので、観点と確認方法をまとめます。
まず、他のトランザクションが大量のログを使っていないかを確認しました。
DB2のアクティブログの状態確認
db2pdコマンドで現在のログの状態を確認
$ db2pd -db <dbname> -logs
結果出力の後半に、各ログファイルの状態が出力されます。
この中で「StartLSN」が「0（実際には000….）」以外のものを確認します。
全てのログが使用状態なら、この値が「０」以外になっているはずです。
トランザクションの状態確認
db2pdコマンドでトランザクションが残っていないか確認します
$ db2pd -db <dbname> -transactions
出力結果の「AppHandle」や「Firstlsn」を確認し、処理中のトランザクションや利用しているログを確認します。
他の要因
他のトランザクションに怪しいものがない場合で、参照制約以外で考えられるのがトリガー設定です。
DELETEが実行されたら、別のテーブルに履歴をINSERTする、といった自動処理が設定がないかを確認します。
まずはトリガーの存在を確認します。SYSCAT.TRIGGERSカタログビューを照会し、対象テーブルにDELETEイベントで発動するトリガーがないか調べました。
$ db2 "SELECT
TRIGSCHEMA, TRIGNAME, TABSCHEMA, TABNAME,
CASE TRIGTIME WHEN 'B' THEN 'BEFORE' WHEN 'A' THEN 'AFTER' ELSE TRIGTIME END AS TRIGGER_TIME,
CASE TRIGEVENT WHEN 'D' THEN 'DELETE' ELSE TRIGEVENT END AS TRIGGER_EVENT,
TEXT
FROM SYSCAT.TRIGGERS
WHERE TABSCHEMA = 'スキーマ名' AND TABNAME = 'テーブル名' AND TRIGEVENT = 'D'";
何かしらトリガーが設定されていれば、ここでトリガー情報が出力されます
コメント