Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Unanswered: Primary Key Generation

    Hi,
    Our system is handling some 30 - 40 people concurrently at a time. For all the people who are coming to our application, we need to generate a primary key and assign it to them. Right now we are doing it at the application level. Is there a way where the db2 can create it at the table level. How ?. Is there any impacts?. What kind of impacts?. Is there any guide to refer?. We are using the db2 6.0

    Thanks,
    Anto

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Primary Key Generation

    There are couple of articles on on ibm technet site
    Quick search on identity
    http://www7b.software.ibm.com/dmdd/l...2fielding.html
    http://www7b.software.ibm.com/dmdd/l...05pilaka2.html


    But I personaly prefer to manage ids in application - it's faster
    You do not need to have extra call to get generated value.

  3. #3
    Join Date
    Mar 2003
    Posts
    69
    How come it is fast when we handle it in application. Even in application also, if you want to generate a new key, you have to lock the table for the particular time and get the key and then release. Am i correct?. So how should it be faster than the db2 do the generation of the key?.

    The current scenario in our case is, we have a separate table where we are generating all the unique keys for different tables from our application and assign it. Now we are thinking, instead of using a separate table to generate keys' why don't we use the max function ?. But i don't know how efficient it is?. Pls give me your suggestions.

    thanks

  4. #4
    Join Date
    Dec 2002
    Posts
    134
    In one of the projects we were using the following approach for generating keys in application (i think it's pretty standard)

    1. create table id_generator(id int, next int)
    2. get range of ids, for example of 1000
    update table id_generator
    set next = next + 1000
    where id = 1
    ;
    select next from id_generator where id =1
    ;
    commit
    ;
    3. use ids in range you just received [next-1000, next)


    If you do not need to know id of just inserted column (and you do not use array insert), then use identiy columns (just increase the cache parameter for identity clause)

    regards,
    dmitri

  5. #5
    Join Date
    Dec 2002
    Posts
    134
    One more thing - do not use MAX - it's slower and you can run into concurency issue (first process selected max and while it was in a process of inserting record with id = max+1, another process selected max ...)

    regards,
    dmitri

  6. #6
    Join Date
    Mar 2003
    Posts
    69
    I have a concern over this. Right now what we are doing is we are locking the table to get the unique key. And our DBA is saying that do not lock the table as this may cause problems. Is there any other way to get the keys other than this. I know that we can use the Max function . But will end up to concurrent issues!!. And since we have the db2 tables in place we can not use the "Identity" which is very costly. So what could be the way to eliminate this problem!?.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ask your DBA why the table isn't using an IDENTITY column

    your current method of locking the table is a potential bottleneck and not the right way to go

    okay, so the tables don't have IDENTITY now, but why could you not add them?

    and what did you mean by "very costly"?

    IDENTITY may or may not require an extra call to find out, after the insertion, what the assigned value was -- i'm not sure if DB2 has an equivalent to @@IDENTITY, which is what you would use in SQL Server, but you can always query the value back simply by selecting the row with the "real" primary key value that you just inserted, and it doesn't require a transaction lock or anything, but yeah, it's an extra read


    rudy

  8. #8
    Join Date
    Mar 2003
    Posts
    69
    No actually the tables are already in place. So If we want to use the identiy, I think we need to drop the table and re-create it with this constraint. Am i correct?. Also so is it like do we need to use the some database constraint ( like identiy) to avoid the locking problem?. By any change cant we handle it through our application server level?

  9. #9
    Join Date
    Dec 2002
    Posts
    134
    Please ask you DBA what kind of problems this could lead to. I doubt he can name resonable ones
    (except saying it could lead to the problems
    And you are not locking the whole table, you lock 1 row

    If you do not what re-create table to add identity (it's not very difficult if tables are not big) you can use sequences.

    I would say if you insert many rows and do not care about ids of inserted records use identity/sequence
    If you insert many rows and do care about values - use own generator


    P.S.
    If you have only one application where you generate ids, you can use any mechanism you want for generating, even simple counter in memory, just make sure on startup you read current max id from db

  10. #10
    Join Date
    Apr 2003
    Location
    Trier, Germany
    Posts
    28
    Originally posted by r937
    ask your DBA why the table isn't using an IDENTITY column

    your current method of locking the table is a potential bottleneck and not the right way to go

    okay, so the tables don't have IDENTITY now, but why could you not add them?

    and what did you mean by "very costly"?

    IDENTITY may or may not require an extra call to find out, after the insertion, what the assigned value was -- i'm not sure if DB2 has an equivalent to @@IDENTITY, which is what you would use in SQL Server, but you can always query the value back simply by selecting the row with the "real" primary key value that you just inserted, and it doesn't require a transaction lock or anything, but yeah, it's an extra read


    rudy
    Hi, DB2 V6 on OS/390 or V7.1 and higher on NT know identity columns for key generation. They are VERY quick, quicker than ervery other method I erver heard of (including the suggestions here).
    Search for Document GC26-9946-01 or SG24-6108-00 on GOOGLE, there everything is explained and performance is discussed.

    Bye

  11. #11
    Join Date
    Mar 2003
    Posts
    69
    Ok. That's fine. Let me put one more question. In a real scenario where there are some 100 to 200 concurrent users hitting the application ( Essentially what we need to do is to generate some 200 unique id's for each user), how much feasible to use the identity constraint?. I could put this suggestion to DBA, but I just want to know so that I can discuss more.

  12. #12
    Join Date
    Dec 2002
    Posts
    134
    Yes, indentity are not very slow. No, identity is slower then well-implemeted custom generator. If you do not beleive - build a test
    How much slower - it depends on application and db access you use.

    Let's assume we are in ideal world - we use CurId++ to generate id (possible in case of one application). You understand that with identity you have overhead of identity (ok it's small if you increase the cache parameter) plus overhead of extra select statement - values identity_val_local()

    Plus you coud not use identity with batch/array (the name depends on access you use) insert

    2WizardOfGermany
    I read the articles you mentioned, and the test they did are not very fair
    The result of the test - yes, poorly implemeted id_generator is slower then identity.
    I think it's stupid idea to update just to get 1 id (to make the test a little bit more compatible, they need to change cache to 1)

  13. #13
    Join Date
    Mar 2003
    Posts
    69

    Max +1

    Hi,
    we had a discussion with one of my DBA now. He is saying some suggestion like why don't we use " insert with max+1 keeping a index desc order for the particular field. And while selecting, select max which will return the maximum value. How much feasible this approach???.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, it's feasible

    but without a lock, between the select max and the insert, you are dead

    if you don't lock, you will have bad data

    if you do lock, you will have bad performance

    conclusion: don't use select max method


    rudy

  15. #15
    Join Date
    Mar 2003
    Posts
    69
    well,
    typically speacking that's true. But imagine a scenario like the bellow. Actually our application is a Microsoft COM+ component. So in my component method, I will insert using max + 1 and immediately select the max record. This means still I am in the same connection. So I should get the exact record?. Is that make sense?

Posting Permissions

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