Single Page Subfile SQLRPGLE Example Program
#IBMiSample – Improve Subfile Performance by Loading One Page at a Time
The traditional subfile can display up to 9,999 rows and works perfectly in many scenarios.
However, when dealing with large datasets or heavy filtering, loading every matching record before showing the first page can drastically slow down the user experience.
Based on comments I received from IBM i developers, here are two practical cases that clearly explain why loading only one page at a time is often a better solution.
Scenario 1 — Small File
- Subfile size: 20 rows
- Total records: 5,000
- Filtered records: 280
(for example:
FLAG = 'X'and creation date in a selected range)
With a standard subfile, the program displays the first page only after scanning all 5,000 records.
With a single-page load, the program stops as soon as it finds the first 20 matching records and immediately displays the first screen. This is significantly faster and improves usability.
Scenario 2 — Very Large File
- Subfile size: 20 rows
- Total records: 50,000,000
- Filtered records: 280
A standard subfile loads the first page only after reading 9,999 matching records. If your file contains 50 million rows, this means scanning the entire dataset before the screen appears.
With single-page loading, the program displays the first 20 matching rows immediately and continues processing only if needed (for page-down). This approach is dramatically more efficient and reduces waiting time for the user.
Conclusion
When working with large files or dynamic filters, it is often much better to load one page at a time instead of preloading the entire subfile. This technique boosts performance and ensures a much faster first-screen response.
How to Test This Sample
Before compiling the source code, follow this post and install the required sample files:
Files for my sample: CLANA00F (Customers)
Then copy, paste, and compile the source members provided below.
Sources
Below you will find the sample DSPF and RPGLE code used for the demonstration.
Display file: **FREE // Description... Template Single Page Subfile SQLRPGLE Program // File Name..... OT04SF.SQLRPGLE // Author........ Aldo SUCCI from idea of Nick Litten // (https://www.nicklitten.com) // https://www.nicklitten.com/example-of-an-ibm-i-rpg-single-page-subfile // Date.......... Jan 21th 2019 // // Compile Instructions: // CRTSQLRPGI OBJ(YourLib/OT04SF) SRCFILE(YourLib/QRPGLESRC) SRCMBR(OT04SF) // Run instruction:// OBJTYPE(*PGM) // // call OT04SF ctl-opt debug option(*nodebugio:*srcstmt) datfmt(*iso-) timfmt(*iso.) indent('| ') truncnbr(*yes) expropts(*resdecpos) copyright('| TEMPLATE Single Page Subfile SQLRPGLE Program') dftactgrp(*NO) ; dcl-f CLANA01L keyed usage (*input); // Customer File dcl-f OT04SFV workstn sfile(SFL01:rrn) indDs(dspf); // Displayfile dcl-s p_Indicators pointer inz(%addr(*in)); dcl-ds dspf qualified based(p_Indicators); help ind pos(01); exit ind pos(03); refresh ind pos(05); previous ind pos(12); pageup ind pos(25); pagedown ind pos(26); sflclr ind pos(32); sfldsp ind pos(30); sflctl ind pos(31); sflEnd ind pos(35); end-ds; dcl-s Position like(OTCOR0) ; dcl-s IndexPag like(OTCOR0) ; dcl-s PaginaST like(OTCOR0) dim(10000) ; dcl-s PaginaEN like(OTCOR0) dim(10000) ; dcl-s EndOfData char(1); dcl-ds dsp_fields extname('OTORD00F') end-ds; dcl-s rrn like(sflrrn); dcl-s pagesize zoned(2) inz(19); // ********************************************************************* // PGM start // ********************************************************************* exec sql set option commit = *none, closqlcsr = *endmod; // initially let's force a refresh to load first page dspf.refresh = *on; dou dspf.exit or dspf.previous; if dspf.refresh; // Pointer of current page displayed IndexPag = 1; // Page number PaginaST = 0; // First record key of x Page PaginaEN = 9999999; // Last record key of x Page clearSubfile(); setPage(); loadSubfile(); elseif dspf.pageUp; setPage(); // Return to the previous page pageUp(); elseif dspf.pagedown; pageDown(); // Go to next page endif; showSubfile(); // show screen enddo; *inlr = '1'; return; // ********************************************************************* // Clear Subfile procedure... // ********************************************************************* dcl-proc clearSubfile; dspf.sflEnd = *off; dspf.sflclr = *on; write ctl01; dspf.sflclr = *off; end-proc; // ********************************************************************* // Build Subfile procedure... // ********************************************************************* dcl-proc loadSubfile; dspf.sflEnd = *off; rrn = 0; exec sql fetch next from mycursor into :dsp_fields; // Start pointer of current page if sqlcode < 0 or sqlcode >= 100; OTCOR0 = 0; endif; PaginaST(IndexPag)= OTCOR0; dow sqlcode >= 0 and sqlcode < 100; GetCustomer(); // Get Customer name rrn += 1; write SFL01; if rrn = pagesize; leave; endif; // get next record exec sql fetch next from mycursor into :dsp_fields; enddo; // End pointer of current page PaginaEN(IndexPag)= OTCOR0; // Chiave x prossimo SETLL+1 // if we didnt load a full page then set END OF SUBFILE if rrn < pagesize; dspf.sflEnd = *on; endif; end-proc; // ********************************************************************* // Display Subfile procedure... // ********************************************************************* dcl-proc showSubfile; if rrn < pagesize; errormsg = 'Start of data'; endif; if rrn > 0; dspf.sfldsp = *on; else; dspf.sfldsp = *off; errormsg = 'No more data from OTORD00F!'; endif; dspf.sflctl = *on; write cmd01; exfmt ctl01; clear ERRORMSG; end-proc; // ********************************************************************* // Get Customer... // ********************************************************************* dcl-proc GetCustomer; chain (OTCCL0) CLANA; if %found(CLANA01L); else; clear CLNOM0; endif; end-proc; // ********************************************************************* // Declare cursor procedure... // ********************************************************************* dcl-proc setPage; position = 0; if (IndexPag>1); position = PaginaEN(IndexPag-1)+1; if (dspf.pageup); position = PaginaST(IndexPag-1); endif; endif; exec sql close mycursor; exec sql declare mycursor scroll cursor for select * from OTORD00F where OTCOR0 >= :position order by OTCOR0 limit 19 for read only; exec sql open mycursor; end-proc; // ********************************************************************* // Page up procedure... // ********************************************************************* dcl-proc pageUp; // set start cursor RRN at first row of previous page clear position; EndofData = ' '; // if rrn = pagesize; if IndexPag > 1; IndexPag = IndexPag - 1; endif; // else; // clear position; // endif; // Build the subfile starting at *position* clearSubfile(); loadSubfile(); // If the cursor already in the top the list if rrn <= pagesize; // errormsg = 'Start of customer list'; endif; if IndexPag <= 1; errormsg = 'Start of customer list'; endif; end-proc; // ********************************************************************* // Page down procedure... // ********************************************************************* dcl-proc pageDown; if (EndOfData='Y'); else; IndexPag = IndexPag + 1; endif; // Build the subfile starting at *position* clearSubfile(); setpage(); loadSubfile(); // If more to read then show sflend EndofData = ' '; if rrn < pagesize; errormsg = 'You have reached the bottom of the customer list.'; EndofData = 'Y'; endif; end-proc; |
I appreciate all the comments made on this blog.
Comments
Post a Comment