RE: SQL insert row with next sequence number

Thank you...

So the commitment control methodology is where I lack the knowledge.

Is there somewhere I can see a good (simple) example of how to best do this?

FWIW this is a rewrite of an order picking program... the rewrite involves multiple users picking the same (large) order, simultaneously. So I need to do this right. This is where I often "revert" to native RPG file access because of the complexity.

Greg

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of D*B
Sent: Tuesday, November 26, 2019 2:25 AM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL insert row with next sequence number

<Greg>
I need to increment a sequence number based on the values of 3 other fields.


1. If there aren't any rows for a given order number, the sequence would be
1

2. If there are rows for a given order number, I need the "next" sequence
number

Currently I run Exec SQL to get the max(seq#), add one to that in RPG, and
then Exec SQL and insert the row.

Just wondering if a different (more clever) method exists?

Side Note: There can be multiple users inserting rows simultaneously - this
has me concerned for my method.
</Greg>

you could use a subselect in your insert statement (insert into .... select
max(...) where ...). To make it transaction safe you would need commitment
controll and isolation level READ STABILITY (also called locklvl(*ALL)) to
ensure that max couldn't change during your insert. Don't forget to release
the locks by a commit after the insert.

D*B