Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Akron, Ohio
    Posts
    2

    Red face Unanswered: ROLLBACK on-ERROR IN A STORED PROCEDURE??

    I'm using db2 v 6 on MVS OS390.
    I'm doing stright sql stored procedures.

    I'm calling these stored procedures from ASP (active server pages)

    Can the stored procedure on any negitive SQLCODE number roll itself back? I'm tring not to do the rollback from the Active Server Pages (ASP) script which would cause me more "if then else error logic".
    I would like all my stored procedures to work "100% & commit" or "return an error code & rolled back".


    If rollback in a stored procedure is not possible then I guess I'll have to do it in the ASP code. I've been unsucessfull thus far in coding a "rollback". can someone share an asp example ?

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Won't the SPs do this anyway?

  3. #3
    Join Date
    Dec 2002
    Location
    Akron, Ohio
    Posts
    2

    you would think so.

    Originally posted by Damian Ibbotson
    Won't the SPs do this anyway?
    My experience is that no the stored procedures compiled for DB v6.1 on the MVS OS 390 do not do this at least automaticly. Prehaps I have some settings wrong but when they encounter a negitive type error they do not rollback. Can someone help...


    They all start with the same options:


    CREATE PROCEDURE P652OG

    ( IN SENT_SESSIONID CHAR(40),
    IN SENT_TRACKING CHAR(8),
    IN SENT_OLDREQSW CHAR(1),
    IN SENT_RCPTREQSW CHAR(1),
    OUT TEST_NOTE VARCHAR(40),
    OUT OUT_CODE INT)
    FENCED

    RESULT SET 0

    LANGUAGE SQL

    NOT DETERMINISTIC

    MODIFIES SQL DATA

    NO DBINFO

    COLLID SPCOLLID

    NO WLM ENVIRONMENT

    ASUTIME NO LIMIT

    STAY RESIDENT NO

    PROGRAM TYPE MAIN

    SECURITY DB2

    RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),
    BE(4K,,),LIBS(4K,,),ALL31(ON)'

    COMMIT ON RETURN NO

    P1: BEGIN

  4. #4
    Join Date
    Feb 2004
    Location
    Paramus, NJ
    Posts
    3
    Originally posted by Damian Ibbotson
    Won't the SPs do this anyway?
    Version 6 of DB2 does not allow to use SQL ROLLBACK with the stored procedure. However, when the ROLLBACK statement got executed, the data will be rollbacked even though the SQLCODE = -751. You need to handle this SQLCODE in your stored procedure.

Posting Permissions

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