AS/400 Error: RRN() does not start from 1, but starts from number of rows deleted + 1 db2, v5r4m0

45 pts.
Tags:
AS/400
AS/400 DB2
RRN
V5R4M0
i'm trying to populate a field in A table with the row number using rrn(tablename). the first time if i insert 4 rows then the id is 1,2,3,4 as required. if i delete the rows and try the insert again the rrn() does not return the same so 1,2,3,4 but returns 5,6,7,8 (that is the nr of rows deleted + 1). how can i fix this?

 

thnx a lot.

 

elai



Software/Hardware used:
as400/DB2,V5R4M0

Answer Wiki

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

This is happening because when you delete the record, it is still physically there, but the pointer is gone. If you did a DSPFD, you would see it has 0 records and 4 deleted records.
If you did a RGZPFM command, it would show 0 records and 0 deleted records and your process would return 1,2,3,4 as you desire.
But the real question is what exactly are you trying to do. If you build the file with SQL, you can have a field that would automatically increment by 1 whenever a record is added.
This is some background informaiton, if you give us more specifics on your intent, we can give you more assistance.

========================================================

RRN() does not return a row-number. It is the “relative record number of a row”. If the first row is in relative record number 5, then RRN() will return (5), not (1).

ROW_NUMBER (or ROWNUMBER) is a very different value. It is available in V5R4 and later in OLAP queries. You might use something like:<pre>
SELECT row_number() over () as RowNum FROM mytable</pre>
The OVER () clause specifies ordering for the ROW_NUMBER() determination.

Tom

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Note that you can use OVER () by itself, i.e., no parameters, if you are ordering by arrival sequence. That is, the query in the 'Answer' should run exactly as written for a table named MYTABLE. Tom
    125,585 pointsBadges:
    report
  • Elai
    thank you for your answers. the problem is that all the records in this table are added and deleted daily, and the field i want to populate should always begin from 1, so the auto-increment field does not solve my problem. i have used rrn() in the past and have never had this problem???? i´m using cl programming. if i run RGZPFM i fix the problem or i should do something else? should i run this after the delete query and before the insert ? thnx elai
    45 pointsBadges:
    report
  • TomLiotta
    i have used rrn() in the past and have never had this problem? RRN() cannot be guaranteed to return row numbers beginning with 1, as you are seeing. If you look at the file with DSPFD and use TYPE(*ATR), you should able to scroll down and see the 'Reuse deleted records' (REUSEDLT) attribute. It should be near the bottom of the second screen or top of the third. This file appears to have REUSEDLT(*NO). If you made RRN() work in the past, those files probably had REUSEDLT(*YES). If not, then they didn't have records that were deleted -- they had members that either were cleared with CLRPFM or had been reorganized with RGZPFM. (When all rows are deleted with SQL DELETE without a WHERE clause, SQL can use CLRPFM.) You can use CHGPFM REUSEDLT(*YES) against your current file to change it to use the deleted record spaces over again. Why does it matter if RRN() returns a 1 or a 5 for the first row? The only thing RRN() is intended to do is tell you what the order is. As long as record 5 comes before 6, and 6 comes before 7, etc., it shouldn't matter if the counting starts at 1 or at 5. The order is still the same. Relying on physical record order instead of logical order is likely to introduce problems at unexpected times. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Yes, if you want to use the RGZPFM it would be between the delete query and the call to the RPG program. Perhaps it's worked ikn the past because your jobs created a duplicate work file in QTEMP for the run. Since it was a new file the RRN() would be 0. But if you know that you have a cleared file and you want to start at 1 why use RRN()? Phil
    50,415 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following