75 ways to demystify DB2 #45: Techtip : How to capture critical performance diagnostics information for DB2 BLU ( BLU aka CDE - Columnar Data Engine) environment

Body

Users can use the DB2 Trace Facility (db2trc) in DB2 BLU ( BLU aka CDE - Columnar Data Engine) environment to collect the critical performance diagnostics information using new component, CDE_PERF_TRACE.
 

• To collect performance diagnostics, one should run:

      db2trc on –m CDE_PERF_TRACE
      ... run workload ...
      db2trc dmp perftrc.dmp
      db2trc off
      db2trc fmt perftrc.dmp perftrc.fmt

• There were reports on "db2trc on –m CDE_PERF_TRACE" not working due to some yet unidentified environment problem.
  Then one may use the equivalent command:

      db2trc on -m "*.*.CDE_PERF_TRACE.*.*"

• To make "performance log" persistent, tracing to disk may be used:

      db2trc on –m CDE_PERF_TRACE –f perftrc.dmp

• This approach addresses the issue of the infinitely growing performance.log file. “Circular buffer” is available even when tracing to a file:

      db2trc on –m CDE_PERF_TRACE –f perftrc.dmp –l 1G

• The new CDE performance trace is dynamic and it's easy to turn on or off.
   
• The new CDE performance trace allows one to specify which records are of interest and only these records will be traced.
  For example, in order to trace only SQL-- text and CDE Runtime Statistics, use:

      db2trc on -m ibm_cde::services::perftraceRuntimeStats -f trc.dmp

• The new CDE performance trace allows to trace data only for the specific application (identified by application handle or application ID).