Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    5

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

    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,

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Marcus A,
    THank you!

    I agree with you and dr_te_z.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •