SQL RRN Processing – by Key?

I'm putting together a job that needs to process a large masterfile in a very short period of time. To do this, I'm (sorta) cloning an existing SQLRPGLE application that runs multiple concurrent copies of a program...each processing a range of the file by RRN. The number of copies is dynamic, set by an input parm. The total number of records in the masterfile is divided by the input parm to determine the beginning and ending RRN for each copy of the program. The problem I have is when I want to filter the masterfile by some criteria. The only way I can think of is to use from/to "RRN(masterfile) = :hostVar" criteria on a Select query, and drive the program based on the resulting SQL cursor. This will work (sort of) but since the filter critera won't be evenly spread out in the file, I'm going to end up with different copies of the program processing 250K, 450K and 100K records (for example) when I was trying to balance out the load. Is there some way that I can directly position to the nth record of an SQL result set without reading and counting until I get there? TIA...Chuck

Answer Wiki

Thanks. We'll let you know when a new response is added.

select * from table where rrn(table) between 2500 and 5000

I’m not positive how efficient that is, or if it is/can be indexed, so, finding some key to use may be more efficient, but, I have used rrn() quite a bit in the past. when I needed to limit results.

If you’re using different RRN() positions, but want to use the same program, pass them in as parameters, and then you can choose the range when you submit the job.

unluckily I have pretty limited exposure to RPG/SQLRPG, so, I’m probably just saying things you already know/thought of… but, I figured if nothing else, I might get some gears turning.

hope that helps.


As you already know, RRN() isn’t appropriate. You can ignore any suggestion to use it on your master file.

Since you have selection criteria as well as a key, you might simply create a new table of keys as SELECT keycol FROM mytable WHERE selectcriteria = ‘x’ (populate a secondary table with the keys from all rows that meet your selection criteria.)

The populating of the new table should be relatively fast. Now consider that keycol could be an ORDER BY criteria too.

Partitioning that new file into five parts (or four, six or whatever) gives you direct sets of unique keys that can be processed by five instances of your program. You can do the partitioning in any way that you choose — by RRN(), by copying sections into work files, by anything you want.


Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ChuckM
    Kevin...thanks for your response. I think this is more of an SQL issue than an RPG issue. I've already tried your suggestion, but as I said, using the RRN of the file doesn't allow me to balance the number of records between the different programs...when I'm filtering on some other criteria in the records. What I'd like to find is a way to access the recordset RRN instead of the RRN within the underlying file. I'm probably not explaining well...maybe an example will help. Here's a sample table: RRN CustNo Act 1 1122 Y 2 2233 Y 3 2211 N 4 3322 N 5 3333 Y 6 4455 N 7 5544 N 8 4433 Y 9 5555 N 10 2222 N select * from TABLE where ACT = 'Y' and rrn(TABLE) between 1 and 5 ... will return 3 rows...between 6 and 10 will return 1 row. Is there a way to rewrite this query so I get 2 rows in each recordset?
    0 pointsBadges:
    I tested on my system after sending, and realized you'll deal with a lot of extra processing using RRN (atleast going by the file I tested) our system takes about 30 seconds to scan 500k records for RRN(), so if you're looking at a huge file, that could be a big cost in time by itself. If you're at a high enough release level, you may be able to use something like this, and avoid RRN() altogether. select * from table where act='Y' and CustNo > *LASTQUERYMAX* order by CustNo Asc Fetch first 10 rows only The biggest problem I can think of here is that you won't know what the last customer number you'll rertrieve will be, but you'd have the same problem with RRN() With some tweaking, and subselects, you should be able to get use an indexed field, run several queries in an initial program to set up your limits, and then submit your jobs with proper parameters all using indexed fields. There's another more complicated way of doing things using a correlated subquery. But, in my experience, those are VERY expensive in processing power. here's an example: select * from table a where a.act='Y' and 10 > (select count(*) from table b where b.act='Y' and a.custno
    0 pointsBadges:
  • ChuckM
    I finally found what I need in the SQL row_number() function. Unfortunately, our current iSeries OS release doesn't support this function yet (we're currently at 5.3...support for OLAP functions is in 5.4). But I think I can do what I need to do with a relative fetch statement. I just need to make sure I know when to stop. Thanks again for taking the time to look at this...Chuck
    0 pointsBadges:
  • Ashnirody
    Here?s a way to do this load balancing between two or more instances of a program. Create a list of RRN?s or keys and split them between two or more data queues. Then let each instance of the program read one of the data queues and process that set of records.
    100 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: