Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Identity column: how to make numbering without missing values in identity column

    Hi,
    on "DB2 Enterprise v8.2 FP11" on Linux I would like to create such a identity column that would prevent having missing numbers. For example: identity column should have values 1, 2, 3, etc and not 1, 3, 4 etc (missing 2).

    Sample:

    application 1:
    db2 create table admin.tab (col1 int not null GENERATED BY DEFAULT AS IDENTITY, col2 int)
    db2 +c insert into admin.tab values (default, 100)

    application 2:
    db2 +c insert into admin.tab values (default, 200)
    db2 +c commit

    application 1:
    db2 +c rollback

    Executing select on table returns the following data:
    COL1 COL2
    ----------- -----------
    2 200

    Application 1 rolled back so I have a missing number 1 in table. Requirement in my case is to have no missing values. This probably means that no insert should be allowed by application 2 before application 1 executes commit or rollback.

    Is there any way I could make this numbering without missing numbers in identity columsn?

    Thanks,
    Grofaty

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't think it is feasable to do that with identity columns if you are concerned about rollbacks. You can just use a next-available-number table and make the updating of that in the same unit of work as the insert.

    Another problem with identity columns is that cache values are lost if the database is deactivated. So you would want to set the cache to zero.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A next-available-number table has the drawback that it pretty much serializes all transactions that need this table. And that is rather bad performance-wise. (Any decent database admin will be seriously tempted to shoot you for doing things like that.) That's one of the reasons why sequences and identity column were introduced.

    But let's step back a little: Grofaty, why do you believe you have a "requirement" to not have missing values? Is it really a hard requirement or just a "I would like to have it that way, but technically it doesn't matter at all" thing?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    Is it really a hard requirement or just a "I would like to have it that way, but technically it doesn't matter at all" thing?
    Hi,
    it is hard requirement, because law in over country restricts there should be no missing values for example account system that needs strict sequence with no missing values.

    For now this kind of problem is solved to have two tables. One has account data and one table has one-record table with max account value. When new account appears then this one-record table is locked new account is inserted into account table and one-record table has max+1 number then commit is executed.

    This way of solving problem has a performance impact because transactions has to wait to unlock one-record table.

    Any idea how to make this problem solved. Identity is good choice but no missing values is requirement.

    P.S. Can you please point me out to some document about "next-available-number table"? I haven't heard for this functionality yet.
    Thanks,
    Grofaty
    Last edited by grofaty; 09-29-08 at 03:53.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    I would like to create such a identity column that would prevent having missing numbers.
    The following should do that:
    Code:
    INSERT INTO admin.tab
     SELECT max(col1) + 1, 300
     FROM admin.tab
    So no need for an identity column. If the first column is indexed, this query should be sufficiently performant.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Peter,
    this is good solution but it has one weakness: how to find out which value I have inserted into table.

    My problem is little more complex. I need to insert new identity value and get info about the value and in the same unit of work I need to update another table with this value.

    So if using identity I can get this value by executing command:
    SELECT INT(IDENTITY_VAL_LOCAL()) FROM SYSIBM.SYSDUMMY1
    or VALUES INT(IDENTITY_VAL_LOCAL())

    Peter, is there any simple way I could get info about max(col1) info from insert with select command?
    Thanks,
    Grofaty

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Select COL1 from final table
    (INSERT INTO admin.tab
    SELECT max(col1) + 1, 300
    FROM admin.tab)

    This assumes COL1 is the PK of the inserted table that you want the value for. You can include any of the columns in the table (after the insert takes place) in the select.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    Select COL1 from final table
    (INSERT INTO admin.tab
    SELECT max(col1) + 1, 300
    FROM admin.tab)
    Indeed, this is the only way to do this.
    "select from insert" can of course also return any other column from the just inserted row(s).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A
    Select COL1 from final table
    (INSERT INTO admin.tab
    SELECT max(col1) + 1, 300
    FROM admin.tab)
    Unfortunately, this does not take into account concurrent transactions, which may generate duplicate keys or gaps when using such a scheme.

    I think the only reliable way to ensure generation of a unique key without gaps is to use a key table where the record corresponding to the next key value is locked during the entire transaction. Further to that, there may also be a problem of deleted key values, which will require renumbering. Clearly this creates the performance bottleneck, but that's the price you pay for such a requirement.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    n_i, can you please provide more info about why this solution is not good? I have tested it from db2cmd and it looks like it does what I want

    application 1:
    db2 create table admin.tab (col1 int not null primary key, col2 int)
    db2 +c select col1 from final table (insert into admin.tab select max(col1) + 1, 300 from admin.tab)

    application 2:
    db2 +c select col1 from final table (insert into admin.tab select max(col1) + 1, 300 from admin.tab)

    application 1:
    db2 +c rollback

    application 2:
    db2 +c commit

    Application 2 gets the right numbering with no gaps. Am I missing something?

    BTW, delete is not a problem, because there should be no delete statement (law requirement).

    Regards,
    Grofaty

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try committing application 1 instead of rolling back.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'm still with Knut's first response about the "HARD" requirement. Are you sure this isn't being misinterpreted here? This sounds uttlerly ridiculous to have to jump through so many hurdles. We were just sitting in office discussing your situation and we all find it very hard to believe that lawmakers in some country have put it forth that every number in a table of data that is owned by a company all have sequential numbers with none missing. If it is truly the case then your lawmakers really need more to do.
    Dave

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Much will ultimately depend on that part of the requirements to which we're not privy.

    For example, the ID in question is a bank account number, and these numbers are required to be consecutive. However, only an average of 30 accounts are opened each day, in which case it is perfectly acceptable from the concurrency point of view to use the key table solution.

    Or may be these are online order numbers, which also must be consecutive. Orders come at the rate of 50 per second at peak times but the order consolidation job and reports run once per day, at 18:30. In such case you could assign internal order IDs from a sequence and keep a separate table containing pairs of the internal order IDs and "lawful" order numbers. That latter table is updated daily, just before the batch run, to generate consecutive "lawful" order numbers for those orders created during the day. No concurrency issues and just a slight delay for the batch.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by n_i
    Try committing application 1 instead of rolling back.
    Hi,
    I have tried this and application 2 gets number of application 1 risen by one. I can't see the problem here. Can you please write how do you see problem?
    Regards,
    Grofaty
    Last edited by grofaty; 09-30-08 at 15:58.

  15. #15
    Join Date
    Jan 2003
    Posts
    1,605
    dav1mo, this gap-free numbering is required at billing-system. So bills has to be numbered without gaps. If some bill has a mistake the record from table must not be deleted but "reversal event" has to appear. So there always has to be trace what happened with no-valid bills.

    So if there would be bill numbers 1, 2, 3, 5, etc (missing 4) tax-government-man would ask what happened with bill no. 4? Are you trying to avoid country tax by deleting bill no. 4 and not paying the tax? If this bill was "reversal" tax-man can check at customer if this bill was really reversed or was just a tax-avoidance.

    So if tax-government-man comes to inspection he/she checks all the bills in particular year and checks if all taxes have been paid. Missing numbers of bills would tell tax-government-man that there is high possibility tax was avoided.

    Hope this helps explaining numbering without gaps.

    So my requirement is not gap free numbering in all cases but in some special cases like billing that a law says there should be no gaps.

    Hope this helps explaining. By the way are in your country bills order numbers not required to have no gaps? Don't you have difficulties to persuade tax-man what happened with missing bills?

    Regards,
    Grofaty
    Last edited by grofaty; 09-30-08 at 16:07.

Posting Permissions

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