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)
DB2 10.5 for Linux, UNIX, and Windows