Insert a record, immediately delete this record and than again insert a new record.
last rrn assigned in new record. I am hoping this rrn no should be increment. can we increment this in db2 as400?
2 Answers
Your file is set to reuse deleted records. When a record is deleted in DB2 for i, it actually remains in the file and is just marked deleted. If the file is defined with REUSEDLT(*YES), then it will reallocate these deleted records if possible when new records are added to the file. However if REUSEDLT(*NO) is set, then inserts will always create a new record, and therefore a new RRN. Just as a side note, one of the differences between DDS and DDL defined files is that when you create a physical file by compiling DDS source, the default is REUSEDLT(*NO). When you create a table using SQL CREATE TABLE, the default is REUSEDLT(*YES).
You can change this file using CHGPF FILE(yourfile) REUSEDLT(*NO).
Be careful though. Deleted records still take up the same amount of space as a non-deleted records, and in a highly volatile table, the size of such a table can grow quickly. You may need to reorganize the table regularly so that you don't have one with large numbers of deleted records.
RRNs are handled by internally by the database. As @jmarkmurphy pointed out, there are limited possibilities to influence the allocation of numbers, and @Charles rightfully says, it's a brittle thing to rely on RRNs.
Summarizing: If you have database workflow relying on RRNs, it's already broken. Never rely on RRNs!
Better: Add an identity field, a timestamp, or a numeric ID field, and use this to reliably refer to a certain record. It is part of the record and not part of the internal organization of the database engine itself and thus reliable.
Related