Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: It really exists,but I got nothing when select by using the underscore character.

    The following record exists in tbl01
    Code:
    key_txt
    x'F0F2F1F8F0F0F0F6001A021302A80E2FC3C3F0F1404040404040'
    When using the following stmt:
    Code:
    select * from tbl01 where substr(key_txt,7, 8 ) = x'001A021302A80E2F'
    I got:
    Code:
    KEY_TXT
       02180006........CC01
    But when select by using the underscore character:
    Code:
     select * from tbl01 where key_txt like 
     x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'
    I got nothing.

    why,who can help explaining?

    Any hints is welcome.Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are working with DB2 z/OS because your strings appear to be in EBCDIC, right? Have you made sure that your application is also connecting to DB2 in EBCDIC or due you use a different code page perhaps?

    Is there a specific reason why you work on binary data instead of regular strings?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tiger_wen
    select * from tbl01 where key_txt like
    x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F140404040 4040'
    Shouldn't that be
    Code:
    select * from tbl01 where key_txt =
     x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'
    or otherwise have a wildcard ("%" or "_") in the right hand side.
    Note that the wildcard must be there literally, i.e., x'6D' is NOT a wildcard!
    Moreover, you may need a "%" at the very end for a match to be found.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Nov 2006
    Posts
    4
    Thanks all for your kindly help on this.

    I should make it more clear:
    If I got the key_txt like
    Code:
    key_txt
    x'F0F2F1F8F0F0F0F2001A0213000601B9C3C3F0F1404040404040'
    It shows me the same results by using
    Code:
    select * from ... where substr(key_txt,....... =
    &
    Code:
    select * from ....where key_txt like '0218____....
    Why?Does anyone know something about ASCII LIKE or UNICODE LIKE.
    Is the ASCII LIKE or UNICODE LIKE the cause?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you are going about this in the wrong manner. You should convert your key_text to hex and then compare it to your string. Here is a query I ran against syspackage table that works fine.

    SELECT NAME, HEX(NAME) FROM SYSIBM.SYSPACKAGE
    WHERE COLLID = 'mycollid'
    AND LOCATION = ''
    --AND NAME = 'MRM170'
    AND HEX(NAME) LIKE '%D9D4F1F7%'

  6. #6
    Join Date
    Nov 2006
    Posts
    4
    Quote Originally Posted by dav1mo
    I think you are going about this in the wrong manner. You should convert your key_text to hex and then compare it to your string. Here is a query I ran against syspackage table that works fine.

    SELECT NAME, HEX(NAME) FROM SYSIBM.SYSPACKAGE
    WHERE COLLID = 'mycollid'
    AND LOCATION = ''
    --AND NAME = 'MRM170'
    AND HEX(NAME) LIKE '%D9D4F1F7%'
    Thanks for your reply!
    [DB2 v7 ENCODING SHEMA 0935]
    If use LIKE to select records for table,there should no characters such as
    x'0E',x'0F',x'6C',x'6D' in pattern expression -- one of the LIKE parm.

    In my original post,
    Support:We have two record in tbl01
    HEX(key_txt)
    001A021302A80E2F
    001A021302A8252F

    If change the x'001A021302A80E2F' to x'001A021302A8122F',and keep
    other characters the same.The select stmt with like parm will give us a
    record.

    If we use
    Code:
     select * from tbl01 where key_txt like 
     x'001A021302A86D2F'
    It only gives me the x'001A021302A8252F' record.

    Thanks everyone so.Thanks a lot.
    Last edited by tiger_wen; 12-25-08 at 01:35.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    DB2 v7 ENCODING SCHEMA 0935
    CCSID 935 is a MIXED encoding schema. With DB2 v8, your problem would be simplified since DB2 will convert the character data in your program from the encoding declared by your compiler into 935, hence you don't need to bother with the hexadecimal representation.
    With a MIXED encoding scheme, you switch between 2-byte characters and 1-byte characters with the SHIFT-IN and SHIFT-OUT characters (x'0E' and x'0F'). Otherwise said, these two bytes are no characters so indeed they don't match the "_" pattern in a LIKE.
    The two wildcards are "%" (x'6C') and "_" (x'6D'). To search for these characters "as is" in a LIKE, you need to escape them, e.g. by preceding them with x'E0', but then also adding
    Code:
    ESCAPE x'E0'
    after the LIKE.

    Coming back to your original question:
    The following record exists in tbl01
    key_txt = x'F0F2F1F8F0F0F0F6001A021302A80E2FC3C3F0F140404040 4040'
    which has substr(key_txt,9,8) = x'001A021302A80E2F'
    This indeed starts with the 8 EBCDIC characters '02180006', then 8 other bytes, then the EBCDIC characters 'CC01 ' (ending in 6 blanks).
    BUT if the data is encoded with CCSID=935, x'0E' and x'0F' have a special meaning. One of those 8 bytes is the shift-out character (x'0E'), which means that at this point double-byte interpretation begins, i.e., x'2FC3' is one character, x'C3F0' is one character, etc. There is no shift-in (x'0F') to end double-byte interpretation.
    If the data has no codepage ("FOR BIT DATA"), or a single-byte codepage, x'0E' is a "normal" byte and no interpretation is done. Apparently, in your situation, this is the case. So in any "WHERE key_txt = x'......'" also the right hand side is interpreted as "FOR BIT DATA", which explains why you find your row.

    Code:
    select * from tbl01
    where key_txt like x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'
    Here, the LIKE forces *textual* interpretation, since at BIND time the characters (not bytes) "%" and "_" (i.e., in codepage 935, these are x'6C' and x'6D') must be recognised.
    For a key_txt without the byte x'0E' this works: the four x'6D' are four wildchars. But with the x'0E', double-byte interpretation starts, i.e., the byte x'0E' in key_txt is compared with the double-byte x'2FC3', and these are different.
    Try the following, and your row should be found:
    Code:
    select * from tbl01
    where key_txt like x'F0F2F1F86D6D6D6D001A021302A8D62FC3C3F0F1404040404040'
    i.e., replace any occurrence of x'0E' or x'0F' by the wildcard x'6D'. (Which of course is a bit too permissive, so maybe some additional rows would be returned, which you can filter out by adding the condition AND substr(key_txt, 14,1) = x'0E').
    When your search string has an x'6D' or x'6E', precede it in the LIKE argument with x'E0' and add "ESCAPE x'E0'" at the end:
    Code:
    select * from tbl01
    where key_txt like x'F0F2F1F86D6D6D6D001A021302A8D62FC3C3F0F14040404040E0D6'
    ESCAPE x'E0'
    would search for a "_" as the last character.
    Replace x'E0' by anything else when your search pattern would already have an x'E0'.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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