Readers Write about the INCR and RRN Functions
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.
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.
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.
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 |
