Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006

    Unanswered: lock table for insert with increment number

    Hi !!
    here's my problem with DB2 version 7.1 (zos, cobol, cics transaction)
    I have a table client which contains 3 cols (id_client, sequence_number, data)
    the primary key is id_client and sequence number.
    When i created a new row and a new client, the sequence_number = 1
    When i created a new row with old client, the sequence_number = (max of sequence number of the same id_client +1).
    The solution is :
    1/ to do a select max of sequence_number
    2/ add 1 to this sequence_number
    3/ make the insert.
    But i'm afraid if there are 2 persons who do the same things at the same time, they will have the same sequence number. What about concurrency ?
    Thanks a lot.

  2. #2
    Join Date
    Nov 2004


    or create a sequence and retrieve this unique nbr or
    select .... for update --> this will acquire a higher lock and avoid any other application to do a select.. (lock wait)
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified

  3. #3
    Join Date
    May 2003
    If you retrieve a duplicate sequence number, you will get an RI error when you try the insert since you have a PK, so there will be no data integrity problem. You can code retry logic if that specific error code is returned (duplicate PK).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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