Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Question Unanswered: To make a non-key field unique

    Dear All,

    I encountered a tricky problem, a non-key field (string type) must be unique. It is with the format of YYYY-Order_No, while YYYY is current year and Order_No should be incremental. We use '2010' || '-' || 1+Select count (*) from Order_table to generate the string.

    The problem is, when there are two persons access to the query at the same time, the value will be duplicated. Any solution? As it is already a production database, we cannot change much on the schema.

    Thanks!
    XZ

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    create an unique index on that field.

    If two user try to insert the same value, the second one will fail and can ( automaticly ) recalculate the Orderno and retry the insert

  3. #3
    Join Date
    Apr 2002
    Posts
    84
    The problem is we have some historical data with null values in the field. Any solution?

    Thanks.


    Quote Originally Posted by umayer View Post
    create an unique index on that field.

    If two user try to insert the same value, the second one will fail and can ( automaticly ) recalculate the Orderno and retry the insert

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    in that case: create an UNIQUE WHERE NOT NULL index ...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "WHERE NOT NULL" is supported on DB2 for z/OS and iSeries.
    It is not supported on DB2 for LUW.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    We don't know on which product the OP is working. So if it is indeed DB2 LUW, one option would be to add a trigger that does the weak uniqueness checks.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you said you already have historical data in there and some of it is NULL. If you have more then one record where this data is NULL you are SOL as you can only have one NULL in your UNIQUE index. You would have no other choice but to do table redesign like it or not.

    Run a SQl to find out if you have more then one NULL. Sounds like you will.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    You can use something like this:

    Code:
    select char(year(current date)) || '-' ||char(coalesce(count (*), 0) + 1 ) 
    from Order_table
    Lenny

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by qxz View Post
    Dear All,

    I encountered a tricky problem, a non-key field (string type) must be unique. It is with the format of YYYY-Order_No, while YYYY is current year and Order_No should be incremental. We use '2010' || '-' || 1+Select count (*) from Order_table to generate the string.

    The problem is, when there are two persons access to the query at the same time, the value will be duplicated. Any solution? As it is already a production database, we cannot change much on the schema.

    Thanks!
    XZ
    i think you should had have 2 cols - year and order_number - and make order_number an indentity column. Will work much much faster. you wll not get dups. Select count (*) is bad in the first place - takes too long. And I agree with Cougar8000 - you need to re-design it.

    In order to keep the NULLs if there is more than one, you can
    - create another table with same structure but allowing NULLs in order_num col
    - move all records with NULLs to this table
    - alter original table to make order_number an identity col
    - create a view on both tables with union all
    - make users to use the view (if it has same name as original table they will not notice any difference )
    Last edited by MarkhamDBA; 02-22-10 at 12:49.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I totally missed it the first time. You are doing this for every new record?

    '2010' || '-' || 1+Select count (*) from Order_table
    How is your performance?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    we have one of those - instead of using identity col or a sequence, they created a trigger which finds max(col), adds 1 and inserts it into the table. performance is awful. we are going to change it to identitly column as soon as we can.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  12. #12
    Join Date
    Apr 2002
    Posts
    84
    Thanks for all the help.

    What should I do if there is some condition, for example, to get the number of orders of a particular product. How to optimize a query like:
    > select count(*) from Order_Table where product_id = 'abc'?


    Quote Originally Posted by MarkhamDBA View Post
    we have one of those - instead of using identity col or a sequence, they created a trigger which finds max(col), adds 1 and inserts it into the table. performance is awful. we are going to change it to identitly column as soon as we can.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    put an index on product_id

  14. #14
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    BTW if you insist on using '1+Select count (*)' to get a value for next order_no, I think it's better change it to 'max(order_no)' or you are skipping numbers because your count(*) counts NULL values too. For example, if you have 100 records and 10 of them are NULLs, your next order_no will be 101 (not 91) though your max order_no might be =90.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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