はじめに
Flywayはスキーマ変更などSQLで行うことを自動化するデータベース移行ツールです。大量のSQLを実行するには大変便利ですが、本番運用するには乗り越えなければいけない壁がいくつかあります。
内容
話すこと
- Flywayを本番運用するにあたり、発生した問題と解決策
話さないこと
- Flywayの概要
- Flywayのhello,world的な使い方
環境
- パッケージソフトウェアを開発する会社
- 契約するお客さまごとにデータベースサーバーが存在
- リリースのたびに複数あるデータベースのスキーマを変更
- IBM Db2 V11.1 Windows（他のDBMSでも参考になるはずです。）
- Flyway V7.7.2（V6.3.2から使っていて記載している内容的には変わりません。）
既知のエラー
Flywayはエラーが発生すると処理を中止します。 想定外のエラーで中止するのはうれしいですが問題のないエラーでいちいち中止しては仕事がはかどりません。既知のエラーには以下があります。
- オブジェクトの追加
- オブジェクトの削除
- 重複するレコードのinsert
オブジェクトの追加
スキーマ変更でテーブル、カラム、インデックスなどのオブジェクトを追加します。先行リリースですでにそれらが追加されているデータベースがあります。Flywayはすでに存在するオブジェクトを再度作成しようとするとエラーになり、処理を中止します。
IF NOT EXISTS
テーブルなどオブジェクト作成時にIF NOT EXISTSをオプションを追加するとテーブルが存在しないときのみに実行されるのでエラーになりません。
DECLARE CONTINUE HANDLER
Db2はバージョンによって、IF NOT EXISTSをサポートしていないので複数バージョンに対応するならDECLARE CONTINUE HANDLERを使った方が良いでしょう。
-- ALTER TABLE TPCLFT ADD COLUMN AUTO_REGIST_FLG CHAR(1) NOT NULL WITH DEFAULT '0' ;
begin DECLARE CONTINUE HANDLER FOR SQLSTATE '42711' BEGIN END; EXECUTE IMMEDIATE 'ALTER TABLE TPCLFT ADD COLUMN AUTO_REGIST_FLG CHAR(1) NOT NULL WITH DEFAULT ''0'''; end ;
定義文をDECLARE CONTINUE HANDLERで括ります。SQLSTATEが42711のときはエラーにせず、処理を続行します。42711は定義済みの際に返すSQLSTATEです。既知のエラーのSQLSTATEで指定することで他の処理にも応用できます。
オブジェクトの削除
オブジェクトの追加と逆の話になります。トリガー等のオブジェクトを再定義する時にいったん削除する場合です。Flywayは存在しないオブジェクトを削除しようとするとエラーになり、処理を中止します。
IF EXISTS
テーブルなどオブジェクト削除時にIF EXISTSをオプションを追加するとテーブルが存在するときのみに実行されるのでエラーになりません。
DECLARE CONTINUE HANDLER
Db2はバージョンによって、IF EXISTSをサポートしていないので複数バージョンに対応するならDECLARE CONTINUE HANDLERを使った方が良いでしょう。
-- DROP TRIGGER TRIG_TPANELITEM_INS;
begin DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END; EXECUTE IMMEDIATE 'DROP TRIGGER TRIG_TPANELITEM_INS'; end ;
削除文をDECLARE CONTINUE HANDLERで括ります。42704は存在しない際に返すSQLSTATEです。
重複するレコードのinsert
マスターデータをinsertする際、先行リリースですでにマスターデータが登録済みのデータベースがあります。Flywayは主キーが重複するレコードをinsertするとエラーになり、処理を中止します。
MERGE or NOT EXISTS
insertするSQL文をMERGEかNOT EXISTSを使うことにより、主キーが重複するレコードをinsertしないようすることができます。詳細は以下の記事をご覧ください。
SQL:レコードがない場合のみinsertしたい
データベースコマンド
FlywayはSQLを解釈して処理を行います。スキーマ変更ではSQLではないデータベースコマンドを実行したい場合があります。Flywayはデータベースコマンドを解釈せず、エラーになります。 Db2の例を以下に挙げます。
- 統計情報の更新(RUNSTATS)
- テーブル、インデックスの再編成(REORG)
- CSVファイル等のバルクインサート(IMPORT)
admin_cmd
Db2の場合、admin_cmdをコールします。
ALTER TABLE TEMRFLG ALTER COLUMN FLG_STAT SET DATA TYPE CHAR(2);
-- REORG TABLE TEMRFLG ;
call admin_cmd('REORG TABLE TEMRFLG') ;
カラム属性を変更（例は列幅を拡張）した後はテーブルの再編成が必要になります。REORGはSQLではないため、admin_cmdで括ったうえでコールします。
ファイル処理
バルクインサート(import)などファイル処理をする際、ファイルをどこに置くべきでしょうか。Db2ですとパスを指定しないとインスタンスのデフォルトフォルダー1 になり、使い勝手が悪くなります。かと言って、SQLファイルに絶対パスを記述するのもスマートではありません。
placeholders
設定ファイルのplaceholdersに絶対パスを記述することにより、SQLファイルはパスを変数で記述することができます。
flyway.encoding=Windows-31J
flyway.sqlMigrationSuffixes=.sql,.SQL,.ddl,.DDL
flyway.url=jdbc:db2://localhost:50000/mydb
flyway.user=db2admin
flyway.password=password
flyway.placeholders.ddl_path=F:\flyway-7.7.2\sql <-- 絶対パスを記述
この場合、ddl_pathが変数となり、SQLファイルに ${ddl_path} と記述することで設定ファイルの絶対パスを参照できます。
call admin_cmd('IMPORT FROM ${ddl_path}\20171130_V11.B\V11.2.00000000.06.20150129.01__TSYSPROF.DEL of DEL INSERT INTO TSYSPROF') ;
文字コード
データベースの文字コードがシフトJISなので設定ファイルにShift_JISを指定しているとバルクインサートするときにエラーになりました。CSVファイルに丸数字（①②③）があったのが原因でした。
Windows-31J
Flywayの開発言語がJavaであることから、その方面で調査したところ、文字コードにWindows-31Jを指定することで丸数字を処理することができました。
ネーミングルール
FlywayはSQLファイル名でバージョン管理を行います。 よって、ファイル名は非常に重要です。一方、パッケージソフトウェアのリリースにもネーミングルールがあり、こちらとすり合わせる必要があります。パッケージのバージョン管理は英字もOKなネーミングルールですが、Flywayが数字のみですので英字を数字に置き換えました。
SQLファイルのネーミング
V11.4.20201028.01.20200475.01__TGAZBUI_ALTER.ddl
Vaa.b.cccccccc.dd.eeeeeeee.ff__gggggggggggg.ddl
a.メジャーリリース
b.マイナーリリース
c.リリース日
d.リリース日の何回目か
e.管理番号
f.実行順序
g.コメント
組織
Flywayはファイル名でバージョン管理を行います。よって、リリースするSQLファイルの名前をFlywayのネーミングルールにする必要があります。また、テストも必要であり、開発部門の負担が増加します。それに見合う効果、具体的にはスキーマ変更を行う保守部門の工数削減、品質向上が必要です。
開発部門の負担増加
- FlywayのネーミングルールでSQLファイルを作成
- マイグレーションが期待通りに行われるかをテスト
保守部門のメリット
- スキーマ変更の手数を低減
- スキーマ変更の時間を短縮
- スキーマ変更のケアレスミスを低減
Flywayの導入で行ったこと
- 勉強会の開催
- ネーミングルール等、運用の提案
- ツールのロードテスト
- 継続的なフォロー
おわりに
新しいツールを本番運用に乗せるには、技術的な壁を乗り越え、組織に理解してもらうことが必要です。
-
%SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2 ↩