Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Location
    Cape Town
    Posts
    2

    Question Unanswered: XML Index on DB2 ZOS

    Hi All
    Question refers to Db2 V9.1 on Zos 1.11/1.12

    Here are two indexes (as an example) created from a XML Col, using a pattern.

    create index idx2 on st_data(statementdoc) generate key using
    xmlpattern '/st/@account' as sql varchar(16);

    create index idx3 on st_data(statementdoc) generate key using
    xmlpattern '/st/li/ref' as sql varchar(10);


    Do any of you know how to define an index using multiple Fields.
    Either 2 XML fields. (ie a pattern with two fields).
    Or Normal Col and a XML pattern (ie Mixed).

    It would be to expensive to scan all the doc ref 101 for the matching account or to scan all the docs of an account for ref 101.

    I need to be able to build a composite index on Account and Ref.
    Account can also be found in a standard Db2 column, ref is only available in the xml DOC.

    Regards
    Andre

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ajagerCT View Post
    Do any of you know how to define an index using multiple Fields.
    Either 2 XML fields. (ie a pattern with two fields).
    Or Normal Col and a XML pattern (ie Mixed).
    This seems to be impossible.
    The SQL Reference Guide does not explicitly exclude the possibility of two columns combined with the "generate key using xmlpattern", but since the XML specification is "global", there seems to be no way to indicate to which of the two specified columns it belongs, neither seems there be a way to pass two such xmlpatterns.

    I tried out a few syntax variants, but all gave error SQLCODE = -20304:
    INVALID INDEX DEFINITION INVOLVING AN XMLPATTERN CLAUSE OR A COLUMN OF DATA TYPE XML. REASON CODE = 2

    Did you pose the question on the IDUG DB2 list? (LISTSERV 16.0 - DB2-L List at WWW.IDUG.ORG) Chances are that some DB2 insiders could tell you whether this has been "fixed" in version 10, or has been considered for vNext... (All I can say is that the v10 syntax for CREATE INDEX has not changed with respect to XMLPATTERN.)

    I can only think of one option, in your case, to work around this restriction: concatenate the two xml fields "ref" & "account" (both in the index definition, and in your WHERE XMLEXISTS condition).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    May 2011
    Location
    San Jose, CA, USA
    Posts
    4
    An XML index can only be defined on a single XML element or attribute. XML indexes cannot have a composite key that consists of multiple fields. This restriction is the same in DB2 for z/OS (version 9 and 10) and in DB2 LUW.

    You could consider extracting a few selected XML fields into relational columns of the same or a different table, and then define relational multi-column indexes.

    - Matthias

    ------------------------------------------------
    nativexmldatabase.com
    DB2 pureXML

  4. #4
    Join Date
    May 2011
    Location
    Cape Town
    Posts
    2

    Angry Not very useful for large amount of data.

    Thanks Guys
    That is the same conclusion I have come to after working through the DB2 XML wiki.

    I can build a separate Table holding the repeating keys and index that table but keeping that data in sync is a lousy option even using DB2 bulk rowset insert for the entire statement structure.

    Considering the structures that DB2 creates for XML indexes and or side tables, it may cheaper anyway, to do it the non xml method.

    Regards
    Andre

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
  •