Dear Friends,

by default DB2 is case sensitive? or case-in-sensitive?

how to make DB2 as case-in-sensitive?

that means, while retrieving data, it should identify 'Ind' , 'IND' , 'ind' as same...........like this

how can i make this collation?


thanks in advance

The data in DB2 is case sensitive. If you want select columns to be "case insensitive", I suggest that you create the table with this column to have another column that is derived from the first column and is either lcase or ucase of that column. Then you always do you queries against the derive column. For example:

Instead of:

create table cust (c_id int, name varchar(50))

select * from cust where lcase(name) = lcase('Jones')

you would have:

create table cust (c_id int, name varchar(50), l_name varchar(50))

create trigger BITRIG_cust NO CASCADE BEFORE INSERT ON cust
REFERENCING NEW as newdata FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET newdata.l_NAME = lCASE(newdata.NAME);
END

select c_id,name from cust where l_name = 'jones'

This way you can put indexes on the column l_name and get a performance boost.

HTH

Andy

sunsanvin,

You might have already noticed in this forum that the first input we would expect is the Environment details (platform,version...). No matter how simple the query is. Please give us this details.

Coming to Andy's solution, and assuming DB2 on Z/os. (for point 1 )

1.>
This may not work when LOAD utility is run with SHRLEVEL NONE.
Triggers are not activated when data is loaded using LOAD SHRLEVEL NONE.

2.> Data may not be consistent in the Derived Column.
I think we may need UPDATE trigger as well. Consider the situation,
A row is inserted and some time later it was updated to something else. An update trigger would be needed to put the derived column is sync with the actual column, If at all the table/column is updatable as per the functionality.
I'm not saying Andy missed this point, I just wanted to consider this situation.

3.> I'm not sure about the REORG utility. I think it would also depend on the SHRLEVEL parameter. Not sure though..!

Actually, I was assuming DB2 for LUW. And yes, there is a need for a BEFORE UPDATE trigger. I was just giving an example.

Andy

To use Andy's fundamental approach and address Vinay's concerns, you can define l_name as

l_name varchar(50) GENERATED ALWAYS AS lcase(name)

When inserting/updaing data (and IMPORTing) the l_name is generated automatically.

If you are LOADing, following the load you will have to use SET INTEGRIY statement

REORG should have no specific impact here

Sathyaram

is there no other way to make the complete database as case in sensitive?
instead of using this lcase function regularly?

No, not in DB2. SQL is a case-sensitive language - as are many other programming languages - and you have to say explicitly when to do case-insensitive comparisons.

Dear Friends,
the given solution by is an excellent one. I got much confidence that we can easily start migration in DB2. is it possible to do the same thing in oracle? if so, can you please give me the syntax and related functions?

thank you very much

You may get very few answers here.
(sometimes wrong answers too for Oracle - )

Please try @ http://www.dbforums.com/oracle/

This is the only link i'm aware of -