SQL行選択基準を切り替える、再考｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2020.04.08

SQL行選択基準を切り替える、再考

Ted Holt 著

昨年10月の RPG & DB2 Summit で、IBMのRob Bestgen氏が「System i Developer」のPaul Tuohy氏に、IBM Lab Servicesで目にしたという、あるコーディング手法について話しているのを耳にしました。彼らによれば、ゾッとするようなコーディング手法ということでした。その時、私は「嘘だろう、その手法についてはitjungle.comに記事を書いたことがある」と思いました。皆さんは、ゾッとする準備はできているでしょうか。

Rob氏がPaul氏に話していたのは、スイッチを使用することでWHERE節の一部が有効化/無効化されるSQL照会を目にしたということでした。この手法を発案したのは私ではありません（自分はこれまで独創的なアイデアを思い付いたことなどなかったと数年前に実感しています）。しかし、私は何度もこの手法を使用しています（うまく機能したことを付け加えておきます）。さらに、もう約16年半前となる2003年には、この威厳ある刊行物で、その手法についての記事も書いています。以下で、その動作について説明します。

たとえば、顧客の集合を取得する照会があるとします（もっと現実的な例にするのであれば、顧客注文を取得するとしたらよいのでしょうが、ソースコードの分量をなるべく少なくしたいので）。この集合は、3つの異なる基準から構築できるようにする必要があります。すべての顧客を取得することが必要な場合があります。特定の区分コードを割り当てた顧客のみが必要な場合があります。ある範囲のZIP（郵便）コードの顧客のみが必要な場合があります。

どのようにしたらよいでしょうか。照会を1回書いて、2回クローンすればよいのですが、私たちの多くはソースコードをクローンするのを嫌がります。1か所、変更するとなったら、コピーの方も変更しなければならないからです。代わりに、WHERE節の一部を有効化/無効化するスイッチを使用することもできます。

dcl-s  SelectAll       char(1)    inz('0');
dcl-s  SelectByClass   char(1)    inz('0');
dcl-s  SelectByZip     char(1)    inz('0');

dcl-s  iClass          char(1);
dcl-s  iFromZip        char(5);
dcl-s  iThruZip        char(5);

dcl-ds  Customer  qualified;
    Account       packed ( 5);
    Name          char   (20);
    ZipCode       char   ( 5);
    Class         char   ( 1);
end-ds;

exec sql declare Inp cursor for
   select cusnbr, cusname, cuszip, cusclass
     from customers
   where
       (
          (:SelectAll = '1')
          OR
          (:SelectByClass = '1' AND cusclass = :iClass)
          OR
          (:SelectByZip = '1' AND
                cuszip BETWEEN :iFromZip AND :iThruZip)
        );

exec sql  open Inp;

dow '1';
   exec sql  fetch Inp into :Customer;
   if SqlState >= '02000';
      leave;
   endif;
*** do something with the retrieved row
enddo;

exec sql  close Inp;

これらがスイッチです。

dcl-s  SelectAll       char(1)    inz('0');
dcl-s  SelectByClass   char(1)    inz('0');
dcl-s  SelectByZip     char(1)    inz('0');

これらのスイッチの値は、行選択基準を有効化および無効化します。SelectAllの値が1の場合、WHERE節は次のようになります。

