Technical Blog Post
Abstract
Why the cost of a query showing different from command line and from different clients
Body
A common mistake many people make is, consider a query with parameter marker same as the query with the parameter marker replaced by literals.
As soon as the parameter markers in a query is replaced by literals it's not the same query.
Basically, during query compilation a query with parameter marker provides much lesser intelligence to the DB2 compiler. As a result the access plan chosen by DB2 optimizer might be sub-optimal compared to a query which uses specific literal values.
So, cost of the query will show different for these two queries as those are not identical queries during compilation.
Other common mistake is, not noticing if the query being used as a dynamic SQL or, being used as a static SQL from a package.
The dynamic SQLs will be compiled when it's run, with the current statistics (except REOPT option used, see KC for more details)
But, the static SQLs get the statistics from it's package. So, the statistics will be from the time when the package was last rebound.
Even when the exact same query being run from two different places are both static SQLs, it might still show two different costs. That might be due to the source of the same query are not the same package. Those might be coming from two different packages.
UID
ibm11140826