In most cases (at least for OLTP systems), using SQL parameter markers instead of literal values improves performance, adds consistency, reduces CPU overhead, and makes the database easier to monitor.  SQL parameters let DB2 cache the access plan so that compilation only happens once and execution counts are combined for the same statement.

A transaction system that carelessly uses literals can often be improved by converting literals to markers wherever possible.  But since this recoding takes time, it would be nice to predict the performance impacts (hopefully benefits) of such a change without having to just code it all and then measure.

Fortunately, DB2 9.7 offers the new Statement Concentrator to do just that.  Enabling it at the database level is easy:

db2 update db cfg for DATABASE using stmt_conc literals

And you can see if it’s enabled with:

db2 get db cfg for DATABASE | find /i “stmt_conc”

I suggested this new feature to a colleague today, and did some experiments myself.  It was nice to see the statements combined in the Dynamic SQL Snapshots, and also see the (zero-execution) literal versions separated out.  This means I’ll have to update my snapshot analysis tools which combine similar SQLs.

But the nagging feeling remains that this might be too broad a change.  For some SQLs, those unique (literal-driven) access plans are necessary.  But it may be possible to reach a good balance by enabling the statement concentrator at just the right level and by using optimization profiles for those exceptions to the rule.  That’s something worth concentrating on.