WHERE 
 (                                     
  ('1' = '1')                  
OR                                  
  ('0' = '1' AND cusclass = ' ')                
OR                                  
  ('0' = '1' AND cuszip BETWEEN '     ' AND '     ')

すべての行で1つ目の条件は真であるため、すべての行が選択されます。SelectByClassが1で、iClassがBの場合、WHERE節は次のようになります。

WHERE 
 (                                     
  ('0' = '1')                  
OR                                  
  ('1' = '1' AND cusclass = 'B')
OR 
  ('0' = '1' AND cuszip,1,5 BETWEEN '     ' AND '     ')
 )

1つ目の条件は偽ですが、2つ目の条件は、顧客区分がBである行で真と判定されます。区分Bの顧客が選択されます。

IBMは、この手法が好みではないようです。Rob氏がPaul氏と私に話したのは（いつの間にか私も二人の会話に加わっていました）、このような手法は、必ず照会オプティマイザーに指標を無視させることになる、ということでした。

IBMでは、その代わりにどうするべきだと考えているのでしょうか。動的SQLを使用するということです。

dcl-s  SelectAll       char(1)    inz('0');
dcl-s  SelectByClass   char(1)    inz('0');
dcl-s  SelectByZip     char(1)    inz('0');

dcl-s  iClass          char(1);
dcl-s  iFromZip        char(5);
dcl-s  iThruZip        char(5);

dcl-s  Stmt            varchar(512);

dcl-ds  Customer  qualified;
    Account       packed ( 5);
    Name          char   (20);
    ZipCode       char   ( 5);
    Class         char   ( 1);
end-ds;

Stmt = 'select cusnbr, cusname, cuszip, cusclass from customers;

select;
   when SelectByClass = '1';
      Stmt += ' where cusclass = ?';
   when SelectByZip = '1';
      Stmt += ' where cuszip between ? and ?';
endsl;

exec sql prepare x from :Stmt;

exec sql  declare Inp cursor for x;

select;
   when SelectByClass = '1';
      exec sql  open Inp using :iClass;
   when SelectByZip = '1';
      exec sql  open Inp using :iFromZip, :iThruZip;
   other;
      exec sql  open Inp;
endsl;

dow '1';
   exec sql  fetch Inp into :Customer;
   if SqlState >= '02000';
      leave;
   endif;
*** do something with the retrieved row
enddo;

exec sql  close Inp;

コードは少し長めですが、複雑ではありません。やはりスイッチが列の選択を制御するという点で、同じような動作です。openが3つあることに注目してください。これは、WHEREのバージョンによって引数リストが異なるためです。

ただし、コードはまったく同じではありません。静的なバージョンでは、顧客区分での選択と郵便番号での選択の両方を同時に有効化することができます。動的なバージョンでも、そのように動作するようにできましたが、わざわざそうすることはしませんでした。

IBMからのメッセージはこのようなものでした。最後に私見をいくつか記しておこうと思います。

『 The Four Hundred』のテクニカルエディターとして私は、皆さんが作業を行う際に役立つ実際的な情報を提供するために最善を尽くしています。このことは、私の記事でも他の著者の記事でも変わりありません。私たちが公開しているものが正確であるだけでなく、ベストプラクティスでもあることが保証されるように、できる限りのことを行っています。2003年にこの手法を公開したときも、曇りひとつなく、最善を尽くしたつもりであり、自分が紹介しているプログラミング手法は正当なものだと思っていました。
2003年以降、動的SQLのパフォーマンスは向上しました。当時は、話を聞いたどの専門家からも、できる限り動的SQLは避けるようと勧められたものです。
パフォーマンスは相対的です。何百万もの行がある表に対するテーブル走査は、パフォーマンスがよくないのが一般的です。数千行に対するテーブル走査は、大した処理とはならないかもしれません。
動的SQLは、常にあった制限に今もなお悩まされています。すなわち、プログラムは表を使用していることを知りません。上の例では、プログラム参照表示（DSPPGMREF）が、静的SQLを使用するプログラムが顧客ファイルを使用すると教えてくれます。動的バージョンに対するDSPPGMREFでは、そうはなりません。つまり、ドキュメンテーションパッケージも、おそらく知らないということです。

最後に、IBMでは私たちの何人かがこの手法を使用していることを把握しているため、おそらく、オプティマイザーにそれを探させて、それに応じて照会を最適化させることができるでしょう。IBMのスタッフは優秀であり（皮肉ではなく、心からそう思います）、そのようなことが可能であるなら、それを実現する方法を見つけることができます。

私には、この静的SQL手法に問題があるとは今も思えません。私にとっては、この手法は道具箱に入っているもうひとつの道具なのです。使用するかどうか、そして、いつどこで使用したらよいかについては、読者の皆さんの判断にお任せします。