Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Unanswered: savepoint in DB2 sqlj

    Hi,
    we have a SQLJ procedure which inserts and commits after every 300 rows
    with autocommit set as false.
    requirement is that if there is an error in any record, only that insert should be rollbacked keeping earlier record inserts and once 300 record inserts are over, it should commit.
    We created savepoint before every record insert.since it does not allow us to create a savepoint even with diff. name when there is one already existing (error savepoint already exists and nested savepoints not supported), we released savepoint (from record #2 onwards) and recreated before insert start.
    If we get an error during insert, we rollback to the latest savepoint created before insert and fire a commit after batch commit frequency is reached (Eg. 300) .
    Expected result is all records except the erroroneous one should get inserted.
    But to our surprise none of the records were inserted.
    Logic of our program is such that,
    there is a controller/Main SQLJ which calls processing SQLJ for every record and we tried savepoint creation, release and rollback in the processing SQLJ.
    Version : for testing, DB2 V7.1 on sun solaris unix box.
    requirement on DB2 V7.1 on mainframe os/390
    Can anyone please help in this regard.

  2. #2
    Join Date
    May 2006
    Posts
    82
    If it was on mainframe Host programming language i could have done this way. If you get any error while inserting write that record to error file and go ahead with the insert process.

    INSERT RECORD
    IF SQLCODE <> 0 THEN WRITE to error file.
    (Or check for particular sqlcode as per the requirement)

    I dont have any idea about SLQJ. But i believe it is used for executing static sql. If you have an option of writing to error file then you can go for it. At the end of the batch you can investigate on error records.
    Vinay,

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    Quote Originally Posted by vini_srcna
    If it was on mainframe Host programming language i could have done this way. If you get any error while inserting write that record to error file and go ahead with the insert process.

    INSERT RECORD
    IF SQLCODE <> 0 THEN WRITE to error file.
    (Or check for particular sqlcode as per the requirement)

    I dont have any idea about SLQJ. But i believe it is used for executing static sql. If you have an option of writing to error file then you can go for it. At the end of the batch you can investigate on error records.
    Thanx for the reply..
    But for every record, there are multiple inserts in different tables and all of these have to be atomic. i.e. either all or none. so if we get an error in any one insert, need to rollback all inserts for that record. Hence we need help on savepoint. I just wanted to check with it works with DB2 V7.1 on mainframe.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by udaysaw
    requirement is that if there is an error in any record, only that insert should be rollbacked keeping earlier record inserts
    In that case I would propose to just either commit or rollback after every group of inserts that go together, depending on the return codes received.
    After all, that's what commits & rollbacks are for: to define "logical units of work", and in your application your units of work consist of the inserts that form an atomic set.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2006
    Posts
    82
    Exactly, Put all Multiple inserts to a different tables in a Single Logical unit of work.
    On mainframes, ATOMIC can be achieved by putting a COMMIT or ROLLBACK after the set of statements are executed successfully. if all the inserts are successfull put a commit at the end of unit. You have to check for sqlcode of each and every sql statement as mentioned by Peter.

    If some thing goes wrong in between then can rollback to the latest savepoint or if you can handle that, it would be good.
    Vinay,

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    An alternative is to use atomic compound statements:
    Code:
    BEGIN ATOMIC
       INSERT ...;
       INSERT ...;
       INSERT ...;
    END
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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