Inexact matching examples for SQL statements in optimization profiles

Inexact matching in optimization profiles occurs if you set the EXACT attribute to false in the STMTMATCH tag. The compiling statement is then matched to the statements in an active optimization profile. The compiler matches these statements based on different default matching rules and rules that are specific to inexact matching.

The following examples show where inexact matching is successful for matching SQL statements.

Example 1: Inexact matching in the predicate clause

Each of the following pair of statement fragments have different literal values for the predicate, but still match:

between '07/29/2010' and '08/29/2010' 
between '09/29/2010' and '10/29/2010'

'ab'     like :hv1
'AYYANG' like :hv2

(A=1 AND B=1)       OR (A=2 AND B=2)
(A=:hv1 AND B=:hv2) OR (A=3 AND B=3)

c1 > 0 selectivity 0.1
c1 > 0 selectivity 0.9

c1 = ?
c1 = :hv1 

Example 2: Inexact matching in the IN list predicate

All of the following statement fragments have different values in the IN list predicate, but still match:

c1 in (:hv1, :hv2, :hv3);

c1 in (:hv2, :hv3);

c1 in ( ?, ?, ?, ?);

c1 in (c1, c2 );

c1 in (:hv1, :hv2, c1, 1, 2, 3, c2, ?, ?);

Example 3: Inexact matching in the select list

The following statement fragment has different host variables in the select list, but still matches:

select c1 into :hv1 from t1
select c1 into :hv2 from t1

The following statement fragment has different literals in the select list, but still matches:

select 1, c1 from t1
select 2, c1 from t1

The following statement fragment has a different subquery in the select list, but still matches:

select c1, (select c1 from t1 where c2 = 0) from t1
select c1, (select c1 from t1 where c2 = 5) from t1

The following statement fragment has a different expression in the select list, but still matches:

select c1 + 1
select c1 + 2

Example 4: Inexact matching for different clauses

The following statement fragment has different rows for the optimize clause, but still matches:

optimize for 1 row
optimize for 10 row

The following statement fragment has different rows for the fetch clause, but still matches:

fetch first 10 rows only
fetch first 50 rows only

The following statement fragment has different literal value for the having clause, but still matches:

having c1 > 0
having c1 > 10

Each of the following pairs of statement fragments either have different column positioning for the order by clause or have different literal values for the expression in the order by clause, but still match:

order by c1+1, c2 + 2, 4
order by c1+2, c2 + 3, 4

Each of the following pairs of statement fragments either have different literal values or host variables for the set clause, but still match:

set c1 = 1
set c1 = 2

set queryno = 2
set queryno = 3

set querytag = 'query1'
set querytag = 'query2'

SET :HV00001 :HI00001 = <subquery>
SET :HV00009 :HI00009 = <subquery>

Each of the following pairs of statement fragments have different literal values for the group by clause, but still match:

group by c1 + 1
group by c1 + 2

group by 1,2,3
group by 3,2,1

Each of the following pairs of statement fragments have different literal values for the values clause, but still match:

values 1,2,3
values 3,4,5

values ( 10, 'xml', 'a' )
values ( 20, 'xml', 'ab' )

Example 5: Inexact matching for non-XML functions

Each of the following pairs of statement fragments have different literal values in the function call, but have the same number of literals and still match:

decimal(c1, 5, 2)
decimal(c1, 9, 3)

Blob('%abc%')
Blob('cde%')

max(1, 100, 200)
max(INFINITY, NAN, SNAN)

Example 6: Inexact matching for special expressions

Each of the following pairs of statement fragments have different literal values in either the case when expression or the mod expression, but still match:

order by mod(c1, 2)
order by mod(c1, 4)

case when b1 < 10 then 'A' else 'B' end 
case when b1 < 20 then 'C' else 'D' end  

Example 7: Inexact matching for the CAST function

In Db2® Version 10.5 Fix Pack 3 and later fix packs, the CAST of a literal, host variable or parameter marker matches statements with only the literal, host variable or parameter marker without the CAST function.

CAST( 'a' AS VARCHAR(5) )
CAST( 'b' AS VARCHAR(10) )

CAST( 'a' AS VARCHAR(5) )
'a'

CAST( 'a' AS VARCHAR(5) )
'b'

CAST( :myHostVar AS VARCHAR(5) )
:myHostVar

CAST( C1+1 AS VARCHAR(5) )
C1+2

Example 8: Inexact matching for the ORDER BY clause

In Db2 Version 10.5 Fix Pack 3 and later fix packs sequential column numbers are preserved under an ORDER BY clause.

ORDER BY C1, UPPER(C2), 3, 1+4
ORDER BY C1, UPPER(C2), 3, 5+6

Example 9: Inexact matching for duplicate rows in a VALUES list

In Db2 Version 10.5 Fix Pack 3 and later fix packs, duplicate rows in VALUES list where all columns consist of only one literal, host variable or parameter marker are removed before the statement text is compared.

VALUES (1,1,1), (2,2,2), …, (9,9,9)
VALUES (1,2,3)

VALUES (1+1,1,1), (2,2,2), (3,3+3,3), (4,4,4)
VALUES (5+5,5,5), (6,6+6,6)

VALUES (1,2), (C1,1), (2,2), (3,3)
VALUES (C1,4)

VALUES (C1+1,1), (C2+2,2), (3,3)
VALUES (C1+4,4), (C2+5,5)