Thanks Chuck and Birgitta, for the additional considerations.
I made sure to modify my selects to use "order by RowSequence" (RowSequence being the Identity column) in all the places where the sequence matters -- and especially the program that feeds the "commands" to the external platform.
I understand that creating and dropping tables is a performance hit, but for this purpose it has not been significant enough to warrant a permanent table (and the hoops I need to jump through to get it added to the Production environment).
If I find any issues in the integration tests, I'll reconsider.
Thanks again to all who have assisted here.
This list is truly a tremendous resource!
-- Michael
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, September 13, 2011 9:41 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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
midrange.com
