How to capture Dynamic SQL within Detector for Db2

Products

• Detector for DB2 for z/OS

Issue/Introduction

Requirement to capture Dynamic SQL within Detector for Db2 for z/OS (PDT).

Resolution

There are several ways that you can capture Ddynamic SQL:

1. When starting a collection using Detector's option 5 Initialize SSID Collection, set Y to the following options:

      R11.05 ----------- DETECTOR Start Collection Display ----------- 07/05/13 20:       Command ==>       DB2 SSID ==> D71C    -----------------------------------------------------------------------------    Specify SQL collection options:       Triggered SQL collection ==> Y (Y/N)       Dynamic SQL Text Statistics ==> Y (Y/N) (Requires profile)       Plan Exclude List ==> N (Y/N) (Requires profile)       Specify SQL EXCEPTION collection options: (Require profile)       Dynamic SQL ==> Y (Y/N) Static SQL ==> Y (Y/N)       SQL Error ==> Y (Y/N) SQL Error Text ==> Y (Y/N)       Host variables ==> Y (Y/N)       Exception cache size ==> 2000     

2. Use parameter DYN(Y) as part of an MVS Modify command to start collection. For example:
   
   F PTXMAN, START(DTR),DYN(Y),PRF(COLLDSN1),DB2(DSN1),
   
   INT(0015),EXT(Y),DST(DETECTOR),HLVL(PDT)
   
   
3. With the r11.5 release there is a new option called SQLT(Y) that can be added to enable Dynamic SQL Text Statistics collection with exception processing.
   This option can be added to the $START(DTR) specification during automatic startup.