Hi everyone, any ideas about this?
I have a cics transaction that makes updates and inserts in several db2 tables; we have one issue with an operation that was rolled back but an insert in one table remains, is it possible? I mean is possible that roll back failed?
Any thoughts or suggestions are welcome...

Are you sure that the CICS transaction maps 1:1 to DB2 transactions? If not, it can indeed happen that the CICS transaction uses multiple DB2 transactions, some of them having already been committed.

Hi, I have checked that and is 1:1, I feel confused about this, the transaction do something like this

1.- Get a key from a base table ans update the key with the next number
2.- Insert row in table 1 using the key
3.- Insert rown in table 2 usign the key
4.- Update some register in table 3
5.- Insert row in table 4 usign the key
6.- Insert row in table 5 usign the key
6.- commit or rollback

In this case what happend was that the next transactions after the error complains that can't insert the register in table 5 because a register exists already with that key;

It have never happened before, and I've checked that the insert in table 5doesn't come from another transaction, that doesn't exist commit points in between, etc.

ok if u say 1:1 can you chk how many agents having application name as cics in db2 list applications show detail and how many overall users who are using ur application
i believe its not 1:1 connections

regds
Paul

Hi, I really appreciate your help

I don't understand what you mean by agents and we process few transactions, perhaps 1,000 a day (is a web service). Perhaps there is something I dont quite understand... in cics db2 guide says that for every transaction in cics a thread is created depending on the definitions on dbconn, dbentry and dbtran and this thread is not released until syncpoint and according to my analysis all the sql operations run in the same uow, could you elaborate a little more on how you guess the connectios could be defined that would yield this result so that i can talk to cics people and explore this hypotesis? or if you could refer some manual where i can explore do some research

Or maybe you are using auto commit?

I guess the real question is: what is your application doing wrong to break the consistency guarantee by DB2 transactions?

I agree, in spite of what the reason is, only happens under very specific circumstances, it has happened 2 times in a year; that's why i discard things like autocommit... thank you for sharing your ideas

Hi, I guess I know what is happening, Stolze, after your question "what is your application doing wrong to break the consistency guarantee by DB2 transactions" took me in the right path, so I analyse the code with fresh air and i found that the data used to insert to the db2 table is coming from a queue, and in certain situations the queue can contain data from another transaction, so the row came from another uow;

Stolze and Mathew thank you very much