Hello Everyone,

I have a wierd problem which I am currently facing with my sequences.
I have a an 2 ITIC (integration tool) mapping which is running in parallel and updating/creating records in a table XYZ which has a primary uniuqe key ID which is generated through a sequence.

Now out of the 2 ITIC's (lets say ITIC A and ITIC B) one is creating problem with the sequences it is generating the sequence which is already generated by the other and then the other ITIC is giving sql exception (duplicate unique value)
I am not sure if it is because the 2 ITIC 's are running in parallel and updating/creating records in the same table/attributes.

Please help with this
Thanks
Piyush

i could think of situation where in a same UOW we select from sequence and insert in table
it might be better to commit the increment of sequence asap and not to wait for other operation like insert to occur.
otherwise you could define the column in the table as identity and value would be assigned by table-handler and not sequence...

It is not clear if you are using Sequences or Identity columns, but in either case you do not need to commit to reserve them. Once they are obtained, no one else can use them, and if you rollback there will be a gap in the sequence.

On the other hand, if you are using some homegrown next-available-number table, instead of sequences or identity columns, then you could have a problem with commits.

Thanks for the quick reply

I am using Sequence here and not the Identity coloumns.
The problem is 2 parallel ITIC runs both access same sequence and both getting the same generated number from sequence.

What version of DB2 is it ? I suggest that you raise a PMR.
DB2 Sequences are not supposed to work that way.

As Marcus said, incrementing of sequences do not wait for the transaction to commit.

A basic question: How do you know they are the same sequence ? Are the schemas same too? Can you check in the db2 catalog ?
Also, are you sure that the applications do not share the very same sequence number ?

select * from syscat.sequences where seqname='seqence name'


Sathyaram