メニューボタン
IBMi海外記事2020.12.23

SQLと展開しているサブファイル

Ted Holt 著

SQLは、サブファイルのロード時には、レコード レベル アクセス(RLA)のようにはうまく機能しないと言われるのを耳にしたことが何度かあります。どうしてそう感じられるのかは分かります。彼らは、たいてい、ポインターの位置をキー値へ戻すことについて考えていて、一方、SQLカーソルにはSETLL(下限の設定)命令コードはありません。

それでも、私はSQLの方が良いと思うので、この記事では、良い実例になると思われる1つのケースを紹介したいと思います。私の頭に思い浮かんでいたのは、展開しているサブファイルでした。両者の間には類似点があります。以下について考えてみてください。

  • サブファイルをクリアするたびに、新たなカーソルをオープンします。
  • ROLLUP標識がオンになるたびに、より多くのデータをフェッチします。

残すは、細かな事柄ばかりかもしれません。

この記事で使用されているコードは、 ここからダウンロードできます。

プロセスの説明のために、プログラムをササッと作ってみました。コードの出来映え云々については棚上げして、業務用レベルではないことについて注意だけはしておきます。コードの長さ云々については棚上げして、私なりに可能な限り短くしたつもりだという弁解だけはさせていただきます。

まずは、サブファイルを含む表示装置ファイルです。

A                                      DSPSIZ(24 80 *DS3)
A                                      REF(EMPS)
A                                      INDARA

A          R SFL01                     SFL
A            CLOCK     R        O  8  2
A            NAME      R        O  8  9
A            TYPE      R        O  8 30
A            DEPARTMENTR        O  8 37

A          R CTL01                     SFLCTL(SFL01)
A                                      CA03(03)
A                                      OVERLAY
A  41                                  SFLDSPCTL
A  42                                  SFLDSP
A  40                                  SFLCLR
A  43                                  SFLEND(*MORE)
A                                      SFLSIZ(6)
A                                      SFLPAG(5)
A                                      ROLLUP(10)
A            SFLRCDNBR      4S 0H      SFLRCDNBR
A                                  1 33'Employee Inquiry'
A                                  3  5'Sort:'
A            SORTOPTION     1   B  3 11
A                                  3 15'1=Name'
A                                  4 15'2=Department, Name'
A                                  5 15'3=Clock'
A                                  3 58'Select department:'
A            DEPARTMENT     2   B  3 77
A                                  7  2'Clock  Name                 Type  -
A                                       Department'
A                                      DSPATR(UL)

A          R SCREEN01
A            MESSAGE       78   O 14  2
A                                 15  5'F3=Exit'

今度はRPGです。一気に全体を読み通そうとしないことをお勧めします。特徴的な箇所について、いくつか指摘することとしようと思っています。それら以外の箇所については、必要に応じて目を通していただくよう、読者の皆さんにお任せとします。

**free
ctl-opt  option(*srcstmt: *nodebugio)
         main(VAS023R_Main)
         actgrp(*new);

dcl-f  Display    workstn(*ext)  qualified
                                 extdesc('VAS0230D')
                                 extfile(*extdesc)
                                 sfile(SFL01: RRN01)
                                 indds(WsInd)
                                 usropn;

dcl-ds  CTL01_rec_t     likerec(Display.CTL01:    *all)   template;
dcl-ds  SFL01_rec_t     likerec(Display.SFL01:    *all)   template;
dcl-ds  SCREEN01_rec_t  likerec(Display.SCREEN01: *all)   template;

dcl-c  SFL01_PageSize      5;
dcl-s  RRN01               zoned(3);

dcl-ds  WsInd    len(99)  qualified;
   ExitKey       ind      pos( 3);
   RollUp        ind      pos(10);
   SflClr        ind      pos(40);
   SflDspCtl     ind      pos(41);
   SflDsp        ind      pos(42);
   SflEnd        ind      pos(43);
end-ds  WsInd;

