Sometimes when you’re looking at Db2 Queries, you may notice question marks in your queries, like this:
SELECT partnum, sum(quantity) as QUANTITY FROM orderitems oi INNER JOIN orders o ON oi.orders_id = o.orders_id where o.field3 is null and o.timeplaced is not null and TIMESTAMPDIFF(4,CHAR(current timestamp - o.timeplaced)) < ? GROUP BY oi.partnum ORDER BY oi.partnum with ur /* WC_UTILITY 'Data Load' */Similarly, you may see a value specified using a colon, particularly if the statement concentrator is on:
select firstname, lastname from employee where empno=:L0Host variables look and perform similarly, but are most often used in static SQL instead of dynamic SQL:
SELECT S_NAME FROM S_DETAIL WHERE SN = :SN-INThese may look strange the first time you see them, but alone they are not a problem. You might see them in queries reported in a dynamic SQL snapshot, the MON_GET_PKG_CACHE_STMT table function, or the LONG_RUNNING_SQL administrative view.
What are these and what do they mean?
Parameter Markers
In the statements above, the ? represents a parameter marker, while the :L0 represents a parameter marker that comes from the statement concentrator. :SN-IN represents a host variable. Functionally to Db2 these can be thought of as roughly equivalent.
Before an SQL statement is executed, it must be compiled. Compilation is a process where the Db2 optimizer comes up with the optimal access path to physically access the data needed to fulfill the query. One of the true powers of using SQL in a Db2 database and most other relational databases is that you don’t have to know where data physically resides on disk. The person writing a query knows what column of what table, but doesn’t know what tablespace, file, disk, LUN, or sector that data resides on. Because of these layers of abstraction and helper objects like indexes or statistical views, Db2 spends time calculating the best way to access the data.
Parameter markers and host variables allow Db2 to take the same compiled access plan and use it for many iterations of a query, even when the values used in a query change for every execution. This saves the time that goes into compilation.
Parameter markers and host variables are used depending on the way a developer specifies the literal values when preparing and executing a query against a Db2 database. Since certain values in a query work better using literal values while others work better as parameter markers, this should be a conscious choice made by developers or vendors for each and every query.
Statement literals come into play when developers or vendors are unwilling or unable to properly use parameter markers. When that happens, DBAs can mitigate the negative impact this has on database performance by carefully enabling the statement concentrator. This is not an action to be taken lightly, but in some situations can significantly help performance.
Why Can’t I see the Actual Values Used?
The only places you might capture the static values used for parameter markers is through an activity event monitor or through the audit facility. Everywhere else, you’ll see these placeholders. The reason is that the main place Db2 gathers statements and related performance information is the package cache, and the package cache stores only aggregate information across all executions of a query, not information on individual executions.
Are Parameter Markers or Statement Literals a Problem?
While they may look odd or be confusing, parameter markers and statement literals are not in themselves a problem. Some performance problems may come out of incorrect use of them, and a skilled DBA may be able to help you identify such issues. Usually their use is a good thing rather than a bad thing.
Use of parameter markers or statement literals is more likely to hurt large complex queries. Often the best use of parameter markers is for small, very frequently executed SQL such as that executed by an OLTP application.