Db2とOracle 空文字の扱いの違い

この投稿は、
・Db2のOracle互換機能を使ってみた😃<5>　～空文字(長さ0の文字)の扱い(3)～
の続きになります。

当回はDb2において、空文字更新の有無をチェックするための１つの案ご紹介します。

■空文字挿入/更新の有無をDb2に対してチェックするトリガーの紹介

前回で予告させていただいたトリガーですが、ご紹介するのは、空文字挿入/更新があったらその対象の表名を別の表に格納するトリガーの例です。

ここでは空文字挿入/更新があった表の情報を格納する表としてLOG_TABを用意し、TAB1を調査対象とし、INSERTとUPDATEのトリガーを作成しています。INSERTやUPDATEの値に空文字があった場合は、表名をLOG_TABのTAISHOU_HYOUの列に、そして、INSERTなら「I」をUPDATEなら「U」をI_OR_U列に挿入するようにしています。

Db2のOracle互換機能を使ってみた😃<4>　～空文字(長さ0の文字)の扱い(2)～　で、NULL可能な列が空文字で更新されようとしたら、その代わりにNULLで更新するBEFOREトリガーをご紹介しましたが、別表を更新する場合はAFTERトリガーにする必要があります。
なお、以下の例ではTAB1やLOG_TABにTIMESTAMP列がありますが、これはトリガーの検証時にどのINSERTやUPDATEで起動したのかを識別しやすいように追加しているものですので、トリガー作成上必須のものではありません。

AFTERトリガーの作成例

AFTERトリガーでSQL文を発行する場合、実行したいSQL文はCREATE TRIGGERステートメントの中でBEFOREとENDで囲んで記載しますが、このBEFOREとENDの間のSQL文の区切りを示す記号としてはセミコロン（；）を使用する必要があります。
そのため、トリガー作成の場合はファイルに記載して実行するのが良いでしょう。

--テスト表の作成
CREATE TABLE TAB1
(KCOL INT,
 C_COL CHAR(5),
 VC_COL VARCHAR(5),
 TMSTMP TIMESTAMP);

--空文字更新情報格納用の表作成
CREATE TABLE LOG_TAB
(TAISHOU_HYOU CHAR(30),
 TMSTMP TIMESTAMP,
 I_OU_U CHAR(1));

--INSERTトリガー
CREATE OR REPLACE TRIGGER TRIG_LOG_INS
AFTER INSERT ON TAB1
REFERENCING NEW AS TN
FOR EACH ROW
WHEN (TN.C_COL = '' OR TN.VC_COL = '') 
BEGIN ATOMIC
 INSERT INTO LOG_TAB VALUES ('TAB1',CURRENT TIMESTAMP,'I');--
END
;

--UPDATEトリガー
CREATE OR REPLACE TRIGGER TRIG_LOG_UPD
AFTER UPDATE ON TAB1
REFERENCING NEW AS TN OLD AS T0
FOR EACH ROW
WHEN ((TO.C_COL <> '' AND TN.C_COL = '')
   OR (TO.VC_COL <> '' AND TN.VC_COL = '') )
BEGIN ATOMIC
 INSERT INTO LOG_TAB VALUES ('TAB1',CURRENT TIMESTAMP,'U');--
END
;

ここでお気づきの方もいらっしゃるかと思いますが、上記例ではBEGIN ENDの間のセミコロンが；だけでなく、；--になっています。
その理由については、以下でご紹介していますので、参考にしてください。
・Db2 トリガー作成をファイルに記載する場合に、ステートメント終了文字がセミコロンのままでも実行できる裏技

INSERT実行例

テスト表TAB1に以下の4行を挿入してみます。

--CHAR列とVARCHAR列に文字を挿入
INSERT INTO TAB1 VALUES (1,'A','A',CURRENT TIMESTAMP);
--CHAR列に空文字を挿入
INSERT INTO TAB1 VALUES (2,'','B',CURRENT TIMESTAMP);
--VARCHAR列に空文字を挿入
INSERT INTO TAB1 VALUES (3,'C','',CURRENT TIMESTAMP);
--CHAR列とVARCHAR列に空文字を挿入
INSERT INTO TAB1 VALUES (4,'','',CURRENT TIMESTAMP);

<<<結果>>>
LOG_TABに入るのは、KCOLが2,3,4の更新（TMSTMPを黄色でマークした処理）、

