Defining case insensitive Primary Key in DB2



IBM's flagship relational database management system

Defining case insensitive Primary Key in DB2

Postby Neelu_Soni » Fri Oct 23, 2009 12:30 am

Hi All,

I have a DB2 table whose primary key SLNO is 5 character alphanumeric with the following data.

SLNO                   SLTYPE
F1aCr                  C
F1ACR                  C
F1ABB                  M


Currently SLNO is defined as case sensitive, i.e. F1aCr is not equal to F1ACR. I have a requirement to make the SLNO case insensitive, i.e. F1aCr should be treated same as F1ACR.

One option suggested to me was to define a INSERT/UPDATE trigger to convert all lower case characters to uppercase while inserting or updating values in table. But this might impact performance as every time this table is updated the trigger will be executed.

Could someone suggest me other possible ways to implement this.

Regards,
Neelu Soni
Neelu_Soni
 
Posts: 5
Joined: Thu Oct 22, 2009 11:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Defining case insensitive Primary Key in DB2

Postby dick scherrer » Fri Oct 23, 2009 1:50 am

Hello and welcome to the forum,

You might consider rejecting lower-case values when they are entered rather than at the time of insert/update.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Defining case insensitive Primary Key in DB2

Postby swd » Fri Oct 23, 2009 4:38 am

Have you tried SELECT UPPER(SLNO), SLTYPE FROM....... ?

Cheers
Steve.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Defining case insensitive Primary Key in DB2

Postby dick scherrer » Sat Oct 24, 2009 12:49 am

Hello,

From the little info posted, i am confused about "logical duplicates". . .
F1aCr should be treated same as F1ACR
The SELECT UPPER would find both, but i'm not sure that they should both be in the table. . . :?

If i understand (and i may not), having both seems to be invalid.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Defining case insensitive Primary Key in DB2

Postby Neelu_Soni » Mon Oct 26, 2009 11:27 pm

DB2 allows the a unique column to have values like
F1ACR
F1aCR
F1Acr
F1AcR
f1acr
All of the above values are different and will not be considered as duplicate. I do not know much about it but they say that IBM refers to this as "Shared-weight vs. Unique-weight".

I am not looking for selecting upper case values. So SELECT UPPER is out of scope.
Checking for case while inserting/updating at programming level is a possibility for any values that are entered new. But I am also concerned about the data that is currently present in Production table. How can that be updated.

I prefer having this at the database level, because logically thinking SLNO being a primary key should not allow duplicates and all the above mentioned combinitions should be considered similar to F1ACR and rejected by database. I am looking for solution like a setting in DB2 or defining a constraint.

Any suggesstions are appreciated.

Neelu Soni
Neelu_Soni
 
Posts: 5
Joined: Thu Oct 22, 2009 11:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Defining case insensitive Primary Key in DB2

Postby dick scherrer » Tue Oct 27, 2009 12:13 am

Hello,

Suggest you first identify any existing "duplicates" and decide how they should be corrected. A new key might be assigned for each duplicate, duplicates might be removed, the data from "duplicate" rows might be combined, whatever the business rules dictate.

Once the "duplicates" have been handled, suggest you unload the table, convert everything to upper-case, change the application to only insert upper-case values, reload the table and go forward. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Defining case insensitive Primary Key in DB2

Postby Neelu_Soni » Thu Oct 29, 2009 7:54 pm

Hi,

The key is being used extensively over the application. Hence changing the application is a huge impact and effort. Hence I am looking to handle it at the Database level and not the programming level.

I have been looking over the net for solution for this. Some post suggested setting
SRTSEQ = *LANGIDSHR

Does anyone know what does this mean and how it works.

Neelu Soni
Neelu_Soni
 
Posts: 5
Joined: Thu Oct 22, 2009 11:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Defining case insensitive Primary Key in DB2

Postby dick scherrer » Fri Oct 30, 2009 1:25 am

Hello,

The key is being used extensively over the application.
There should not be many places where a new row is inserted. If these bits of code are changed to force UPPER case data, the problems should go away (unless i misunderstand).

Hence changing the application is a huge impact and effort.
Not a good reason to continue to use an application that will most likely cause problems due to the way it is implemented. The cheap way is often more costly later and this situation soulds like it can cause all sorts of problems for anything that uses this table.

Regardless of the attraction of doing no real work to correct the problem, the problem shoud be corrected. Also, when we don't want to do something, we often make it out to be more difficult than it needs to be. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Defining case insensitive Primary Key in DB2

Postby GuyC » Tue Nov 03, 2009 8:15 pm

In DB2 9 you could create a unique index on upper(SLNO) thus enforcing uniqueness.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Defining case insensitive Primary Key in DB2

Postby dick scherrer » Wed Nov 04, 2009 12:41 am

Hi Guy,

If a CREATE was attempted for the "upper(SLNO)" index, would it raise an error about the already existing duplicates? Do the duplicates need to be resolved beforehand?

Thanks,

d
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post