Using parameter markers to reduce compilation time for dynamic queries
The Db2® data server can avoid recompiling a dynamic SQL statement that has been run previously by storing the access section and statement text in the dynamic statement cache.
A subsequent prepare request for this statement will attempt to find the access section in the
dynamic statement cache, avoiding compilation. However, statements that differ only in the literals
that are used in predicates will not match. For example, the following two statements are considered
different in the dynamic statement
cache:
SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 26790
SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 77543
Even
relatively simple SQL statements can result in excessive system CPU usage due to statement
compilation, if they are run very frequently. If your system experiences this type of performance problem, consider changing the
application to use parameter markers to pass predicate values to the Db2 compiler, rather than
explicitly including them in the SQL statement. However, the access plan might not be optimal for
complex queries that use parameter markers in predicates. For more
information, see Using the REOPT bind option with input variables in complex queries.