Parameter markers in place of host variables

If you have host variables in a predicate for an original query in a static application and if you are using QMF or SPUFI to execute EXPLAIN for the query, you should consider using parameter markers where you use host variables in the original query.

Begin program-specific programming interface information. If you use a constant value instead, you might see different access paths for your static and dynamic queries. For instance, compare the queries in the following table:

Table 1. Three example queries for the use of parameter markers
Original Static SQL QMF Query Using Parameter Marker QMF Query Using Literal 
DECLARE CUR1
CURSOR FOR
SELECT * FROM T1
WHERE C1 > :HV
 
EXPLAIN PLAN SET
QUERYNO=1 FOR
SELECT * FROM T1
WHERE C1 > ?
 
EXPLAIN PLAN SET
QUERYNO=1 FOR
SELECT * FROM T1
WHERE C1 > 10

Using the constant '10' would likely produce a different filter factor and might produce a different access path from the original static SQL statement. (A filter factor is the proportion of rows that remain after a predicate has "filtered out" the rows that do not satisfy the predicate. The parameter marker behaves just like a host variable, in that the predicate is assigned a default filter factor. End program-specific programming interface information.