MERGEとチキン・エッグ問題

「鶏が先か、卵が先か」という命題がありますが、それについてじっくり考える時間はありません。けれども、SQLには「鶏が先か、卵が先か」式のデータベース更新で、問題の解決に役に立つ手法があることにはホッとしています。たとえば、2つのステートメントを実行する必要があり、お互いが遠慮し合うかのように相手に先に実行してもらう必要があるという状況です。

たとえば、在庫管理を行うIBM i システムのサポートを行っているとします。品目マスター表（物理ファイル）には、品目の説明、標準原価、表示価格といった一般的な情報が保存されています。

create table ItemMaster
  (ItemNumber     char(6),
   Revision       dec(3),
   Description    char(20),
   Cost           dec(5,2),
   Price          dec(5,2),
   EffectiveFrom  date  not null,
   EffectiveThru  date  not null with default '9999-12-31',
   primary key (ItemNumber, Revision));
   
insert into ItemMaster values
('AA-101', 1, 'Widget',  2.50, 3.00, '2019-01-01', '9999-12-31'),
('BB-202', 1, 'Doodad',  1.00, 2.00, '2019-02-01', '2019-07-31'),
('BB-202', 2, 'Doodad',  1.25, 2.15, '2019-08-01', '9999-12-31'),
('CC-303', 1, 'Thingie', 3.50, 4.25, '2019-01-01', '9999-12-31');

多くのシステムでは、品目マスターには、品目ごとに1つの行（レコード）があります。一度に保存できるのは一組の値だけです。つまり、1つの説明、1つの原価、1つの価格の一組です。列（フィールド）を更新すると、その列の古い値は失われます。

けれども、このシステムでは1つの品目に複数の行が許容されています。各行には、有効期間の日付範囲があります。どの品目の行の日付にも重複がないため、日付が指定されたら、ある品目の説明、標準原価、表示価格がどのようなものなのかが分かります。

品目の行はリビジョン（バージョン）番号によって識別されます。この番号は、品目の新たなリビジョンが作成されると値が増えます。そのため、品目のアクティブなリビジョンを識別するには、2つの方法があります。一番大きいリビジョン番号を探す方法と、有効期限切れ（有効期間終了）の日付が9999-12-31になっているものを探す方法です。

品目の説明、原価、価格を変更するには、2つの操作が必要です。

1. 最新リビジョンの有効期間終了日を、9999-12-31から、新たな値が有効になる前の日付へ変更します。
2. 新しいデータと9999-12-31の有効期間終了日を持つ、新しい行を追加します。

つまり、既存のアクティブな行をUPDATEする必要があり、新しいアクティブな行をINSERTする必要があるということになります。

少し考えてみてください。こうした変更は、どのようにして行うのでしょうか。UPDATEが先か、INSERTが先か、どちらでしょうか。どちらでも問題が生じる可能性があります。先にINSERTする場合は、有効期限切れの日が9999-12-31である品目の行が複数あることになります。先にUPDATEする場合は、新しい行のリリース番号を生成するために最新のアクティブな行から値を取得することがより難しくなります。これは各品目のMAX（REVISION）を探す必要があるからです。前述した「卵が先か、鶏が先か」という状況です。

また、2つのうちの1つ目が完了しようとしていて、2つ目がエラーでキャンセルになるという問題もあり、これは非常に起こる可能性が高いものです。そのいずれも、克服できないものではありません。指摘しておきたいことは、このような問題は慎重に考えておく必要があるということです。

幸いなことに、良い方法があります。それはMERGEを使用する方法です。

まず、品目マスターに適用される変更のファイルメンテナンス表を考えてみます。

create table ItemUpdates
  (ItemNumber   char(6),
   Description  char(20),
   Cost         dec(5,2),
   Price        dec(5,2));
  
insert into ItemUpdates values
( 'AA-101', 'Widget',  3.00, 3.55),
( 'BB-202', 'Doodad',  1.50, 2.25);

2つの品目のデータ（説明、原価、価格）があります。これらは、品目の現行の行にあるのと同じ値でも、新しい値でも構いません。この例では、説明をそのままのままにして、両方の品目の原価と価格のみを変更することにしました。

MERGEは、1つの品目につき、2つのトランザクションを必要とします。1つはUPDATEの実行のためで、1つINSERTの実行のためです。しかし、ItemUpdates表には、1つの品目につき1行しかありません。次のようにして、1つのトランザクションから2つのトランザクションを生成します。

select m.ItemNumber, m.Revision, m.Description, m.cost, m.price
         from ItemMaster as m
         join ItemUpdates as u
           on m.ItemNumber = u.ItemNumber
        where m.EffectiveThru = '9999-12-31'
       union all
       select m.ItemNumber, m.Revision + 1, u.Description, u.cost, u.price
         from ItemMaster as m
         join ItemUpdates as u
           on m.ItemNumber = u.ItemNumber
        where m.EffectiveThru = '9999-12-31'

この照会は、2つの入力列を4つの出力列に変換します。1つ目のSELECTは、現行のリビジョンからデータを取得します。リビジョン番号を使用し、説明、原価、および価格の列は無視します。2つ目のSELECTは、新しい説明、原価、および価格で新たなリビジョンを生成します。

この照会が、MERGEのソース データ セットになります。つまり、USINGの後に続きます。では、MERGEの魔法を見てみましょう。

merge into ItemMaster as tgt
using (select m.ItemNumber, m.Revision, 
              m.Description, m.cost, m.price
         from ItemMaster as m
         join ItemUpdates as u
           on m.ItemNumber = u.ItemNumber
        where m.EffectiveThru = '9999-12-31'
       union all
       select m.ItemNumber, m.Revision + 1, 
              u.Description, u.cost, u.price
         from ItemMaster as m
         join ItemUpdates as u
           on m.ItemNumber = u.ItemNumber
        where m.EffectiveThru = '9999-12-31') as src
   on (src.ItemNumber, src.Revision) = (tgt.ItemNumber, tgt.Revision)
 when matched then
    update set tgt.EffectiveThru = '2019-09-30'
 when not matched then
    insert values (src.ItemNumber, src.Revision, src.Description,
                   src.Cost, src.Price, '2019-10-01', '9999-12-31')

UPDATEは1つの列のみを変更します。つまり、有効期限切れの日付を、新しいリビジョンが有効になる日の前の日付に更新します。INSERTは、新たなリビジョンを作成します。素晴らしいです。

たとえばRPGプログラムなど、本番の状況での使い方を見ておいた方が現実的でしょう。

dcl-s  NewEffectiveDate   date;
dcl-s  NoExpirationDate   date    inz(d'9999-12-31');

exec sql
   merge into ItemMaster as tgt
   using (select m.ItemNumber, m.Revision,
                 m.Description, m.cost, m.price
            from ItemMaster as m
            join ItemUpdates as u
              on m.ItemNumber = u.ItemNumber
           where m.EffectiveThru = :NoExpirationDate
          union all
          select m.ItemNumber, m.Revision + 1,
                 u.Description, u.cost, u.price
            from ItemMaster as m
            join ItemUpdates as u
              on m.ItemNumber = u.ItemNumber
           where m.EffectiveThru = :NoExpirationDate) as src
      on (src.ItemNumber, src.Revision) =
            (tgt.ItemNumber, tgt.Revision)
    when matched then
       update set tgt.EffectiveThru = :NewEffectiveDate - 1 day
    when not matched then
       insert values (src.ItemNumber, src.Revision,
                      src.Description, src.Cost, src.Price,
                      :NewEffectiveDate, :NoExpirationDate);

結果はどうなるでしょうか。ご自分で確認してください。まずは、実行前です。

続いて、実行後です（新たな有効期間開始日は2019年10月1日としています）。

またまた、MERGEがやってくれました。

ここで一言忠告しておきます。このような更新を行うときは、コミットメント制御下で行うようにする必要があります。MERGEのUPDATE部が成功してINSERT部が失敗したり、また、その逆になったりすることがあり得ます。一部が成功して一部が失敗した場合は、ROLLBACKできる必要があります。部分的に更新されているデータベースというのは、よろしくありません。

約11年前に、Joe Celko氏がこのような問題についての記事を書いています（ただし、2、3の相違点があります）。このトピックについてさらに詳しくお知りになりたい場合は、下記の記事をご覧ください。書籍であれ、オンライン記事であれ、彼が書いたものはどれも読んでみる価値があります。

あわせて読みたい記事

今はIBM i の一部に過ぎないクラウドは、将来ハイブリッド化が進む

IBMｉ海外記事2024.04.24

IBM i のショップは、まだ生成AI導入に向けての準備段階にある

IBMｉ海外記事2024.04.24

IBM i のセキュリティ上の懸念が最高潮に達する一方で、ソリューションの導入は遅れ気味、Fortra社の市場調査

IBMｉ海外記事2024.04.10

プロシージャー主導型RPGと、オブジェクト指向プログラミングの柱の導入

IBMｉ海外記事2024.04.10