AW: Sequential numbering service program +ILE architecture

1. the CYCLE keyword determines what happens when the end is reached
2. yes. ALTER SEQUENCE xyz RESTART WITH z

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jerry Adams
Gesendet: Dienstag, 23. Juni 2009 13:49
An: RPG programming on the IBM i / System i
Betreff: RE: Sequential numbering service program +ILE architecture

Wow, Birgitta, you are a treasure. I had never heard of a Sequence object. I'll look up more information about it later, but two quick questions:

1. What happens when the MaxValue is reached?
2. Can the current value be reset, sorta like CHGDTAARA?

Thanks.

Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, June 22, 2009 11:28 PM
To: 'RPG programming on the IBM i / System i'
Subject: AW: Sequential numbering service program +ILE architecture

Instead of an data area you may also have a look at a SQL sequence object.

CREATE SEQUENCE MYSCHEMA/MYSEQ
AS INTEGER START WITH 10 INCREMENT BY 10
ORDER CYCLE NO MINVALUE MAXVALUE 999999999
CACHE 20;

When using a sequence object (which is nothing else than a data area, but
more powerful), you always get the next no.
To get the next value you may use embedded SQL:
Exec SQL Set :MyInvoiceNo = Next value for MySeq;

With Previous Value for MySeq you will get the most recently generated value
for the specified sequence for a previous statement within the current
application process.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Rory Hewitt
Gesendet: Tuesday, 23. June 2009 00:42
An: RPG programming on the IBM i / System i
Betreff: Re: Sequential numbering service program +ILE architecture

I'd say an external data area, or perhaps a file with a single record - a
simple program/procedure can open the file, read it, increment the number
and close it again.

On Mon, Jun 22, 2009 at 3:27 PM, Simon Coulter <shc@xxxxxxxxxxxxxxxxx>wrote:

On 23/06/2009, at 7:58 AM, Kim Spears wrote:

I wish to more closely integrate these separate systems, however to do
so I need an invoice numbering routine that would ensure that no
matter
how many invoicing programs were in use each program cycle would get
the
next unique sequential number and not cause record locks.

Why the concern with locks? Locks are a normal part of application
operation and are not a problem if managed correctly. In this case a
lock would be held only for the duration of the invoice number
increment. Once that's done the lock is released and the number is
available for the next request.

It seems that a service program would be in order. The question is,
how
do I make sure only one instance of the procedure can run?

You can't. You can define the 'next number' variable as global or
static and run in a named activation group which will ensure the same
variable will be used by each invocation but this is scoped to the
job. It will not help when multiple jobs are accessing the number. For
that you need an external object of some sort (or shared activation
groups which are not available to application code).

You could force a single sequence by submitting the invoice generation
job to a single-threaded job queue, or you could have a never-ending
batch program that accepts requests to generate invoices from a data
queue or socket connexion but these are flawed approaches.

Or, maybe I'm asking the wrong question. How can I make sure to
programs that run simultaneously can be guaranteed a unique sequential
number?

Decide on an appropriate external object to contain the next number.
This is either the next number to use or the last number used
depending on how you increment it. This object can be a data area,
file, user space, or anything else suitable. Your application, at the
start of each invoice creation, would lock the 'next number' object in
some way (e.g., ALCOBJ, LOCK, etc. for a data area, normal record
locking for a file, perhaps CMPSWP for a user space), then either:
1) read the current value which becomes your 'next' number,
increment
and write the new 'next' number back to the object.

2) read the current value which is the 'last used' value, increment
it to get the new number and write back to the object.

Release the locks--for a file this will happen automatically as a
result of updating the record.

Thus you have a robust and reliable method for assigning 'next'
numbers that will work across jobs. The only issue is if a user
decides to cancel an invoice then there will be gaps in the sequence.
That can be handled by:
1) an audit log that tracks information about cancelled documents.
or
2) don't get the invoice number until the user has committed to
creating the invoice (i.e., do the processing described above at the
end of the invoice cycle instead of the start).

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------



--
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.