• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Use SQL to Easily Update Multi-Key Files

    Updating Through an SQL Cursor

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    LaserVault

    Integrate Virtual Tape to Automate Your Backups And Strengthen Your Ability To Recover From Cyber Attacks And Disasters

    With most IT departments stretched thin, finding something that can quickly free up IT time is definitely a bonus. That’s why it’s important to stop and take a look at integrating virtual tape into your backup and recovery. Virtual tape is one of those technologies where once you have it, you’ll wonder why you didn’t do it sooner. See a demo and get a $50 gift card.

    But what is it about using virtual tape that makes it so worthwhile? Why is it that so many IBM i shops are already using or considering using virtual tape for all or part of their backup and recovery systems?

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    “We needed to find a replacement that would lower the maintenance cost and reduce complexity of our backup and recovery functions without a major disruption to our operations.” David Fray, Director of Enterprise Systems, ABC Financial

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). With ViTL you can:

    • Replace physical tape and tape libraries and eliminate associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SoftLanding Systems:  TurnOver Change Management for a more productive WDSc environment
    Bug Busters Software Engineering:  Quality software solutions for the iSeries since 1988
    COMMON:  Join us at the Spring 2006 conference, March 26-30, in Minneapolis, Minnesota

    Bytware Brings StandGuard AntiVirus to p5 Unix Servers OS/400 Servers Over Time: iSeries to i5 to System i

    Leave a Reply Cancel reply

Volume 6, Number 8 -- February 22, 2006
THIS ISSUE SPONSORED BY:

T.L. Ashford
WorksRight Software
Profound Logic Software

Table of Contents

  • V5R4 RPG Enhancements
  • Readers Write about the INCR and RRN Functions
  • Admin Alert: Moving Libraries Between i5/OS Partitions, Part 1

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.