How to trap Dynamic SQL?

Document ID : KB000055254
Last Modified Date : 14/02/2018
Show Technical Document Details

There are several ways that you can capture dynamic 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.