dcl-c  SortByName        '1';
dcl-c  SortByDepartment  '2';
dcl-c  SortByClock       '3';

dcl-c  c_SQL_EOD      '02000';

dcl-proc  VAS023R_Main;

     monitor;
        open  Display;
        Driver();
        close *all;
     on-error;
        // error handling goes here
     endmon;
     return;

end-proc  VAS023R_Main;

dcl-proc  Driver;

   dcl-ds  CTL01_rec         likeds(CTL01_rec_t)     inz;
   dcl-ds  SCREEN01_rec      likeds(SCREEN01_rec_t)  inz;

   dcl-s  Size01    like(RRN01);
   dcl-s  EndOfData ind;

   dcl-s  SaveSortOption     like(CTL01_rec.SortOption);
   dcl-s  SaveDepartment     like(CTL01_rec.Department);

   CTL01_rec.SortOption = SortByName;
   CTL01_rec.Department = *blanks;

   dow '1';
      if CTL01_rec.SortOption <> SaveSortOption
      or CTL01_rec.Department <> SaveDepartment;
         LoadFirstPage (CTL01_rec: Size01: EndOfData: SCREEN01_rec);
         CTL01_rec.SFLRCDNBR = Size01;
         SaveSortOption = CTL01_rec.SortOption;
         SaveDepartment = CTL01_rec.Department;
      endif;

      WsInd.SflDspCtl  =  *on;
      WsInd.SflDsp     =  (Size01 > *zero);
      WsInd.sflClr     =  *off;
      WsInd.sflEnd     =  EndOfData;
      write   Display.Screen01  Screen01_rec;
      exfmt   Display.CTL01     CTL01_rec;
      if WsInd.ExitKey;
         leave;
      endif;
      clear Screen01_rec.Message;
      if WsInd.RollUp;
         LoadOnePage (Size01: EndOfData: SCREEN01_rec);
         CTL01_rec.SFLRCDNBR = Size01;
      endif;
   enddo;

end-proc  Driver;

dcl-proc  LoadFirstPage;

   dcl-pi  *n;
      ioCTL01_rec     likeds(CTL01_rec_t);
      ouSize          like(RRN01);
      ouEndOfData     ind;
      ouScreen01_rec  likeds(SCREEN01_rec_t);
   end-pi;

   dcl-s   Statement         varchar(512);
   dcl-s   Department        char(2);

   WsInd.SflDspCtl   = *off;
   WsInd.SflDsp      = *off;
   WsInd.SflClr      = *on;
   WsInd.SflEnd      = *off;
   write  Display.CTL01  ioCTL01_rec;
   WsInd.SflClr      = *off;

   ouSize = *zero;
   ouEndOfData = *off;

   Statement = 'select clock, name, department, type from emps';

   if ioCTL01_rec.Department <> *blanks;
      Statement += ' where department = ?';
   endif;

   select;
      when ioCTL01_rec.SortOption = SortByDepartment;
         Statement += ' order by department, name';
      when ioCTL01_rec.SortOption = SortByClock;
         Statement += ' order by clock';
      other;
         Statement += ' order by name';
   endsl;

   Statement += ' optimize for ' + %char(SFL01_PageSize) + ' rows';

   exec sql  close Inp;

   exec sql  prepare x from :Statement;
   if SqlState >= c_SQL_EOD;
      ouScreen01_rec.Message = '10: SQL failed with state ' + 
                               SQLState + '.';
      return;
   endif;

   exec sql  declare Inp cursor for x;
   if SqlState >= c_SQL_EOD;
      ouScreen01_rec.Message = '20: SQL failed with state ' + 
                               SQLState + '.';
      return;
   endif;

   if ioCTL01_rec.Department = *blanks;
   exec sql open Inp;
      if SqlState >= c_SQL_EOD;
         ouScreen01_rec.Message = '30: SQL failed with state ' + 
                                  SQLState + '.';
         return;
   endif;
   else;
      Department = ioCTL01_rec.Department;
      exec sql  open Inp using :Department;
      if SqlState >= c_SQL_EOD;
         ouScreen01_rec.Message = '35: SQL failed with state ' + 
                                  SQLState + '.';
         return;
      endif;
   endif;

   LoadOnePage (ouSize: ouEndOfData: ouScreen01_rec);
   ioCTL01_rec.SFLRCDNBR = ouSize;

