Re: SQL Insert Row as last RRN

On 12-Sep-2011 13:10 , Koester, Michael wrote:

My current issue is pretty linear, with no concurrent hits to the
table.

Linearly\sequentially invoked SQL does not preclude parallel implementations for data access within any one SQL statement. See the QQRYDEGREE system value [and DEGREE on CHGQRYA]. Also see next inline comment.

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.

While the reuse-deleted [REUSEDLT attribute] may impact the order of rows inserted, so might the [lack of an] ORDER BY on a SELECT. If the subselect for the INSERT being used to populate the "temporary" TABLE precludes ORDER BY per syntax limitations [as I recall in older releases], or that clause is allowed but omitted, then are the rows really getting inserted in the "appropriate sequence" by design or perhaps only coincidentally? I wonder if there might be a [potential] problem in each portion of the "load" phase, but that all attention has been directed only to the insert portion of the load versus the select portion of the load.?

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.

FWiW: Except as compared with a truly temporary object [which a *FILE in QTEMP is not], the best performance may be from creating a permanent file for the application rather than creating and dropping each time; i.e. create an object once, and manipulate only data repeatedly instead of including the create\drop activity repeatedly. That could require additional complexity in the application, e.g. for its selection for deletion, perhaps using a key value of GENERATE_UNIQUE or something else sufficiently unique like job name and date to correlate the rows to any one process.

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?

An Identity column would normally also be defined as the primary key for the TABLE being created. Requesting a primary or unique key constraint would also preclude use of QTEMP since constraints [like the ROWID data type] are not allowed in QTEMP.

Regards, Chuck