DB2 7.2

Could anyone out there tell my how to stop DB2 removing all the rows from my temporary table when the unique constraint gets violated.

table:
DECLARE GLOBAL TEMPORARY table tt (a VARGRAPHIC(10),b VARGRAPHIC(20) ) ON COMMIT PRESERVE ROWS NOT LOGGED
index:
CREATE unique index SESSION.xtt on SESSION.tt(a)

inserts:
insert into SESSION.tt (a, b) values ('xxx', 'xxx')
insert into SESSION.tt (a, b) values ('yyy', 'yyy')

count:
select count(*) from SESSION.tt
- result is 2 - GOOD!

another insert (get duplicate error as expected):
insert into SESSION.tt (a, b) values ('xxx', 'xxx')
- gets [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "SESSION.TT" from having duplicate rows for those columns. SQLSTATE=23505

do count again:
select count(*) from SESSION.tt
- result is 0 - BAD!!!

I would expect the count to still be 2.

I tried tagging on "ON ROLLBACK PRESERVE ROWS" to the table declaration but it made no difference.

thanks for any help on this.

Andy

Have you considered upgrading to a supported version of DB2? (V7 is out of services for nearly 3 years now.)

Well if I can resolve this issue I might...

Actually its version 8.1.

My diag tool was sort of hard coded...its a long story...

This does look like a bug (or an error in the manual); I would open a PMR.

I think I have seen this behavior also. I think it has to do with getting an error that clears the GTT. Maybe you should open a PMR with IBM.

Andy

Not bad - seeing as though I spotted this 'bug' in 2004!
Re-addressing it due to performance problems. (my work around is to first select from the TT using the insert values to make sure that I don't get a duplicate before actually doing the insert).

Ohh, well - if thats what you experts say then so be it.

Andy

A negative return code will perform a rollback. I am not sure it is a bug. You might be able to trap the error with a condition handler.

Instead of doing a select then a conditional insert, use the MERGE statement.

Andy

If a rollback is performed then surely adding "ON ROLLBACK PRESERVE ROWS" to the table declaration would stop the previous rows from being removed? - as the wording suggests?

it will keep the rows that were comitted, but any work done between a prior commit and current rollback will be rolled back. This is not a bug it is well documented and the correct behavior.
Dave

The thing is that even the previously committed rows are gone after the error. Besides, I don't believe there is an implicit rollback upon the error.

Besides, the clause ON ROLLBACK PRESERVE ROWS implies that no rows are deleted upon rollback either. In that, temp tables deviate from regular base tables.

Just tested this and you are wrong - the previous comitted rows are also deleted.

Looks like I'll have to raise a PMR with IBM.

Thanks to all who contributed to my original question.

Andy