SQL SAVEPOINT command ON ROLLBACK option - IBM AS400

This is a discussion on SQL SAVEPOINT command ON ROLLBACK option - IBM AS400 ; I understand Commit Control concepts, and have happily used them in COBOL and in Java, but I'm confused about the SAVEPOINT command in embedded SQL. Why do I have to specify either ON ROLLBACK RETAIN CURSORS or ON ROLLBACK RETAIN ...

+ Reply to Thread
Results 1 to 13 of 13

Thread: SQL SAVEPOINT command ON ROLLBACK option

  1. SQL SAVEPOINT command ON ROLLBACK option

    I understand Commit Control concepts, and have happily used them in
    COBOL and in Java, but I'm confused about the SAVEPOINT command in
    embedded SQL.

    Why do I have to specify either ON ROLLBACK RETAIN CURSORS or ON
    ROLLBACK RETAIN LOCKS?

    Surely there are times when you would want to retain both, and other
    times when you would want to retain neither? I feel I am
    misunderstanding something here. Can anyone enlighten me?


  2. Re: SQL SAVEPOINT command ON ROLLBACK option

    I'm guessing there are other keywords for the SAVEPOINT statement that allow
    different Cursor & Lock behavior, but those keywords are not yet supported by
    DB2 for i5/OS.

    walker.l2 wrote:
    > I understand Commit Control concepts, and have happily used them in
    > COBOL and in Java, but I'm confused about the SAVEPOINT command in
    > embedded SQL.
    >
    > Why do I have to specify either ON ROLLBACK RETAIN CURSORS or ON
    > ROLLBACK RETAIN LOCKS?
    >
    > Surely there are times when you would want to retain both, and other
    > times when you would want to retain neither? I feel I am
    > misunderstanding something here. Can anyone enlighten me?
    >


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

  3. Re: SQL SAVEPOINT command ON ROLLBACK option

    Hmm, maybe.
    I'm worried that neither option seems to fully release SQL resources.
    If my program hits an SQL error, I can rollback to the savepoint okay,
    but will just doing that release all the SQL resources my program
    might have open at the point of the initial error? (Or are these SQL
    resources released automatically by the OS when my program ends?)

    I suppose I could look into BEGIN ATOMIC ... END instead, but at first
    glance SAVEPOINT seemed the simpler solution.


  4. Re: SQL SAVEPOINT command ON ROLLBACK option

    On Aug 2, 4:43 pm, "walker.l2" wrote:
    > Hmm, maybe.
    > I'm worried that neither option seems to fully release SQL resources.
    > If my program hits an SQL error, I can rollback to the savepoint okay,
    > but will just doing that release all the SQL resources my program
    > might have open at the point of the initial error? (Or are these SQL
    > resources released automatically by the OS when my program ends?)
    >
    > I suppose I could look into BEGIN ATOMIC ... END instead, but at first
    > glance SAVEPOINT seemed the simpler solution.


    Surely to fully release resources you have to end the work unit with a
    final commit, or rollback with no savepoint specified?
    It does seem odd that you must specify 'on rollback retain cursors'
    when adding a savepoint unless there is a plan to allow specification
    without this sometime in the future. I'm on V5.3 .

    Jonathan


  5. Re: SQL SAVEPOINT command ON ROLLBACK option

    The problem is that I can't guarantee that a program higher up the
    calling chain hasn't already started a commit cycle. If that has
    happened, I don't want to ROLLBACK all the changes, but only my own,
    hence ROLLBACK TO SAVEPOINT seemed like the way to go. A COMMIT or
    plain ROLLBACK ends the UoW, but does a ROLLBACK TO SAVEPOINT end the
    UoW as well? (And if so, why are resources left open?)

    Sometimes I wish I could code this in Java (instead of COBOL with
    embedded SQL), and then I wouldn't have these problems!


  6. Re: SQL SAVEPOINT command ON ROLLBACK option

    On Aug 3, 2:32 pm, "walker.l2" wrote:
    > The problem is that I can't guarantee that a program higher up the
    > calling chain hasn't already started a commit cycle. If that has
    > happened, I don't want to ROLLBACK all the changes, but only my own,
    > hence ROLLBACK TO SAVEPOINT seemed like the way to go. A COMMIT or
    > plain ROLLBACK ends the UoW, but does a ROLLBACK TO SAVEPOINT end the
    > UoW as well? (And if so, why are resources left open?)
    >
    > Sometimes I wish I could code this in Java (instead of COBOL with
    > embedded SQL), and then I wouldn't have these problems!


    Well the commit cycle was started by the higher up program as you
    stated - so that level of programming needs to finalise the uow with a
    commit or rollback. Now of course you are ni trouble if your changes
    must be committed but the higher level either rolls back or does not
    exist & hence the job ends & rolls back automatically. Your programs
    need to co-operate in this area - cant see any other way really.

    Jonathan


  7. Re: SQL SAVEPOINT command ON ROLLBACK option

    The problem is that the prior programs are provided by a third-party,
    and I have no idea exactly how they behave. (Some of the information
    is discernible by using debug breakpoints and working with the Job and
    Journals, but the picture is not complete - I can't guarantee the
    behaviour isn't different for different input data.)

    :-(

    Fortunately the updates I want to do are pretty straightforward (my
    commit cycle is only there to ensure DB integrity in the event that
    the AS/400 fails part way through my updates), so I'm not overly
    concerned about my program messing up the third-party stuff.

    Now that I think about it, I suppose I really have a more general
    question: in Java each class is responsible for its own commit cycle
    regardless of what other classes elsewhere in the stack trace are
    doing (unless you are deliberately doing a 2-phase transaction), so
    handling commits and rollbacks is straightforward (since you never
    need to worry about what any other class is doing); but with embedded
    SQL (in RPG or COBOL) you can never be certain at compile time what
    the runtime call stack will be, so what technique can you use to
    prevent one program from interfering with the commits / rollbacks of
    others in the call stack? (Whilst the help text on the COMMIT setting
    of CRTSQLCBL is clear on the visibility of changed data, it doesn't
    give much insight to UoW boundary impacts.)


  8. Re: SQL SAVEPOINT command ON ROLLBACK option

    If you truly want to isolate a program from transactions started by other
    program, then you could consider the use of activation groups.

    walker.l2 wrote:
    > The problem is that the prior programs are provided by a third-party,
    > and I have no idea exactly how they behave. (Some of the information
    > is discernible by using debug breakpoints and working with the Job and
    > Journals, but the picture is not complete - I can't guarantee the
    > behaviour isn't different for different input data.)
    >
    > :-(
    >
    > Fortunately the updates I want to do are pretty straightforward (my
    > commit cycle is only there to ensure DB integrity in the event that
    > the AS/400 fails part way through my updates), so I'm not overly
    > concerned about my program messing up the third-party stuff.
    >
    > Now that I think about it, I suppose I really have a more general
    > question: in Java each class is responsible for its own commit cycle
    > regardless of what other classes elsewhere in the stack trace are
    > doing (unless you are deliberately doing a 2-phase transaction), so
    > handling commits and rollbacks is straightforward (since you never
    > need to worry about what any other class is doing); but with embedded
    > SQL (in RPG or COBOL) you can never be certain at compile time what
    > the runtime call stack will be, so what technique can you use to
    > prevent one program from interfering with the commits / rollbacks of
    > others in the call stack? (Whilst the help text on the COMMIT setting
    > of CRTSQLCBL is clear on the visibility of changed data, it doesn't
    > give much insight to UoW boundary impacts.)
    >


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

  9. Re: SQL SAVEPOINT command ON ROLLBACK option

    Aha! Does this mean I have finally found something that can be done in
    ILE but not in OPM? Perhaps now at last I'll pull out the ILE manuals
    and start to learn it properly, rather than continuing to use OPM.


  10. Re: SQL SAVEPOINT command ON ROLLBACK option

    Having thought about it some more, I'm pretty sure the third party
    programs only use Commit Control with Record IO, so my SQL commits
    shouldn't cause any conflicts, right?


  11. Re: SQL SAVEPOINT command ON ROLLBACK option

    On Aug 8, 9:41 am, "walker.l2" wrote:
    > Having thought about it some more, I'm pretty sure the third party
    > programs only use Commit Control with Record IO, so my SQL commits
    > shouldn't cause any conflicts, right?


    I dont think so. If I use SQL to set the transaction isolation level
    then do some updates & leave SQL I get a message saying some
    uncommitted updates have occurred. ( I often get this when I dont set
    the transaction level & only do selects & even no outstanding commits
    exist outside SQL) Then the CL command COMMIT works. Are your updates
    occuring because of triggers? If so then the good old fashioned
    trigger program interface to eg RPG provides a flag saying commit is
    in place & so the RPG program can decide to open its files under
    commit control & leave the data uncommitted & at the mercy of the
    higher level. This means the whole transaction is controlled correctly
    by the 3rd party sw. If the commit flag is off the update is
    effectively commited immediatly & the RPG prog can just update
    regardless of commit control. I tested this & it works fine - however
    I would have had the same problem as you describe if I wanted to use
    commit control at this point for some other reason. I am not sure if
    you would get suce a flag for an SQL trigger.I think Kent's idea of
    activation groups may be your best bet.

    Jonathan


  12. Re: SQL SAVEPOINT command ON ROLLBACK option

    The scenario is effectively a trigger, but I'm not actually using one:
    triggers don't seem to be very visible, and the sys admin guys are
    worried that if the trigger were to be accidentally deleted / lost
    some months from now they wouldn't easily notice, or be able to
    determine how to recreate it. Instead, since I do have access to the
    source code of one of the third-party programs, I have altered it to
    directly call my program; but of course I can't guarantee that another
    third-party program higher in the call-stack won't be doing some other
    commit control stuff.

    > If so then the good old fashioned
    > trigger program interface to eg RPG provides a flag saying commit is
    > in place & so the RPG program can decide to open its files under
    > commit control


    What are you referring to here? (Excuse my ignorance - I'm aware of
    the concept of file triggers, but we only use triggers on queues.) I
    can't see anything on ADDPFTRG that relates to commit control. Or is
    this something I can retrieve once control reaches my program (along
    with the relevant file record)?

    For safety's sake, it sounds like activation groups are the way to go.
    Whether I'll get the go-ahead to introduce the first ILE program here
    is another matter though. The particular function I'm dealing with
    should be relatively straight-forward / stand-alone, so I don't think
    I'll be upsetting any third-party stuff by COMMITing myself [he said,
    crossing his fingers].


  13. Re: SQL SAVEPOINT command ON ROLLBACK option

    On Aug 8, 2:31 pm, "walker.l2" wrote:
    > The scenario is effectively a trigger, but I'm not actually using one:
    > triggers don't seem to be very visible, and the sys admin guys are
    > worried that if the trigger were to be accidentally deleted / lost
    > some months from now they wouldn't easily notice, or be able to
    > determine how to recreate it. Instead, since I do have access to the
    > source code of one of the third-party programs, I have altered it to
    > directly call my program; but of course I can't guarantee that another
    > third-party program higher in the call-stack won't be doing some other
    > commit control stuff.
    >
    > > If so then the good old fashioned
    > > trigger program interface to eg RPG provides a flag saying commit is
    > > in place & so the RPG program can decide to open its files under
    > > commit control

    >
    > What are you referring to here? (Excuse my ignorance - I'm aware of
    > the concept of file triggers, but we only use triggers on queues.) I
    > can't see anything on ADDPFTRG that relates to commit control. Or is
    > this something I can retrieve once control reaches my program (along
    > with the relevant file record)?
    >
    > For safety's sake, it sounds like activation groups are the way to go.
    > Whether I'll get the go-ahead to introduce the first ILE program here
    > is another matter though. The particular function I'm dealing with
    > should be relatively straight-forward / stand-alone, so I don't think
    > I'll be upsetting any third-party stuff by COMMITing myself [he said,
    > crossing his fingers].


    In position 33 of the trigger buffer parameter passed to the triggered
    program is a char(1) with 0=none,1=chg,2=CS,3=all. I found this in the
    redbook 'who knew you could do that...' but is most likely defined in
    other places too. The trigger programs we have include the comment
    'Apply this to PF xxx by using ADDPFTRG.......' so we can 'easily' re-
    apply if we ever need to. We actually use just 1 trigger which does
    the pointer maths & then decides which program to call with the new
    pointers. This way we can replace triggers which would otherwise be
    locked by users in the system. We can also make unpleasant changes
    depending on if its the live/test system etc.

    Jonathan.


+ Reply to Thread