DB2 10.5 for Linux, UNIX, and Windows

Inexact matching

During compilation, if there is an active optimization profile, the compiling statements are matched either exactly or inexactly with the statements in the optimization profile.

Inexact matching is used for flexible matching between the compiling statements and the statements within the optimization profile. Inexact matching ignores literals, host variables, and parameter markers when matching the compiling statement to the optimization profile statements. Therefore, you can compile many different statements with different literal values in the predicate and the statements still match. For example, the following statements match inexactly but they do not match exactly:

select c1 into :hv1 from t1 where c1 > 10

select c1 into :hv2 from t1 where c1 > 20

Inexact matching is applied to both SQL and XQuery statements. However, string literals that are passed as function parameters representing SQL or XQuery statements or statement fragments, including individual column names are not inexactly matched. XML functions such as XMLQUERY, XMLTABLE, and XMLEXISTS that are used in an SQL statement are exactly matched. String literals could contain the following items:

A whole statement with SQL embedded inside XQuery, or XQuery embedded inside an SQL statement
An identifier, such as a column name
An XML expression that contains a search path

For XQuery, inexact matching ignores only the literals. The following literals are ignored in inexact matching with some restrictions on the string literals:

decimal literals
double literals
integer literals
string literals that are not input parameters for functions: db2-fn:sqlquery, db2-fn:xmlcolumn, db2-fn:xmlcolumn-contains

The following XQuery statements match if inexact matching is enabled:

xquery let $i:= db2-fn:sqlquery("select c1 from tab1")/a/b[c=1] return $i

xquery let $i:= db2-fn:sqlquery("select c1 from tab1")/a/b[c=2] return $i

For inexact matching, the special register is not supported. The following examples show some of the type of statements that do not match in inexact matching:

c1 between 5 and :hv
5 between c1 and c2

```
c1 in (select c1 from t1)
c1 in (1,2,3)
```
```
c1 in (c1, 1, 2)
c1 in (c2, 1, 2)
```
```
A = 5
A = 5 + :hv 
```
```
with RR
with RS
```
```
c2 < CURRENT TIME
c2 < '11:12:40'
```

c3 > CURRENT TIMESTAMP
c3 > '07/29/2010'

Syntax for specifying matching

Within the optimization profile, you can set either exact or inexact matching at either the global level or at the statement-level. The XML element STMTMATCH can be used to set the matching method.

The STMTMATCH element has an EXACT attribute which can be set to either TRUE or FALSE. If you specify the value TRUE, exact matching is enabled. If you specify the value FALSE, inexact matching is enabled. If you do not specify this element or if you specify only the STMTMATCH element without the EXACT attribute, exact matching is enabled by default.

To have a matching method apply to all statements within the optimization profile, place the STMTMATCH element at the global level just after the top OPTPROFILE element.

To have a matching method apply to a specific statement within the optimization profile, place the STMTMATCH element just after the STMTPROFILE element.

The following example shows an optimization profile with a STMTMATCH element at both the global level and statement level:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE>

<!--Global section --> 
   <STMTMATCH  EXACT='FALSE'/>

<!-- Statement level profile -->
   
   <STMTPROFILE ID='S1'>
      <STMTMATCH  EXACT='TRUE'/>
      <STMTKEY>           
          <![CDATA[select t1.c1, count(*) from t1,t2 where t1.c1 = t2.c1 and t1.c1 > 0]]>
      </STMTKEY>
      <OPTGUIDELINES>
         <NLJOIN>
            <TBSCAN TABLE='T1'/>
            <TBSCAN TABLE='T2'/>
         </NLJOIN>
      </OPTGUIDELINES>
   </STMTPROFILE>

   <STMTPROFILE ID='S2'>
      <STMTKEY><![CDATA[select * from T1 where c1 in( 10,20)]]>
      </STMTKEY>
      <OPTGUIDELINES>
         <REGISTRY>
            <OPTION  NAME='DB2_REDUCED_OPTIMIZATION'  VALUE='YES'/>
         </REGISTRY>
      </OPTGUIDELINES>
   </STMTPROFILE>

</OPTPROFILE>

Order of precedence

In the example the STMTMATCH element has been set at both the global and statement level. Therefore, to determine which matching method gets executed depends on the order of precedence. The following is the order of precedence from highest to lowest:

Statement level profile settings
Global level profile settings

This order means that in the example, inexact matching is set at the global level because EXACT is set to FALSE. Therefore, inexact matching is applied to all the statements in the profile unless the compiling statement matches the first statement. Then, exact matching is enabled for that statement because the STMTMATCH element for that statement has EXACT set to TRUE.

The last statement in the example optimization profile file does not have an STMTMATCH element. The global setting takes precedence and inexact matching is applied to this statement.