Readers Write about the INCR and RRN Functions

Published: February 22, 2006

Hey, Ted:

I liked your article, Numbering Rows in an SQL Result Set, but I think your INCR function may be overkill. I'm able to accomplish the same thing with the RRN function. Would you please elaborate on the difference between the two methods?

--Rafael

Rafael, you need to take a look at the results of the slightly modified SQL query you sent me. Here's the query.

SELECT INCR(9999), (RRN(qcustcdt) + 9999),
       LSTNAM, INIT, CUSNUM               
  FROM qiws/qcustcdt                      
 ORDER BY LSTNAM, INIT                    

And here are the results.

INCR    RRN     LSTNAM    INIT  CUSNUM
10000   10011   Abraham   M T   583990
10001   10006   Alison    J S   846283
10002   10007   Doe       J W   475938
10003   10000   Henning   G K   938472
10004   10003   Johnson   J A   938485
10005   10001   Jones     B D   839283
10006   10010   Lee       F L   192837
10007   10005   Stevens   K L   389572
10008   10008   Thomas    A N   693829
10009   10004   Tyron     W E   397267
10010   10002   Vine      S S   392859
10011   10009   Williams  E D   593029

Notice that first two columns are not identical, so RRN and INCR do not do the same thing. The INCR function numbers the rows of the result set. The RRN function returns the relative record number, that is, the order in which the record is stored in the physical file.

There's a bit more I can say about these functions. Concerning INCR, here are the comments of one faithful reader.

Hey, Ted:

I really like the INCR( ) function. I've been looking for a way to get line numbers on an SQL listing. One thing I have noticed is that if you page down at the end of the listing the line number keeps incrementing. Also, if there are many pages and you page back up after the first page the line numbers carry on incrementing. Nevertheless it is still a great Christmas present!

--Phil

You're correct, Phil. Every time INCR is called, it returns the next number. It's a feature, not a bug. ;) I wrote that function in order to load sequential integers into a numeric field. If I ever figure out a way around the "features" you mention, I'll publish it. Maybe some shrewd reader will write in and enlighten the both of us.

As for RRN, several people wrote in response to Chuck's tip, Use SQL to Easily Update Multi-Key Files. The following is representative of the sentiments of several readers.

Hey, Ted:

The reason that your dear reader has never seen his "easy" solution to multi-key update published is that it performs very poorly. The RRN function must be evaluated for every record in the file, even those after the record that satisfies the WHERE clause. Apparently IBM has no plans to improve the situation (or at least didn't when we asked them a while ago).

--Kevin

I contacted IBM to find out if the RRN function still causes a table scan and received some good news from Kent Milligan. There are two co-existing query engines in DB2/400. The old one is now known as the Classic Query Engine (CQE). It interprets non-SQL queries. The new one is called the SQL Query Engine (SQE), and, as its name implies, it interprets SQL queries. However, sometimes the SQE decides to pass the query off to the CQE. There is no way for the user (that's you) to tell the system which query engine to use.

The V5R3 SQE cannot access a row directly by relative record number, but it can get close. The V5R4 SQE gets even closer. The result is that RRN does not cause a table scan in V5R3 and above if the SQE handles the query.

If you'd like to read more about the SQE, see the IBM InfoCenter.

Another faithful reader brought up another good point.

Hey, Ted:

It may be a useful reminder to mention that had Chuck used a cursor in his read-before-update operation, he could then have executed an update "where current of (cursor)," without the need for retrieving the relative record number.

--Jim

True, Jim. Updating through a cursor is a good technique. I wrote about it back in June of 2004. But not updating through a cursor also has its uses. For example, you can make one program read the data and another program update it.

Thanks very much to everyone who wrote. Your questions and comments are the main reason this newsletter has been so successful.

--Ted

RELATED STORIES

Numbering Rows in an SQL Result Set

Use SQL to Easily Update Multi-Key Files

Updating Through an SQL Cursor