In testing the timestamp idea from interactive sql, I'm showing no duplicate timestamps (yet), but most are within 1 or 2 microseconds of the previous row. Who said sql has performance issues?
It looks like I may be best off with an integer that I have to hand-manage. The template file does have a sequence field, which may be useful, but did I mention that the whole set of commands passed to the switch includes commands from a dozen or so templates? Nothing is ever easy, and the RRN sure looked good, if we could only get SQL to respect it.
Back to the question I posed earlier: If I did not go the added column route, would I be safe in changing the file to REUSEDLT(*NO), and adding ORDER BY RRN(myfile) to the sql retrieving the rows to process?
-- Michael K
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Monday, September 12, 2011 12:47 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Mike,
True. It's down to the microsecond which wouldn't be unique if you're running on a fast computer (grin).
Is there some sort of order in the commands template table that could be brought forward with the command? (I'd guess RRN in that table too) Maybe a sequence column could be added in the template table that gets copied into the work table?
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 12:18 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
I like the idea, Paul, but many of the records inserted are selected from a "commands template" file (using an INSERT with data for the column coming from a nested "select from" statement). Not sure that a timestamp column would have significantly different timestamp values to keep it all in order. Or maybe it would? Something to test out.
-- Michael
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Monday, September 12, 2011 12:07 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Michael,
You might consider using a TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP instead of an INTEGER column. You could then delete and order based on the timestamp instead of the integer/RRN. You don't have to futz with your insert statement or use CHGPF.
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 11:48 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Thanks Michael and Robert and Joe and Paul and Kurt.
To Joe's question about what happens downstream, the table contains commands that are is used by another program where those commands are sent in sequence to another platform (telephone switch equipment). A week or so ago, I saw one instance (from over 800 orders processed) of an unexplained out-of-sequence processing. That was one where delete was not involved -- the mischief was probably due to SQL's creative ordering. Sure enough, there is no ORDER BY rrn(a). I guess I've been pretty lucky, but clearly I need to get this fixed.
Paul, the rows get deleted with an "SQL DELETE ... where rrn(a) between :x and :y ...". The single column is not unique, and even if it was, does not contain anything pertinent to its processing sequence.
Based on the statement...
The order in which rows are inserted does not guarantee the order in which they will be retrieved.
... I'm thinking I'd better modify my design to include an integer row# column.
I was looking at the RowID data type, but QTEMP will have no part of that (at v6.1, anyway).
If I did not go the added column route, would I be safe in changing the file to REUSEDLT(*NO), and adding ORDER BY to the sql retrieving the rows to process?
-- Michael K
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 10:31 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL Insert Row as last RRN
I have a very simple 1-column workfile created on the fly with embedded SQL. As I add rows to the table, some situations require that some rows need to be deleted, which works just fine. But the next row added then gets stuffed into the workfile in the position (relative-record-number-wise) of the first row vacated by the delete. I didn't anticipate this when I designed my process, so I didn't build in a sequence number column. All the access to this workfile is through SQL - no RLA in this program.
Is there a way to specify on the INSERT statement that the new row is to have an RRN greater than that of the greatest RRN already written? If not, is there a simple way to re-sequence (compress) out the deleted rows after my DELETE statement, so that the result has contiguous RRNs (thereby leaving no gaps, and a subsequent INSERT would have to go to the "end" of the workfile)?
Thanks.
Michael Koester
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
midrange.com