end-proc  LoadFirstPage;

dcl-proc LoadOnePage;

   dcl-pi *n;
      ioSize          like(RRN01);
      ioEndOfData     ind;
      ouScreen01_rec  likeds(SCREEN01_rec_t);
   end-pi;

   dcl-ds  SFL01_rec    likeds(SFL01_rec_t)  inz;

   dcl-ds  DataArray  qualified    dim(SFL01_PageSize);
      clock       packed( 5);
      name        char  (16);
      department  char  ( 2);
      type        char  ( 1);
   end-ds;

   dcl-s   Ndx          uns(5);

   if ioEndOfData;
      return;
   endif;

   RRN01 = ioSize;

   exec sql  fetch Inp for :SFL01_PageSize rows into :DataArray;
   if SqlState > c_SQL_EOD;
      ouScreen01_rec.Message = '40: SQL failed with state ' +
                               SQLState + '.';
      return;
   endif;

   ioEndOfData = (SqlState >= c_SQL_EOD);

   for Ndx = 1 to sqler3;
      eval-corr  SFL01_rec = DataArray (Ndx);
      RRN01 += 1;
      write  Display.SFL01   SFL01_rec;
   endfor;

   ioSize = RRN01;

end-proc LoadOnePage;

表示装置ファイルはシンプルですが、いくつかのコード行には触れておきたいと思います。

A  43                                  SFLEND(*MORE)

A                                      ROLLUP(10)
A            SFLRCDNBR      4S 0H      SFLRCDNBR
  • 展開しているサブファイルで、ROLLUPキーをテストする必要があります。サブファイルの最後のページがディスプレイ上にあるときに、ユーザーが別のページを要求すると、標識10はオンになります。
  • SFLENDキーワードは、標識43に条件付けられています。FETCHがデータを返さなくなるまで、この標識をオフにし続けます。通常の環境では、それはSQL状態02000ですが、エラーが原因である場合もあります。
  • SFLRCDNBRキーワードを使用して、最後に追加されたページにディスプレイを位置付けます。

RPGについては、2つのサブプロシージャーがこのワークロードを処理します。LoadFirstPageはサブファイルをクリアし、SQLカーソルをオープンして、サブファイルの最初のページをロードします。

dcl-proc  LoadFirstPage;

   dcl-pi  *n;
      ioCTL01_rec     likeds(CTL01_rec_t);
      ouSize          like(RRN01);
      ouEndOfData     ind;
      ouScreen01_rec  likeds(SCREEN01_rec_t);
   end-pi;

   dcl-s   Statement         varchar(512);
   dcl-s   Department        char(2);

   WsInd.SflDspCtl   = *off;
   WsInd.SflDsp      = *off;
   WsInd.SflClr      = *on;
   WsInd.SflEnd      = *off;
   write  Display.CTL01  ioCTL01_rec;
   WsInd.SflClr      = *off;

   ouSize = *zero;
   ouEndOfData = *off;

   Statement = 'select clock, name, department, type from emps';

   if ioCTL01_rec.Department <> *blanks;
      Statement += ' where department = ?';
   endif;

   select;
      when ioCTL01_rec.SortOption = SortByDepartment;
         Statement += ' order by department, name';
      when ioCTL01_rec.SortOption = SortByClock;
         Statement += ' order by clock';
      other;
         Statement += ' order by name';
   endsl;

   Statement += ' optimize for ' + %char(SFL01_PageSize) + ' rows';

   exec sql  close Inp;

   exec sql  prepare x from :Statement;
   if SqlState >= c_SQL_EOD;
      ouScreen01_rec.Message = '10: SQL failed with state ' +
                               SQLState + '.';
      return;
   endif;

   exec sql  declare Inp cursor for x;
   if SqlState >= c_SQL_EOD;
      ouScreen01_rec.Message = '20: SQL failed with state ' + 
                               SQLState + '.';
      return;
   endif;

   if ioCTL01_rec.Department = *blanks;
   exec sql open Inp;
      if SqlState >= c_SQL_EOD;
         ouScreen01_rec.Message = '30: SQL failed with state ' + 
                                  SQLState + '.';
         return;
   endif;
   else;
      Department = ioCTL01_rec.Department;
      exec sql  open Inp using :Department;
      if SqlState >= c_SQL_EOD;
         ouScreen01_rec.Message = '35: SQL failed with state ' +
                                  SQLState + '.';
         return;
      endif;
   endif;

   LoadOnePage (ouSize: ouEndOfData: ouScreen01_rec);
   ioCTL01_rec.SFLRCDNBR = ouSize;

