RE: SQL Insert Row as last RRN

Bill and Chuck--
Good things to know for sure. Thanks.

My current issue is pretty linear, with no concurrent hits to the table. It's a batch process using a QTEMP copy, and I got blind-sided when loading rows to the table, expecting the new row to get a larger-than-last RRN. Not so after deleting and reusing the deleted row position.

After I get all the "commands" loaded into the table in the appropriate sequence, the driver program calls another program that reads the "command" from each row, in the expected sequence, and sends the command to the external system. Once all that occurs, control returns to the driver program to process verification routines, etc. The table itself is not used elsewhere, and is dropped after that set of commands has been sent.

Eric, my testing with interactive SQL at v6.1 shows that the Identity column performs as advertised in a qtemp table -- RowID does not. Perhaps that's the one that got you?

--Michael

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, September 12, 2011 3:45 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL Insert Row as last RRN

On 12-Sep-2011 12:07 , Morgan, Paul wrote:

IDENTITY column requires maintaining a sequence in the database with
some minor performance issues especially with multiple processes
using the same table.

If ["multiple processes" suggests] concurrency is already an issue
for the OP, then the RRN implementation already seems flawed, even if
reuse of deleted rows is prevented.? The DELETE in an earlier post was
using a RANGE on RRN() values, so if competing insert requests can
intersperse... Ouch!

From what I have heard about using SQL with RRN() ordering, I think
there would be a performance benefit for using an identity instead.
Using identity eliminates any requirement to turn off reuse of deleted
rows and more importantly eliminates entirely any use of the RRN().
Using RRN() vitiates the choice of the SQL, forcing the SQL to function
like just another RLA method when many of the benefits from the SQL are
derived from not being dependent on RRN().

The column sequence could require a reset. It will eventually wrap
around to a lower sequence and throw off your ordering when it does
wrap. It's a similar problem you are having with the RRN.

The AS IDENTITY clause can specify NO CYCLE to prevent wrapping,
except when wrapping is the effect of a user-requested reset to the
identity column [start with] attributes. I do not know what sqlstate
identifies that an insert exceeded the MINVALUE or MAXVALUE.

Regards, Chuck