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?

Answer Wiki

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.

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

Processing your response...

Discuss This Question: 4 &nbspReplies

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

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

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

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

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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