Re: SQL REUSEDLT

For any INSERT the database must locate where to place the row atomically, whether it be via addressing the end of the dataspace using information stored in the dataspace about where that location is, or finding that location via the reuse information stored in the reuse segment. In both cases the row must be added in a manner that deals with any concurrency. The performance with reuse is impacted when the data segment into which the row is placed must be brought into memory, which is less likely with data always added at the end. With reuse enabled the data segment into which the row data is placed is variable\random [i.e. not sequential], it can be faster to enable more concurrency [concurrent write support] than when all rows must be placed concurrently at the end. Thus a single threaded batch insert will likely do better to have pre-allocated storage or very large storage extents defined, with data coming in arrival sequence.

It is the CHGPF SRCFILE(specified) which definitely impacts the SQL-ness of the specified file; TABLE designation is lost. The specified file is then created from the specified DDS using CRTPF. A DDS PF with columns modified by SQL ALTER TABLE may pick up some SQL-ness for certain column attributes. The request to CHGPF SRCFILE(*NONE) just changes the attributes common to physical files; some attributes are protected for a SQL TABLE, for example MAXMBRS() must remain one.

Regards, Chuck

Kurt Anderson wrote:

Jorge - I think when you do a CHGPF on a SQL created file it is
no longer treated as SQL created. Not 100% on that, but I
thought I came across that somewhere.

Rob - good questions. I was asking because I wanted to know what
my options are. There is concern in the shop about reusing
deleted records. While I'm not sure of their reasons, there is a
performance consideration. Reusing deleted records results in a
"nominal" performance hit, but nominal adds up when batch
processing millions of records (in this case it's doubtful that
there'd be millions of deleted records - although I don't know if
the nominal hit is in regard to hitting the Deleted Record Map
before performing the write or in the reuse of the deleted record
itself).

<<SNIP>>

Rob wrote:

Why would you not want it to? Are you relying upon arrival
sequence? Can you bypass that by adding a "sequence" column to
the table? If you're concerned about sequence rollover, log a
date along with the sequence. That should work unless you plan
on rolling over your sequence in the same day.