Re: Force SQL Statement In RPG To Insert Records To Next RRN?

Hi Robert,

Just out of curiosity, is there a situation where an application *should* re-use a deleted record?  If not, then CHGPF myFile REUSEDLT(*NO) would solve your problem. If the problem is that you do not have authority to do the CHGPF, I'd suggest talking to the person who does.

Re-reading your original post, you state that "I have an application that will allow a user to delete and then, potentially, recover the deleted records."  If there is any scenario where some application does re-use a deleted record, your application will not be able to recover a re-used deleted record.  For that ability to work reliably, you really should be making all efforts to change the file to REUSEDLT(*NO).

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 5/28/2020 6:16 PM, Vernon Hamberg wrote:

Robert

I believe that there are C functions for writing to an RRN - not in RPG - no guarantee, though, but if there is, you could write an SQL UDTF to do this, maybe.

But this architecture - wow! Does the app also initialize a certain number of records? Not sure I'm saying anything that is helpful, of course!

Vern

On 5/28/2020 4:46 PM, Robert Wenzel wrote:

I have DDS Physical files that are multi-member files and are
declared REUSEDLT(*YES).  Is there a keyword or phrase that will force the
next insert to the next RRN without reusing an empty/deleted record?

Why I ask ; I need to have an SQL Insert statement to insert the next
record to the end of the file (not a reused RRN).  The files do not have
unique keys.  I cannot modify them.  I have an application that will allow
a user to delete and then, potentially, recover the deleted records.  I
catalogue the RRN of the deleted record in an audit file with the original
RRN (from the original member) and the new RRN (of the recovery member).
After recovery I catalogue the RRN of the record that was inserted into the
original member from the recovery member.  The problem occurs if the
user deletes, for example, RRN #1 then deletes RRN #2 and then recovers RRN
#2.  DB2 places the "new" (recovered) record in the RRN #1 location of the
PF.   My catalogue now has duplicate RRNs of records that were deleted from
RRN position 1.   This should not be the case.

I know I can change the attributes of the PF, even temporarily, but many
batch jobs use these files throughout the day.  I would prefer an SQL
statement directive.

Thank you all.