Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: DB2 LUW Text Search - search using special characters

    Hello,
    I have installed DB2 LUW 9.7.4 with Text Search option on Windows box and I'm currently 'struggling' with full text search queries containing special characters, especially "_" (underscore). I'm searching agains XML structure inside XML column using xpath.
    The "FILENAME" XML tag contains records like "abcdefg_h.pdf".

    This is my query:
    SELECT * FROM MY_XML_TABLE WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[. contains("abcdefg\_h*") ]''')=1
    go

    Here's the result:
    Error occurred during text search processing on server "localhost" using index "SCHEMANAME.TXTIND_DOCXML". The error message is ""IQQS0032E The query cannot be proces".. SQLCODE=-20423, SQLSTATE=38H10, DRIVER=3.53.71

    From the db2diag.log I can see that the command syntax used in wrong:
    "IQQS0032E The query cannot be processed because it has incorrect syntax.
    Causes of the problem:
    IQQG0020E com.ibm.siapi.SiapiException: Message0: SEVERITY_ERROR: Message ID: [] Resource Bundle: [] Message Text: []
    IQQG0020E com.ibm.es.nuvo.search.query.parser.TokenMgrError: Lexical error at line 1, column 48. Encountered: "_" (95), after : "\'/DOCUMENT/FILENAME[. contains(\"abcdefg\\""

    As you can see, I use "\" to escape "_" character (according to TS documentation) and wildcard (*) search, but this obviously doesn't work as I expected (I also get the same error using the term without "*" (e.g. "abcdefg\_h.pdf")). Query with "abcdefg_h.pdf" works fine.

    What I'm I doing wrong?
    I would really appreciate some help on this issue.

    Thank you in advance.
    Best regards,
    Domen
    Last edited by domenm; 07-17-12 at 09:48.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Don't you also need to include the ESCAPE clause to identify the escape character?
    DB2 Database for Linux, UNIX, and Windows r7/topic/com.ibm.db2.luw.admin.nse.topics.doc/doc/t0 052180.html

  3. #3
    Join Date
    Jul 2012
    Posts
    16
    Quote Originally Posted by db2mor View Post
    Don't you also need to include the ESCAPE clause to identify the escape character?
    DB2 Database for Linux, UNIX, and Windows r7/topic/com.ibm.db2.luw.admin.nse.topics.doc/doc/t0 052180.html
    Hello db2mor,

    Thank you for your reply. At first I've also thought about the ESCAPE keyword, but I believe this is only valid for NSE, but not for DB2 Text Search. The only way, as I see, is to use "\" to escape special chars from search term.

    Please, see:
    http://www.ibm.com/developerworks/da...ion/index.html


    Thanks anyway.
    BR,
    Domen
    Last edited by domenm; 07-17-12 at 09:46.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    OK I see now what you mean.
    Is something eating that escape character?

  5. #5
    Join Date
    Jul 2012
    Posts
    16
    Looks like it. Maybe xpath doesn't allow to use this kind of search form?

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    At this page:
    Search syntax for XML documents - IBM DB2 9.7 for Linux, UNIX, and Windows

    it says:
    DB2 Text Search features such as phrases, wildcards, and synonyms are not supported in XML search queries.

  7. #7
    Join Date
    Jul 2012
    Posts
    16
    OK, I see what doc says. But still, if I perform a query like the following one, I get the result(s) correctly:
    SELECT * FROM MY_XML_TABLE WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[ contains("abcdefg*") ]''')=1;

    Only when underscore "_" char is in play, the query doesn't return anything. Here's the example:
    SELECT * FROM DOCUMENTS_XML WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[ contains("abcdefg_*") ]''')=1;

    Strange.

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
  •