Db2 Warehouse on Cloud を利用するときのTips

Db2 Warehouse on Cloud

クラウドで使えるDWHとしてIBM Db2 Warehouse on Cloudがある。
フルマネージドなので難しいことは考えずすぐに利用可能なのが利点であるが、オンプレのDb2ユーザーの観点からするとsshでの接続ができないなどの制約があり、オンプレで実施していた様々な方法をそのままでは利用できないことがある。
未来の誰かのためにTipsというか覚書として残しておく。

バージョン情報の取得方法

通常のDb2であれば、db2cmdからdb2levelで情報を取得できる。
Db2WoCでは以下のSQLを発行して情報を取得する。

SELECT T.SERVICE_LEVEL, T.RELEASE_NUM, T.BLD_LEVEL FROM TABLE(ENV_GET_INST_INFO()) AS T
| DB2 v11.5.2.0 | 0603010F | special_39774 |

Db2 Warehoue on Cloudの接続方法とバージョン

Db2WoCに対しては主に以下の方法で操作が可能：

• Webコンソール
• クライアントソフトウェアからのJDBC/ODBC接続
• Db2クライアント
• Db2Wh Client Container

クライアントソフトウェアであるが、Db2は共通エンジンなのでDb2 11.1に対応しているクライアントであればすべて問題なく接続できる。ただし、機能拡張の関係上、Db2のドライバは11.1 fp4以上が推奨されている。もちろん最新のドライバのほうが良い。

アクセスプランのとり方

2年くらい前はアクセスプランを取ることができなかったが、現在ではオンプレと同様にアクセスプランを取ることができる。

1. ツールを使う方法
   GUIでDB操作が可能なDBeaverを利用する。ツールの画面から簡易的なアクセスプランを取得可能。
2. Db2をインストールしているマシンがローカルに存在する場合
   Db2クライアントであれば、db2explainを利用可能。ただし取得できる情報が少ないことに注意。
3. db2exfmt
   サーバーとしてインストールされていればdb2exfmtを利用することができる。詳細な情報を取れるので、可能であればこちらを利用する。

外部表を利用した高速なデータロード

Db2 Warehouse on Cloudを利用するお客様の場合、多くはS3やiCOSといったオブジェクトストレージにデータをCSVで保管して、必要に応じてデータを読み込む、といった使い方をすることが多い。
この場合は外部表を使ってロードすると高速にロードできる。

db2 "INSERT INTO ターゲットテーブル SELECT * FROM EXTERNAL 'loaddata/hoge.csv' USING (s3('endpoint url', 'authkey1','authkey2', 'endpoint') DELIMITER ',' )"

更に速度を出すためには、パーティションロードや並列実行といった手段を用いる。

LIFTを使ったデータのロード

LIFTは既存のオンプレミスデータベースから、Db2 Warehouse on CloudにデータをロードすることができるCLI。
ダウンロードはhttps://www.lift-cli.cloud.ibm.com/。
データ型の対応もLIFTが自動で行うため非常に簡単にデータの移行をすることができるが、仕組み上遅い。
具体的には以下の手順となる。

1. ソースとなるデータベースからCSVでクライアントにアンロード
2. Db2WoCのランディングゾーンにCSVをアップロード
3. ランディングゾーンのCSVをロード (Db2ロードコマンド)

各手順には対応するLIFTのコマンドを利用するため、操作自体は非常に簡単ではあるので、最初に一度だけデータ移動をする場合や、データ移動に関わる時間の余裕がある時に利用すると良い。

パイプを使ったデータロード

オンプレデータベースからデータを高速にデータの転送をしたいという要求がある。LIFTのように、通常はCSV経由でのデータ連携を行うが、CSV経由だとディスクI/Oがネックとなり時間がかかるのは避けられない。
これを解消するためパイプを使うことで高速にデータをロードできる。
以下はDb2のデータベースをソースの例として、2行目でソースとなるCSVを作成するために外部表での出力をしているが、異なるDBでも同様に出力先をパイプにすることで同様のことが可能。

mkfifo pdata

db2 "create external table 'pdata' using (delimiter ',' ) as select * from ソーステーブル" &

dbload -host ホスト名 -u ユーザー -pw パスワード -df pdata -db DB -t ターゲットテーブル

DB間でのデータ転送を行う場合はデータ量がネックになることが多いので、パイプにGZやLZ4といった圧縮を利用してネットワーク使用量を減らすことも併用する。

Db2ロードコマンドをする際にヘッダーを取り除く方法（パイプ）

Db2のロードコマンドを利用したロードは、統計情報の取得や文字数のカット、エンコード変換などができるので個人的には非常に好きなのだが、なぜかヘッダを含むCSVのロードに対応するオプションが無い(オプションを見逃していたらごめんDB2)。
ヘッダ行を無視すればよいだけなので、ロード実行後に1行エラーが出ていたら無視すればよいだけではあるのだが、ちょっと汚い。
そんなときに以下の方法でヘッダ行だけ無視する。
具体的には、1行目を抜いた結果をパイプに流し込むだけ。

mkfifo pdata

tail -n +2 $file > pdata &

db2 "LOAD FROM pdata OF DEL INSERT INTO ターゲットテーブル NONRECOVERABLE"