RE: SQL insert row with next sequence number

Thanks for the reply...

So these are some of the same challenges that put me back into "RPG RLA mode"
1. I cannot use commitment control with all of the files in the program - some are legacy ERP database files, none are journaled.
2. The specific files in question are used by other programs - so unsure what effect changing these files will have (maybe none?)
3. I'm using other service programs that may not use *CALLER

So if I was writing something from scratch - not reusing existing (working) code and database files, this might be easy. But it adds a level complexity and development time, which is why I've hesitated in the past. I feel like I'm stuck in-between legacy and modern coding.



Commit is no magic and very easy with SQL:
- first of all you would need journaling for your tables (it's a good idea
anyway to journal all tables!)
- your CRTSQLRPGI would specify COMMIT(*CHG) < this is the default anyway>
- give your PGMs a named ACTGRP and your SRVPGMs *CALLER
- so the controlling PGMs are commit master (saying COMMIT or ROLLBACK) and
your SRRVPGMs commit slave
- for your insert statement you could use an isolation clause to pick up
another locklevel (*ALL instead of *CHG from the create) => insert into ....
with RS
- check SQLCODE afterwards. at success commit (releases all locks in
transaction) else rollback (undoes transaction)
- Switching to SQL you would need this technique all overall!

It's a little bit diffrent from RLA, but more powerfull and stable.

D*B