Hi to everybody and welcome to a new post, in this case an embedded SQL tip.
If you are an ILE RPG programmer, I am sure you have used SQL in your RPG code. If not, start doing it right now. There are many advantages to using it instead of legacy opcodes: you don’t need to declare the file you are going to work with, you can update several records with a single sentence, these are just a few of them and of course, the one that titles this post, you can read multiple records with a single sentence. If you want to know how, keep reading.
Block fetch is the ability to read more than one record at a time instead of doing it one by one. Reading more than one record at a time speeds up your application because minimize data transfer.
Another use could be to work with subfiles. What if you have a subfile that displays 13 records at the same time on the screen in a page at a time loading? You could retrieve these 13 records at once with a block fetch and assign them to the subfile in a very simple way.
You may be thinking how do we know how many records have been read and what happens if the database returns fewer records than requested. Don’t worry here comes a use case.
We have an employee file and we need to process it from start to end. Instead of reading the records one by one we will read them five at a time using SQL to increase performance.
BLOCKFETCH.SQLRPGLE:
**free
ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no);
dcl-ds employee extname('EMPLOYEE') dim(5) qualified;
end-ds;
dcl-s nRows packed(2) inz(%elem(employee));
dcl-s rows packed(2);
dcl-s i int(3);
exec sql set option closqlcsr = *endmod;
exec sql
declare cEmployee cursor for
select *
from employee;
exec sql
open cEmployee;
exec sql
fetch cEmployee for :nRows rows into :employee;
dow (sqlcode = 0);
exec sql GET DIAGNOSTICS :rows = ROW_COUNT;
for i = 1 to rows;
snd-msg %char(employee(i).numemp) + ' ' + employee(i).firstname + ' '
+ employee(i).lastname %target(*self:2);
endfor;
exec sql
fetch cEmployee for :nRows rows into :employee;
enddo;
exec sql
close cEmployee;
*inlr = *on;
Explanation:
Line 1: Code will be fully free.
Line 2: These are my standard control options.
Line 4: employee data structure is declared. Using the extname keyword copies all fields from the EMPLOYEE file and their definitions into the data structure. The data structure will be an array with five elements, the same number we are going to read in block and it is qualified.
Lines 7: Variable to hod how many rows must be read at a time.
Line 8: Variable to hold how many rows have been read.
Line 9: Variable that will be used with a for loop.
Line 11: The cursor is closed when the module ends.
Lines 13-16: A cursor is declared to work with the result set.
Lines 18-19: The cursor is opened so the select sentence is run.
Line 21-22: With this sentence, the system will attempt to retrieve as many rows as requested with the nRows variable and then place them into the employee data structure array.
Lines 24: The program checks the value of sqlcode. A value of zero means success so while this is true the result set can be processed.
Line 25: But the previous fetch could have read fewer rows than requested and GET DIAGNOSTICS comes to help. We can obtain a lot of information with GET DIAGNOSTICS but the one that helps in this case is ROW_COUNT. The value it returns is assigned to the rows variable so that we have the exact number of rows returned by the last SQL sentence.
Line 27: For loop to process the result set using the number of rows as limit value.
Lines 27-28: In this example processing a row is write the value of some fields to the job log and to the screen message line.
Lines 30-31: The program get the next block of rows.
Lines 34-35: Having finished with the cursor, the program closes it explicitly.
Once the program is run we get this list of employees in the job log. As there are 13 rows the block fetch has run 3 times and the last one has retrieved only 3 rows but as the for loop has the limit value equal to the value returned by GET DIAGNOSTICS no extra iteration has been done.
I hope you liked the post. Leave a comment if you want and stay tuned for upcoming articles.
7 Responses
I incorporated this example into my blog https://rpgfreeibm.blogspot.com/2024/01/sqlcode-values-dear-readers-unleash.html because I find it highly insightful. I’m open to any modifications if needed. Special thanks to the authors of the referenced page for providing such a valuable resource.
Thank you Aldo!
Hi Antonio,
How about declaring nRows as dim( %elem( employee ));
to get rid of the hardcoded value of 5.
All the best,
Stefan
Hi Stefan,
very good advice. I change it.
Thank you!
If you’re displaying all of the records, I don’t see the benefit of retrieving 5 rows at a time. Why not 100?
For a more practical use, this is good for a subfile when displaying one page at a time.. If the subfile page holds 15 employees, you retrieve “for 15 rows” and exfmt the subfile.
For the display file, it is expected to display More or Bottom depending if there is more data or not. How would you handle this?
Hi Glenn,
retrieve only 5 rows at a time is just an example. Of course in a “real” program the number can be greater but for educational purposes I retrieve only 5. There are only 13 records in my database file and I wanted to show how GET DIAGNOSTICS works retrieving 5, 5 and 3 records. But even retrieving only 5 rows at a time is more efficient than doing it one at a time. Think of a fetch as a request to the database to send you a message containing lines. Every time you run a fetch, the database responds with a message with the lines you requested. If you retrieve 5 rows one by one, the database sends you 5 messages, one per line, but if you retrieve 5 lines at a time it only sends one, so you save 4 messages, which leads to better performance . After all, it is about minimizing input-output operations.
Regarding to your question you can read one more row and check EOF to set SFLEND indicator or not and reposition the cursor in case EOF is false. I’m preparing another post about SCROLL CURSORS and I think I can use a subfile and try this approach. So stay tuned.