Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    27

    Unanswered: DB2 INDEX when using LIKE operator

    Hi I know that in Oracle there is such thing called DOMAIN INDEX. How but in DB2?

    I have statement in my large table:
    SELECT * from TABLE where (upper(COLUMN1) like '%HGW%'))

    Regular normal INDEX on COLUMN1 will not be helpful because of UPPER and LIKE.
    What is the syntax to create some powerful index which will have effect on this UPPER and LIKE statements to get fast result of this query?
    I will appreciate example and help.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Add a new column that is an UPPER version of the existing column, and which is GENERATED ALWAYS.

    If you can get rid of first "%" in the LIKE, index access will be very fast (you can keep the trailing "%").
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2011
    Posts
    27
    Hi Marcus. Unfortunately I can not change (add) database columns. It is used in some IBM application etc. long story....
    Also I cant get rid of first %.

    Does DB2 has solution for this or not?
    Thank you
    Last edited by cankovicv; 10-10-12 at 12:12.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by cankovicv View Post
    Hi Marcus. Unfortunately I can not change (add) database columns. It is used in some IBM application etc. long story....
    Also I cant get rid of first %.
    Adding a new column to an existing database should not adversely affect the application, unless they use "select *" in their code. I would contact IBM and ask them to fix the app.

    Quote Originally Posted by cankovicv View Post
    Does DB2 has solution for this or not?
    Thank you
    I don't know of any other solution, maybe someone else does.
    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
    May 2011
    Posts
    27
    Anybody please?
    It must be some solution..DB2 is not weaker comparing with Oracle

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by cankovicv View Post
    Anybody please?
    It must be some solution..DB2 is not weaker comparing with Oracle
    Have you investigated

    http://www.ibm.com/developerworks/data/tutorials/dm-0810shettar/index.html
    Last edited by lelle12; 10-11-12 at 15:57.

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
  •