Example of DOCID ANDing access (ACCESSTYPE='DI')

Two XMLEXISTS predicates that are connected with AND might be eligible for DOCID ANDing access.

Begin program-specific programming interface information.

Both predicates must have an eligible XML index.

The following query retrieves all XML documents from the INFO column of the CUSTOMER table for a customer whose zip code is 95141 and whose street name, when converted to uppercase, is BAILEY AVE.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/address[@zip=”95141” 
 and fn:upper-case(street)="BAILEY AVE"]'
 PASSING CUSTOMER.INFO as "x")

The following index matches the first predicate.

CREATE INDEX CUST_ZIP_STR ON CUSTOMER(INFO)
	GENERATE KEY USING XMLPATTERN '/customerinfo/address/@zip' 
  AS VARCHAR(10)

The following index matches the second predicate.

CREATE INDEX CUST_STREET_UPPER ON CUSTOMER(INFO)
 GENERATE KEY USING XMLPATTERN '/customerinfo/address/street/fn:upper-case(.)'
 AS VARCHAR(50)

Db2 uses DOCID list access (DX) to get the DOCID lists for the individual predicates, and then uses DOCID ANDing access (DI) to get the intersection of DOCID lists after an index scan of CUST_ZIP_STR and CUST_STREET_UPPER.

An excerpt from the The PLAN_TABLE output for the query looks like this:

Table 1. Example of columns of PLAN_TABLE for DOCID ANDing access
PLAN NO	ACCESS TYPE	MATCH COLS	ACCESS NAME	MIXOP SEQ
1	M	0		0
1	DX	1	CUST_ZIP_STR	1
1	DX	1	CUST_STREET_UPPER	2
1	DI	0		3

End program-specific programming interface information.