× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Michael,

It looks like an identity column is the right solution.

I'm curious why these jobs are single threaded. The switch can't handle multiple connections? Is there a conflict between the batch jobs?

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 4:30 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Paul, unless adding the identity column to the table and the support of same slows the whole process down by multiple seconds, there will be no perceptible impact. Each order runs as its own batch job in a single-thread queue. There rarely more than one or two initiated in any given minute, and the each process is done - switch provisioned, database updated, order stage changed, order memos generated - in 3 to 5 seconds.
--Michael

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Monday, September 12, 2011 4:11 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Michael,

Then wrapping the sequence isn't a problem.

How frequent are the service orders? Could there be a bottleneck as you create and delete work tables with sequences?

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 3:18 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Hey Paul,
It is indeed. The workfile is really short-lived, so identity column wrapping will not be an issue. It gets dropped at the end of the service order being provisioned, so it should rarely get more than 100 rows, and is gone in about 4 seconds; rebuilt fresh for the next order.
Thanks.
-- Michael

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Monday, September 12, 2011 3:07 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Michael,

IDENTITY column requires maintaining a sequence in the database with some minor performance issues especially with multiple processes using the same table. 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.

Is this your interface to the CS 1500? Likely you don't require 24/7 so having to shut down this single process to occasionally reset is probably not a problem.

Hope this was helpful,
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 2:23 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Kevin, I'd not played with the IDENTITY Column before. I just ran some tests and found it to be a pretty good solution. It didn't seem to re-use values from rows I'd just deleted either. Unless someone knows of any shortcomings of that, I think I'll add that to my CREATE TABLE, then do the CHGPF trick to so new rows get added to the bottom, and ensure that all my selects are ORDERED BY the identity column. Perhaps a tiny bit more effort than I initially planned, but not too much, and for as critical as sequence is in this case, it gives me more confidence than RRN.

Thanks for that.

And thanks to everyone else who helped me be not as dumb as I was earlier today :)
-- Michael K

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kevin Bucknum
Sent: Monday, September 12, 2011 1:47 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

Instead of a timestamp could you use a IDENTITY column?

Kevin B

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 12:05 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN

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





Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.