KCOL=1以外のINSERT処理によって、LOG_TABに情報が入ったことが確認できます。

UPDATE実行例

次はUPDATEの場合です。
同じTAB1を使用しますが、更新前の値を揃えるためINSERTのテストで挿入した4行を、初期値としてC_COL=’X’,VC_COL=’Y’で全行更新してから検証を開始しています。

--4行すべてのCHAR列とVARCHAR列を文字で更新して初期化
UPDATE TAB1 SET C_COL='X', VC_COL='Y';

--CHAR列を空文字で更新
UPDATE TAB1 SET C_COL='', TMSTMP = CURRENT TIMESTAMP WHERE KCOL=1;
--VARCHAR列を空文字で更新
UPDATE TAB1 SET VC_COL='', TMSTMP = CURRENT TIMESTAMP WHERE KCOL=2;
--CHAR列とVARCHAR列を文字で更新
UPDATE TAB1 SET C_COL='A', VC_COL='A', TMSTMP = CURRENT TIMESTAMP WHERE KCOL=3;
--CHAR列とVARCHAR列を空文字で更新
UPDATE TAB1 SET C_COL='', VC_COL='', TMSTMP = CURRENT TIMESTAMP WHERE KCOL=4;

<<<結果>>>
LOG_TABに入るのは、KCOLが1,2,4の更新（TMSTMPを黄色でマークした処理）、

INSERTトリガーの場合は更新後の値が空文字であるかを判断すればよいですが、UPDATEトリガーの場合、更新後の値が空文字であるかのみで判断すると、更新対象でない列にすでに空文字が入っていた場合もトリガーが起動してしまいます。

上記のUPDATEのテストのKCOL=4の行を例にして説明します。
KCOL=4 の行はC_COLとV_COLも空文字で更新したので、今の状態はC_COLは固定長なのでブランクで充填、VC_COLは可変長なので空文字そのものが入っています。
ここでC_COLのブランクを‘B'で更新したとします。
すると、KCOL=4の行の更新後の値は、C_COL='B', VC_COL='' となります。
この場合、トリガーの条件が（TN.C_COL='' OR TN.VC_COL=''）だけだと、TN.C_COL=''には合致しませんが、TN.VC_COL=''の方の条件に合致してしまい、C_COL='B' だけの更新もトリガーが動いてしまいます。
そのためUPDATEトリガーの場合は、「更新前の値は空文字以外（TO.列名<>’’）」という判断も各列に対して追加しているのです。

なお、今回の例ではTN.列名=''で空文字を挿入したり更新したりしていますが、TRIM関数の結果が空文字になった場合でも、同じトリガーの条件で構いません。

以上、せめて空文字挿入/更新の対象の表が分かれば調査するアプリケーションの範囲も少しは絞れるのでは、ということでトリガーの利用案をご紹介しましたが、実際問題、すべての表にトリガーを作成するのは現実的ではないとも言えますので、怪しい表に対して確認する目的で使うかどうか、１つの候補にしていただければと思います。

また調査の結果、空文字挿入/更新が確認された場合は、Oracleに移行するのであれば、空文字がNULLに扱われていいのかを検討し、NULLでは困るのであればブランク更新に変更するとか、NULLがOKな場合でも、さらに空文字検索があったならばIS NULLにするとか、それぞれのアプリケーションの要件にあわせての変更が必要になってくるかと思います。

■「空文字(長さ0の文字)の扱い」まとめ

4回にわたってDb2とOracleの仕様の違いとして空文字の扱いを取り上げましたが、この仕様はOracle互換のDb2ではVARCHAR2互換をONにすることによって暗黙的に変更になるため、ともすると忘れがちな仕様の違いであり、さらにVARCHAR2列だけが対象とも思われている場合も多いのですが、可変長列/固定長とも対象になりますので、ここで改めて認識をしていただきたいということで、ご紹介させていただきました。

この空文字に関しては、以前の投稿でご紹介した2つの事例にもあるように、OracleからDb2へ、あるいはDb2からOracleやOracle互換Db2への移行のどちらの場合も考慮が必要な仕様の違いと言えます。
ただ、OracleからDb2への移行の方がDb2のOracle互換モードを使うことで対応しやすい、ということもご理解いただけたなら幸いです。

おわり

関連投稿

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

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

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

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

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

・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用に書き直したものです。

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