Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Question Unanswered: DB2 9.7 LIKE operator issue

    I have a table T:
    create table T(C1 VARCHAR(64));

    and some records in it:
    010/a
    010/b
    011/c
    012/d

    and create index on c1.

    I want records which begin with '010'.

    Now, two query:
    (1) select * from t where c1 like '010%';
    (2) select * from t where c1>='010' and c1 < '011';
    have the same result:
    010/a
    010/b

    My question is:
    (1) How does DB2 compare two strings? The comparison is related to codepage?
    (2) Is query (2) a optimized way of query (1)? If not, can someone give a record which appears in the result of query (2) but not begins with '010'?
    (3) How does query (2) utilize index?

    I am from China. Thank you for your reply.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    (1) It depends on your version and platform. DB2 z/OS has does a simply binary comparison and code page conversions occur during bind-in/bind-out of values. For DB2 LUW, I am not sure - I'd expect the same to happen but maybe different collations come into play.

    (2) Yes, both queries should be semantically identical.

    (3) Query (2) will exploit an index in the usual way: it does a range-search finding the lower/upper keys and scans the index leaf pages for all matching index entries.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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