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

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).

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

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