IBMｉ海外記事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への移行を推奨したいと思います。
