pros, cons, risk or danger of 'Making DB2 Case-Insensitive'

comerun · #1 (**permalink**) 11-10-11, 00:25

Hello everyone,

we are using both DB2 and Oracle (db2 main) at our company.

My aim is to have some data (given contact names) to be shown on the screen ordered by the names BUT case insensitive. It is currently case sensitive and returns the contact names like; Bob, Caren, Don, ben, cameron, damien ..... whereas I want to have them like, Ben, Bob, cameron, Caren, damien, Don....

I did some research and found out the following which is a database level change offered since db2 9.5:

CREATE DATABASE testdb
USING CODESET UTF-8 TERRITORY US
COLLATE USING UCA500R1_LEN_S2

Now I still not too sure how it will affect all the platform.
I can not think of any risk regarding other db tables other than something as an example, contact_table which will work fine.

I just need to get some feedback on how risky can this change be.
Have you had any experience dealing with some problems after this change?
Can it be risky in any way?

Is there any other solution on this (except using UCASE or LCASE cause I still want to show the result as they are on the screen and not all lower or upper case)?

and the last question, does Oracle support the same thing?

I'd appreciate your feedback and helps on this topic.

Thanks,

dr_te_z · #2 (**permalink**) 11-10-11, 06:56

An extra column e.g.

Code:

create table hello_world
  (  name_display varchar (32) not null
   , name_srtseq  varchar (32) generated asways as ucase(name_display)
  )
.
.
 select name_display from hello_world order by name_srtseq asc

Straight & simple.

tonkuma · #3 (**permalink**) 11-10-11, 07:46

An example: Ordered by case-insensitive and display as they are original case sensitive.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES
  'Bob' , 'Caren'   , 'Don'
, 'ben' , 'cameron' , 'damien'
)
SELECT name
 FROM  test_data
 ORDER BY
       UCASE(name)
;
------------------------------------------------------------------------------

NAME   
-------
ben    
Bob    
cameron
Caren  
damien 
Don    

  6 record(s) selected.

Marcus_A · #4 (**permalink**) 11-10-11, 08:59

Quote:

Originally Posted by tonkuma

An example: Ordered by case-insensitive and display as they are original case sensitive.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES
  'Bob' , 'Caren'   , 'Don'
, 'ben' , 'cameron' , 'damien'
)
SELECT name
 FROM  test_data
 ORDER BY
       UCASE(name)
;
------------------------------------------------------------------------------

NAME   
-------
ben    
Bob    
cameron
Caren  
damien 
Don    

  6 record(s) selected.

That works for order by, but not for things like searching, especially searching using a matching index scan (which would rule out using functions like UCASE, LCASE, etc in the predicate).

dr_te_z explained the most commonly used solution, although I would use GENERATED BY DEFAULT so it is easier to do db2move, export/import, etc.

tonkuma · #5 (**permalink**) 11-10-11, 09:15

Marcus A,
THank you!

I agree with you and dr_te_z.

Internet Services

Web Development

Digital Marketing

Consumer Tech