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

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

The problem is we have some historical data with null values in the field. Any solution?

Thanks.

in that case: create an UNIQUE WHERE NOT NULL index ...

"WHERE NOT NULL" is supported on DB2 for z/OS and iSeries.
It is not supported on DB2 for LUW.

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.

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.

You can use something like this:

Lenny

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 )

I totally missed it the first time. You are doing this for every new record?

How is your performance?

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.

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'?

put an index on product_id

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.