Results 1 to 5 of 5

Thread: DB2 Sequence

  1. #1
    Join Date
    Jul 2011

    Question Unanswered: DB2 Sequence

    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

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    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...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

  3. #3
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jul 2011
    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.

  5. #5
    Join Date
    Aug 2001
    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'

    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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