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.

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.

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.

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.

An alternative is to use atomic compound statements: