What are Parameter Markers?

Posted by

Parameter Markers

Parameter Markers show up as ? in queries when you look at them in a dynamic SQL snapshot or in the sysibmadm views. They show up when the value for a variable is not hard-coded at the time of preparation, but instead is changeable at execution time. I’ve included a couple of code snippets to illustrate what I’m talking about.

Parameterized vs. non-parameterized queries

When I start going off on the advantages and disadvantages of parameterized queries, I invariably get a few blank looks. So I’ve included an example here (coded in perl, but not fully complete), to give an idea of what I’m talking about.

Please excuse my partial messy perl syntax – the concepts are the same in other languages


•$sql=“select * from orderitems where orders_id=$orders_id”;
•my $sth = $dbh->prepare($sql);

If you re-execute the statement, you can only do it for orders_id 1234, even if you change the value of $orders_id

Advantages: Makes full use of runstats, including distribution statistics

Disadvantages: Re-compiles on every execution, meaning time is taken to calculate the access path; Also, every execution has an entry in the dynamic package cache, making analysis more difficult


•$sql=“select * from orderitems where orders_id=?”;
•my $sth = $dbh->prepare($sql);

If you re-execute the statement, you can change the value of orders_id or pass an entirely different variable on execution.

Advantages: Access plan is calculated only once, eliminating overhead for calculating access plan. Executions are grouped in the dynamic sql snapshot, making spotting problems easier.

Disadvantages: Does not make use of distribution statistics, meaning that performance (especially for data that is not normally distributed) may be poorer



In general, I’m a fan of not using parameter markers, because in many databases the performance advantage of using distribution statistics is greater than the performance disadvantage of calculating the access plan. However, the overall suggestion that comes out of IBM is to use parameter markers just about all of the time to get rid of the overhead of calculating the access plan. I suppose it depends on how normal the distribution of your data is.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.