end-proc  LoadFirstPage;
  • 以前の記事で記したように、WHEREおよびORDER BY節でフレキシブルな照会を使用できるときには動的SQLを使用する、というのがIBMのお勧めのようです。
  • 私は optimize for n rows 文節を追加しました。nは1ページ上の行数です。これはパフォーマンス向上に役立つかもしれません。照会エンジンは、すべての行を検索する必要がある場合に使用するのとは異なるプランを使用するかもしれないからです。
  • カーソルがオープンすると、最初のページをロードすることができます。そこから、もう一方の重要なサブプロシージャーであるLoadOnePageに移動します。
dcl-proc LoadOnePage;

   dcl-pi *n;
      ioSize          like(RRN01);
      ioEndOfData     ind;
      ouScreen01_rec  likeds(SCREEN01_rec_t);
   end-pi;

   dcl-ds  SFL01_rec    likeds(SFL01_rec_t)  inz;

   dcl-ds  DataArray  qualified    dim(SFL01_PageSize);
      clock       packed( 5);
      name        char  (16);
      department  char  ( 2);
      type        char  ( 1);
   end-ds;

   dcl-s   Ndx          uns(5);

   if ioEndOfData;
      return;
   endif;

   RRN01 = ioSize;

   exec sql  fetch Inp for :SFL01_PageSize rows into :DataArray;
   if SqlState > c_SQL_EOD;
      ouScreen01_rec.Message = '40: SQL failed with state ' + SQLState + '.';
      return;
   endif;

   ioEndOfData = (SqlState >= c_SQL_EOD);

   for Ndx = 1 to sqler3;
      eval-corr  SFL01_rec = DataArray (Ndx);
      RRN01 += 1;
      write  Display.SFL01   SFL01_rec;
   endfor;

   ioSize = RRN01;
end-proc LoadOnePage;
  • サブファイルは、中断したところからロードを再開します。カーソルは、中断したところからフェッチを再開します。こういうところから、展開しているサブファイルとSQLがうまくフィットするわけです。
  • 1度に1行ずつカーソルをループする必要はありません。1度のフェッチで、画面全体のデータを取り込みます。SQLER3は、フェッチが返した行数を教えてくれます。データ構造配列をループすることで、サブファイルが素早くロードされます。

以下の画面が表示されます。

スクリーンショット

これをレコード レベル アクセスで試したくはないものです。SQLがデータを取得するときには、ソートと選択は、はるかに簡単です。

これはあくまで私個人の意見ですが、SQLを使用するときに比べて、RLAを使用するときには、サブファイルの処理はいくつかの点でより簡単だと思います。良い例は、特定のキー値へ移動し、ページを戻して(前ページ)データを表示するケースです。とは言うものの、たとえ、グリーン スクリーンからGUIへの、もっと大掛かりで重要な移行へ向けての必要なステップであるというだけの理由であっても、私はRLAからSQLへの移行を推奨したいと思います。

あわせて読みたい記事

PAGE TOP