Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: how to make DB2 as case in sensitive?

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    May 2006
    Posts
    82
    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..!
    Vinay,

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2006
    Posts
    13
    is there no other way to make the complete database as case in sensitive?
    instead of using this lcase function regularly?

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Nov 2006
    Posts
    13
    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

  9. #9
    Join Date
    May 2006
    Posts
    82
    You may get very few answers here.
    (sometimes wrong answers too for Oracle - )

    Please try @ http://www.dbforums.com/forumdisplay.php?f=4

    This is the only link i'm aware of -
    Vinay,

Posting Permissions

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