DB2 10.5 for Linux, UNIX, and Windows

Examples of using case insensitive XML indexes

To speed up queries that search string data, you can use the fn:upper-case() function to create indexes that store values as case-insensitive entries.

Creating case-insensitive indexes

This example demonstrates how to create a table with an XML column, insert data into it, and create case-insensitive indexes.

First, create the table called CLIENTS, which has a column called CONTACTINFO that is of type XML:

CREATE TABLE clients (
  ID            INT PRIMARY KEY NOT NULL,
  NAME          VARCHAR(50),
  STATUS        VARCHAR(10),
  CONTACTINFO   XML
);

Insert two records into the CLIENTS table:

INSERT INTO clients VALUES('0092', 'Johny Peterson', 'Standard',
'<Client>
    <address type="permanent">
         <street>8734 Zuze Ave.</street>
         <city>New York</city>
         <state>New York</state>
         <zip>95443</zip>
     </address>
</Client>');

INSERT INTO clients VALUES('0093', 'Rose Locke', 'Golden',
'<Client>
    <address type="PERMANENT">
         <street>1121 Oxford Street</street>
         <city>Albany</city>
         <state>new york</state>
         <zip>19232</zip>
     </address>
</Client>');

You can create a case-insensitive index, for example called clients_state_idx, on the /Client/address/state path. The first parameter of the fn:upper-case() function must always be a context item expression (.).

CREATE INDEX clients_state_idx ON clients(contactinfo) 
   GENERATE KEYS USING XMLPATTERN '/Client/address/state/fn:upper-case(.)' 
   AS SQL VARCHAR(50);

You can also create case-insensitive indexes on attributes. For example, you can create the index, called client_address_type_idx, on the address attribute, type, /Client/address/@type path.

CREATE INDEX client_address_type_idx ON clients(contactinfo) 
   GENERATE KEYS USING XMLPATTERN '/Client/address/@type/fn:upper-case(.)' 
   AS SQL VARCHAR(50);

For both the clients_state_idx index and the client_address_type_idx index, the index key values are stored in the upper-case form in the US English encoding set. For example, for the first data record that was inserted earlier, the value associated with the /Client/address/state path is New York, but is stored as NEW YORK. The value associated with the /Client/address/@type attribute is the lowercase string, permanent, but will be stored as PERMANENT.

Running queries that use case-insensitive indexes

A case-insensitive index is considered by the optimizer only when the index pattern and predicate satisfy the following conditions:

The path of the context step in the GENERATE KEYS USING XMLPATTERN clause of the CREATE INDEX statement matches the XML path in the query predicate.
The locale name, if specified in the CREATE INDEX statement, matches the locale specified by the fn:upper-case() function in the query predicate.
The first parameter of fn:upper-case() used in the query predicate is a context item expression (.).

For the following query, the optimizer might choose to use the case-insensitive index, clients_state_idx, if it exists. Instead of performing a table scan to find records where the state element has the value New York (in either upper or lower case), the optimizer can choose to scan the clients_state_idx index, if it involves less work.

XQUERY db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')
   /Client/address/state[fn:upper-case(.)="NEW YORK"];

--------------------------------
<state>New York</state>
<state>new york</state>

2 record(s) selected.

Specifying the locale parameter

When you create a case-insensitive index, you can use the optional locale parameter of the fn:upper-case function. For example, the following statement creates an index on the address attribute, type (with the path /Client/address/@type) for the tr_TR locale:

CREATE INDEX client_address_type_idx_tr ON clients(contactinfo) 
   GENERATE KEYS USING XMLPATTERN '/Client/address/@type/fn:upper-case(., "tr_TR")' 
   AS SQL VARCHAR(50);

Note that if you do not correctly specify the locale string, for example by omitting the surrounding quotes, a default value is used for the locale name. To verify the locale used during index creation, you can use the db2look command or the DESCRIBE statement. For example: DESCRIBE INDEXES FOR TABLE CLIENTS SHOW DETAIL.

The optimizer might choose to use the client_address_type_idx_tr index only if the query also specifies the locale, tr_TR, with fn:upper-case() in the query predicate. For example:

SELECT id FROM clients client1
  WHERE XMLEXISTS('$XMLDOC/Client/address/@type[fn:upper-case(., "tr_TR")="PERMANENT"]' 
  PASSING client1.contactinfo as "XMLDOC")

ID
-----------
         92
         93

  2 record(s) selected.

For a query such as the following example, the index client_address_type_idx_tr is not used, because it specifies a different locale:

SELECT id FROM clients client1 
  WHERE XMLEXISTS('$XMLDOC/Client/address/@type[fn:upper-case(., "en_US")="PERMANENT"]' 
  PASSING client1.contactinfo as "XMLDOC")

ID
-----------
         92
         93

  2 record(s) selected.