Guru: SQL and Expanding Subfiles
October 26, 2020 Ted Holt
I’ve heard it said on more than one occasion that SQL does not work as well as record-level access (RLA) when loading subfiles. I understand why people feel that way. They’re usually thinking about repositioning to a key value, and there is no SETLL (Set Lower Limit) op code in an SQL cursor.
Yet I think SQL is better, and today I’d like to share one case that I think presents a good illustration. I had in mind the expanding subfile. There’s a parallel between the two. Consider:
- Every time you clear the subfile, you open a new cursor.
- Every time the ROLLUP indicator comes on, you fetch more data.
The rest is minor details.
This story contains code, which you can download here.
I threw together a program to illustrate the process. Without apologizing for my code, keep in mind that it’s not industrial strength. Without apologizing for the length, let me say that I made it as short as I thought I could get away with.
First, a display file with a subfile.
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'
And now some RPG. I suggest you not try to read it all at once. I’ll point out the salient features and leave it to you to work through other pieces of the code as required.
**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;
The display file is simple, but there are a few lines of code that I want to mention.
A 43 SFLEND(*MORE) A ROLLUP(10) A SFLRCDNBR 4S 0H SFLRCDNBR
- In an expanding subfile, you need to test for the ROLLUP key. Indicator 10 turns on if the last page of the subfile is on the display and the user requests another page.
- The SFLEND keyword is conditioned to indicator 43. Keep this indicator off until the FETCH fails to return data. In normal circumstances, that’s SQL state 02000, but it could also be due to an error.
- Use the SFLRCDNBR keyword to position the display to the page that was last added.
As for the RPG, two subprocedures handle the workload. LoadFirstPage clears the subfile, opens the SQL cursor, and loads the first page of the subfile.
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;
- As I wrote recently, IBM likes for us to use dynamic SQL when the WHERE and ORDER BY clauses allow for flexible queries.
- I added the optimize for n rows clause, where n is the number of rows on one page. This may help performance, as the query engine may use a different plan that it would use if it needed to retrieve all the rows.
- Once the cursor is open, I can load the first page, which brings me to the other important subprocedure, 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;
- The subfile continues loading where it left off. The cursor continues fetching where it left off. This is why SQL fits expanding subfiles so well.
- We don’t have to loop through a cursor one row at a time. One fetch brings in a screen-full of data. SQLER3 tells us how many rows the fetch returned. Looping through the data structure array quickly loads the subfile.
And this is what we get:
I’d hate to try this with record-level access. Sorting and selecting are so much easier when SQL retrieves the data.
I believe — and this is only my opinion — that subfile processing is easier in some ways when using RLA than when using SQL. A good example is positioning to a certain key value and paging backward (up) through the data. Nevertheless, I advocate a transition from RLA to SQL if for no other reason than that it’s a necessary step toward an even bigger and more important transition — from green screen to GUI.
“I’d hate to try this with record-level access. Sorting and selecting are so much easier when SQL retrieves the data.”
You already have the foundation in your program Ted in that you are loading into an array. If you loaded all the result set that way you could sort it any which way you wanted before displaying it. It is also easy to position the start point etc.
Susan and I described the idea here https://www.itjungle.com/2018/09/05/guru-rpg-sorting-and-searching-a-7-2-update/