IBM Support

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

Technical Blog Post


Abstract

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).

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141000