AS/400 Error: RRN() does not start from 1, but starts from number of rows deleted + 1 db2, 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
thnx a lot.
elai
Software/Hardware used:
as400/DB2,V5R4M0
ASKED:
November 19, 2010 2:37 PM
UPDATED:
November 23, 2010 8:54 PM
RELATED QUESTIONS
- Is there a limit to the relative record number(RRN) for physical file on system i , we are on V5R4 version of OS400
- ISeries Navigator
- now i m using V4R3 on AS400 server and application is COBOL and DB2,so can i migrate the all data & application configuration on V5R4 on AS400 525serv
- Improve performance on Insert rows into DB2 400 table from SQL server
- performance tunning
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.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
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
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