• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3077
  • Last Modified:

commit and sqlrpgle

I have an sqlrpgle program.
It makes several sql statment.
Sometime, one of this statment goes in error
and the program aborts.
Is there a way with commit settings
in order to confirm all the operation only
at the end of the program and only if there was no error?
thanks
0
bobdylan75
Asked:
bobdylan75
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:
When you compile your sqlrpgle program, you'll need to specify a commitment control level of something other than *NONE. Personally, I use *CHG, but you can use whichever works best for you.

Then, in your program, you'd simply issue a "commit" via SQL after doing all you inserts and updates.

C/EXEC SQL COMMIT
C/END-EXEC

That way, if any of the inserts or updates fail, they'll all get rolled back.

HTH,
DaveSlash
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Sure.  

If the number of update/delete/inserts you are performing is small, then use commitment control and wrap the entire process in a commitment boundary.

Here is an IBM example of a SQLRPGLE program that uses commitment control:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzajp%2Frzajpssile.htm

If this is a large batch process, bear in mind that using commitment control to simultaneously commit a large number of changes is undesirable from a system performance standpoint.  In this case, it is best to design your program to allow it to commit smaller batches of data at a time.

References:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzakj%2Frzakjcommitkickoff.htm

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafydicomm.htm

- Gary Patterson
0
 
bobdylan75Author Commented:
thank you very much
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now