Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: Determine if Rollback is needed? How?

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by skydiverMN

    In the Create Procedure statement at the top of the sproc there's a ton of statments, most of which I don't understand
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Thumbs up here's what I've figured out...

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •