I'm new to DB2, and asking the database guy here, he says that we're running on an iSeries. Hopefully that will make sense to you...

My question is regarding a stored procedure that's kicking off a bunch of inserts, other stored procedures, etc. I need an example of how to implement a commit/rollback depending if any errors are thrown by the stored procedure (or any stored procedures within).

In the Create Procedure statement at the top of the sproc there's a ton of statments, most of which I don't understand and they're put there automatically when the procedure is created (I think). Included below is everything after any parameters would be listed, up to the first BEGIN:

DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC DBNAME.STOREDPROCEDURENAME
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *ALL ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX

After all the reading that I've done, I changed the bold COMMIT section above from *NONE to *ALL, but I'm not sure if this is needed for what I'm trying to accomplish.

Basically, when everything has processed what code is needed to know if all's successful or not? In SQL Server if @@error <> 0 then we KNOW that something's happened. What do I need for DB2? Examples would be nice too!

Thanks for your help.
SkydiverMN

Considering the statement above, I'm not sure how far you can go, but I would start here: GET DIAGNOSTICS statement

There are some examples. To commit or rollback, use COMMIT and ROLLBACK statements respectively.

Well, it seems that changing the COMMIT to *ALL and putting the commit at the bottom of the procedure does everything that I need it to do.

Within my procedure I have many statements or other stored procedure calls and I created a situation that forces the 3rd one to fail. This means that the processing got through the first 2 that most certainly inserted rows. When the 3rd part fails, nothing gets committed. Removing my failure situation causes all statements to process successfully, and the rows are committed.

Seems that the rollback is automatic if there's an error. If not error, then the procedure hits the COMMIT and it gets finalized.

Hope this helps people, and it solves my situation (until the next situation)

Thanks!
SkydiverMN