IBM Mainframe Forum

Hi All,

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



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

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.

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

Cheers
Steve.

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.

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

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. . .

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

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. . .

In DB2 9 you could create a unique index on upper(SLNO) thus enforcing uniqueness.

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