IBMi (AS400) fans only - Fully Free RPG: Single Page Subfile SQLRPGLE Example Program
|
But in case of heavy processing, loading the entire file can take a long time. So, in some cases is better to load one page at a time. I received several comments to this statement, so I thought I'd clarify my opinion with two examples. Scenario 1 small file: The subfilesize is 20. You have to process a file of 5000 records, so you may think this is a ideal scenario for a standard subfile processing, bat you want to filter only specific records, for example records with a flag=’X’ and creation date between two specific dates. The total number of this records is, for example, 280 so you get the first page after processing the entire file. With a single page process, you get the first page after finding the first 20 records that match your filters. Faster then standard subfile processing. Scenario 2 big file: The subfilesize is 20. You have to process a file of 50 million records, bat obviously you want to filter only specific records, for example records with a flag=’X’ and creation date between two specific dates. The total number of this records is, for example 280, so you get the first page after processing the first 9999 records that match your filters (You scanned 50 million records before an answer!). With a single page process, you get the first page after finding the first 20 records records that match your filters, and you are able to process the file to the end. Faster then standard subfile processing. So, in some cases is better to load one page at a time. That's how. First of all look at this post and compile and populate these files to use this example. Then copy e compile the following sources. 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.
Great job, thanks for clearing this out!
ReplyDeleteI like it!!!
ReplyDeleteHi Nick, I just wanted to say thank you so much for providing me with that RPG source code. It was really helpful in getting the example I needed. I appreciate your willingness to help out and share your expertise. Thanks again!
Delete