CODEWITHSUNDEEP
db2ibm-midrange
suman biswas
suman biswas

Reputation: 41

RRN repeated in delete case

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?

Upvotes: 0

Views: 376

Answers (2)

PoC
PoC

Reputation: 591

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.

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11483

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.

Upvotes: 2

Related Questions