More About Blocking
January 23, 2008 Hey, Ted
Don’t Disable Blocking was a good article. I would like to offer two suggestions for improvement.
First RPG ILE provides a BLOCK(*YES) file level keyword, which allows compiler-generated blocking when a random I/O operation is issued to a file processed sequentially using a READ. The most common scenario is “Keylist SETxx SomeFile” followed by READ. Note that READE is not treated as a sequential operation.
Second, I am a little disappointed that after your breakthrough OPNQRYF to SQL article using a SPECIAL device, you didn’t recommend the same approach for this situation. SQL automatically generates efficient blocking instructions for all read-only cursors. Plus SQL uses a minimum of 64K, not the paltry 8K compiler generated blocking factor. In addition, using your SPECIAL device approach, the SPECIAL program could utilize blocked FETCH support to retrieve 32,767 rows (or a maximum of 16 MB) into a host array. The SPECIAL program could then feed one record at a time back to the RPG program for each READ operation. Using this approach my current record is 140 million rows processed in under three minutes (Power5Plus, no journal or index).
By the way, I am getting a lot of mileage on the use of the SPECIAL device to bridge legacy RPG programs to SQL. With minimal lines of code change I get big benefits.
Keep up the good work.
–Daniel Cruikshank, IBM