Db2とOracle 空文字の扱いの違いによる影響例

・Db2のOracle互換機能を使ってみた😃<3>　～空文字(長さ0の文字)の扱い(1)～
この投稿でOracleとNative Db2での空文字（‘’）の扱いの違いを具体例でご紹介しましたが、この違いによってどのような影響が出ることがあるのかを、実際にあった事例の中からご紹介したいと思います。

Oracleでは空文字がNULLとして扱われてしまうため、この違いが問題になりやすいのはDb2からOracleへの移行やデータ連携の場合と考えられますが、逆のOracleからDb2への移行の場合も考慮が必要となる場合もあるため、最初にそのケースをご紹介したいと思います。

■OracleからDb2への移行での事例

OracleからDb2への移行の場合、アプリケーションの中でNULLの代わりに空文字を使っていないことが明確に分かっていれば、特に気にする必要はありません。ここでご紹介する事例のお客様も、元のOracleのアプリケーションの中で、空文字でINSERTやUPDATEを行っている処理があるかを何らかの方法で事前に確認できれば良かったのですが、アプリケーションが多すぎてその確認は非常に困難でした。そのため「空文字を使用した処理があるという前提」で移行を進める必要がありました。

現行のOracleのデータベースのデータはそのままDb2に投入する計画です。
よってその中には空文字による更新や挿入によってNULLになっているデータが存在するかもしれません。
またアプリケーションは極力変更しない予定であるため、必要最低限Db2用に修正しなければいけない部分しか修正しません。そのため空文字でINSERTやUPDATEをする処理があれば、それはそのまま残ります。
そうなると、Oracleから移行されたデータはNULLになっていますが、新規にDb2アプリで入ってきたデータは長さ0のデータ(VARCHARの場合)やすべてブランク(CHARの場合)となり、NULLと混在してしまうという不都合が発生することになります。

お客様からの要望

この混在を避けるため、空文字で更新されたらDb2でもNULLに変更して更新できないか、という難題がお客様からプロジェクトチームに提示されました。

トリガーの利用案

Oracle互換のDb2であれば空文字はNULLとして扱われますが、Native Db2では空文字を自動的にNULLに変更する機能や設定はありません。
そのためこれはどうしたものかと悩んだ末考えついたのが、INSERTとUPDATEに対するBEFOREトリガーの利用です。
例えばUPDATEの場合は、NULL可能な列が空文字で更新されようとしたらその代わりにNULLで更新、空文字でなければ元の値で更新するというものです。
【UPDATEの例：空文字（‘’）更新をNULLに変更するトリガーのロジック】

このトリガーを以下の定義のTAB1について作成してみます。

TAB1は4列ありますが、NULLで更新できるのはC_COLとVC_COLの2列なので、その2列に対する更新が空文字であるかを確認しています。

CREATE OR REPLACE TRIGGER TRIG_UPD
BEFORE UPDATE ON TAB1
REFERENCING NEW AS TN
FOR EACH ROW  
WHEN (TN.C_COL = '' OR TN.VC_COL = '') 
SET TN.C_COL = NULLIF(TN.C_COL,'')
  　, TN.VC_COL = NULLIF(TN.VC_COL,'');

トリガーの検証結果

各UDPATEの後のSELECTで、最初の項目は、NVL関数で対象の列がNULL値であれば‘null’という表示にし、2番目の項目でデータのバイナリー値を表示させています。
【BEFOREトリガーの検証】

最初の2つのUDPATE文は対象列を空文字で更新していますが、C_COLもVC_COLもNULLに更新されています。
そして次の2つは‘B’で更新していますが、それはそのままの値で更新されています。

これはUDPATEトリガーの例ですが、INSERTの場合も同様でCREATE TRIGGERで記述しているBEFORE UPDATE ON TAB1の部分をINSERTに変更して、別の名前で作成すればよいです。

トリガー利用時の考慮事項

BEFOREトリガーは、このように更新する値を変更したい場合に便利な機能です。
ただトリガーですので、以下のような考慮事項もあります。
1. 表ごとにUPDATEとINSERT用の2つのトリガーを作成する必要がある。
2. 表の追加や既存表の列定義が変更になった場合、トリガーも整合性を合わせて追加や変更をする必要がある
3. トリガーの処理は元のトランザクションに含まれるので、大量更新ではトランザクションの時間に影響する可能性がある
4. Db2ではLOADでデータを挿入する時はトリガーは発行しないので、LOADの元データに空文字がある場合はデータの改修が必要になる
上記のような考慮事項はありますが、このアイデアはアプリケーションを変更する必要がないということは、非常に大きなメリットであるといえます。
また、表と対象の列数さえ分かれば、あとは地道にかつ機械的に作成していけば良いので、工数もある程度正確に見積もりができるという利点も、計画的にプロジェクトを進めていく上での重要な要素となるのではないでしょうか。

次に逆のケースですが、DB2からOracleへの移行の観点での参考事例は、
・Db2のOracle互換機能を使ってみた😃<5>　　近日投稿予定
で、ご紹介したいと思います。

関連投稿

・Db2のOracle互換機能を使ってみた😃<1>　～データベースの作成～

・Db2のOracle互換機能を使ってみた😃<2>　～可変長列の末尾ブランクの違い～

・Db2のOracle互換機能を使ってみた😃<3>　～空文字(長さ0の文字)の扱い(1)～

・Db2のOracle互換機能を使ってみた😃<5>　～空文字(長さ0の文字)の扱い(3)～

・Db2のOracle互換機能を使ってみた😃<6>　～空文字(長さ0の文字)の扱い(4)～

・Db2のOracle互換機能を使ってみた😃<7>　～NUMBERタイプの互換性(1)～

・Db2のOracle互換機能を使ってみた😃<8>　～NUMBERタイプの互換性(2)～

・Db2のOracle互換機能を使ってみた😃<9>　～NUMBERタイプの互換性(3)～

・Db2のOracle互換機能を使ってみた😃<10>　～NUMBERタイプの互換性(4)～

お断り：
当投稿は、Database migration to DB2-IBM Japan Community Wikiに掲載のブログを、Qiita用に書き直したものです。

本資料掲載事項は、ある特定の環境・使用状況においての正確性は確認されていますが、すべての環境において同様の結果が得られる保証はありません。
これらの技術を自身の環境に適用する際には、自己の責任において十分な検証と確認を実施いただくことをお奨めいたします。