Results 1 to 5 of 5
  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
    144
    Provided Answers: 9
    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
    241
    Provided Answers: 35
    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.

  4. #4
    Join Date
    Feb 2011
    Posts
    43
    Provided Answers: 1
    You could try WHERE instead of JOIN. I do not know if it would be any faster, but it might be worth a try.

    select
    trim(a.usr || '|' || a.No || '|' || trim(b.pin) || '|' || coalesce(a.src,'')
    from
    xxx.veH a,
    xxx.lnr b
    where
    lower(a.X_USRNAME) = lower(b.X_USRNAME)
    and
    a.flag = 'F'
    with ur

  5. #5
    Join Date
    Oct 2007
    Posts
    144
    Provided Answers: 9
    Quote Originally Posted by Brian.Hart View Post
    You could try WHERE instead of JOIN. I do not know if it would be any faster, but it might be worth a try.

    select
    trim(a.usr || '|' || a.No || '|' || trim(b.pin) || '|' || coalesce(a.src,'')
    from
    xxx.veH a,
    xxx.lnr b
    where
    lower(a.X_USRNAME) = lower(b.X_USRNAME)
    and
    a.flag = 'F'
    with ur
    They would be the same.

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
  •