DB2 for LUWでバルクインサート処理のために提供されているIMPORTコマンドは、内部的には1レコードずつINSERTを実行してデータの投入を行ってます。そのためパフォーマンスがあまりよろしくなく、大量データのIMPORTに長い時間がかかってしまうことがあります。
一般的なチューニング方法
IMPORTでパフォーマンス問題発生した場合に以下のような対策がとられることがありますが、いずれもそれなりのデメリットや制約が伴います。
- IMPORTの代わりにLOADを使用する
- LOAD後にバックアップの取得やSET INTEGRITYの実行が必要
- IMPORTと比較し並行性が低下する
- LOAD権限が必要
- IMPORTの代わりにINGESTを使用する
- DB2 for LUW 10.1以上でのみ使用可能
- LOB列を扱えない
- IMPORTのようにID列の制御が出来ない
- 表や索引の物理設計を見直す
- 別のSQLの実行計画が悪化する可能性がある
- オブジェクトを削除して再作成しなければならない場合がある
compoundモディファイアによるチューニング
IMPORTのMODIFIED BYオプションには入力データに関する追加情報などを与えるためのモディファイアが指定されますが、ここにcompound=nを指定するとIMPORTはnレコード単位でまとめてINSERTを行うようになり、パフォーマンスを向上させることが出来ます。1nには最大で100までの値を指定可能です。
import from data.csv of del modified by compound=100 insert into table ;
検証してみた
100万レコードのIMPORTに要する時間をcompoundオプションのありなしで比較してみました。その結果、6分近くかかっていたIMPORTがcompound=100を指定すると1分未満で終了し、予想以上の効果を確認することができました。
- compound指定なしでの実測結果（約5分40秒）
PS C:\Users\svc34\db2> db2 -tvf import1.sql
truncate table tab01 immediate
DB20000I SQL コマンドが正常に完了しました。
values(current_timestamp)
1
--------------------------
2017-01-12-02.04.51.788000
1 レコードが選択されました。
import from dummydata.csv of del commitcount 100000 insert into test01
SQL3109N ユーティリティーが、ファイル "dummydata.csv"
からデータのロードを開始しています。
SQL3221W ...COMMIT WORK が開始されました。 入力レコード・カウント = "100000"
SQL3222W ...すべてのデータベース変更の COMMIT が成功しました。
--- 省略 ---
SQL3110N ユーティリティーが処理を完了しました。 "1000000"
行が、入力ファイルから読み取られました。
SQL3221W ...COMMIT WORK が開始されました。 入力レコード・カウント = "1000000"
SQL3222W ...すべてのデータベース変更の COMMIT が成功しました。
SQL3149N "1000000" 行が、入力ファイルから処理されました。 "1000000"
行が、正常に表に挿入されました。 "0" 行が、拒否されました。
読み込まれた行数 = 1000000
スキップされた行数 = 0
挿入された行数 = 1000000
更新された行数 = 0
拒否された行数 = 0
コミットされた行数 = 1000000
values(current_timestamp)
1
--------------------------
2017-01-12-02.10.31.942000
1 レコードが選択されました。
- compound=100での実測結果（約40秒）
PS C:\Users\svc34\db2> db2 -tvf import2.sql
truncate table tab01 immediate
DB20000I SQL コマンドが正常に完了しました。
values(current_timestamp)
1
--------------------------
2017-01-12-02.23.19.262000
1 レコードが選択されました。
import from dummydata.csv of del modified by compound=100 commitcount 100000 insert into test01
SQL3109N ユーティリティーが、ファイル "dummydata.csv"
からデータのロードを開始しています。
SQL3221W ...COMMIT WORK が開始されました。 入力レコード・カウント = "100000"
SQL3222W ...すべてのデータベース変更の COMMIT が成功しました。
--- 省略 ---
SQL3110N ユーティリティーが処理を完了しました。 "1000000"
行が、入力ファイルから読み取られました。
SQL3221W ...COMMIT WORK が開始されました。 入力レコード・カウント = "1000000"
SQL3222W ...すべてのデータベース変更の COMMIT が成功しました。
SQL3149N "1000000" 行が、入力ファイルから処理されました。 "1000000"
行が、正常に表に挿入されました。 "0" 行が、拒否されました。
読み込まれた行数 = 1000000
スキップされた行数 = 0
挿入された行数 = 1000000
更新された行数 = 0
拒否された行数 = 0
コミットされた行数 = 1000000
values(current_timestamp)
1
--------------------------
2017-01-12-02.23.59.681000
1 レコードが選択されました。
オプションの追加だけでパフォーマンスを大きく改善できるため、これは非常に有用なチューニング方法と思われます。
注意事項
以下の制約が存在します。
- INSERT_UPDATEモードと併用できない
- ID列に関するモディファイアと併用できない
- usedefaults
- identitymissing
- identityignore
- generatedmissing
- generatedignore
- 階層テーブルを対象にできない
また、アクティブログが極端に小さくかつレコードが極端に長い場合は、まとめて実行されたINSERTによりアクティブログが一杯になる可能性があります。2
参考資料
- IBM Knowledge Center
- DB2 for Linux UNIX and Windows 11.1.0