Results 1 to 3 of 3
  1. #1
    Join Date
    May 2017
    Posts
    1

    Unanswered: Join the Data between two tables ignoring the case sensitivity

    Code:
    select trim(a.usr || '|' || a.No || '|' || trim(b.pin) || '|' || coalesce(a.src,'') from xxx.veH a inner join xxx.lnr b on lower(a.X_USRNAME)=lower(b.X_USRNAME) and a.flag='F' with ur
    The Above Query taking very long time to fetch the data.we have about 2Million Data

    When i remove the lower in join condition the results are very faster
    Code:
    select trim(a.usr || '|' || a.No || '|' || trim(b.pin) || '|' || coalesce(a.src,'') from xxx.veH a inner join xxx.lnr b on (a.usr)=(b.usr) and a.flag='F' with ur
    How to ignore the case sensitivity.
    I have tried the below one.. but still it is taking time
    Code:
    select trim(a.usr || '|' || a.No || '|' || trim(b.pin) || '|' || coalesce(a.src,'') from xxx.veH a inner join xxx.lnr b on on COLLATION_KEY_BIT(a.usr,'UCA500R1_LEN_S2')=COLLATION_KEY_BIT(b.usr,'UCA500R1_LEN_S2') and a.flag='F' with ur
    We can't create index using lower(usr) and also we can't make the entire database to ignore the case sensitive.

    Do we have any other options?

  2. #2
    Join Date
    Oct 2007
    Posts
    118
    Provided Answers: 6
    an index on expression is the best option, guess you are on back level version is why you can't use it? I have seen it done using a before insert trigger, where you add on a column called lower_usr and you have a trigger set lower_usr = lower(usr) and have an index on this column.

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    208
    Provided Answers: 31
    If you are at the older DB2 version than 10.5, you can add a generated always field with an expression 'lower(usr)' to both tables and create index on this field on both tables.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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