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.