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 Dynamic SQL:

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

         ----------- DETECTOR Start Collection Display ----------- 23/12/04 17:10
    Command ==>
    
    DB2 SSID ==> XXXX
    
    Specify collection options:
     Triggered SQL          ==> Y  (Y/N)  Plan Excl/Incl List  ==> N (Y/N)
    
    SQL collection options:
     Standard Activity      ==> Y (Y/N)
       Dynamic SQL Stats    ==> Y (Y/N)   (Requires profile)
       View By Keys         ==> Y (Y/N)   (Requires profile)
       Dynamic Exceptions   ==> Y (Y/N)   (Requires profile)
       Static Exceptions    ==> Y (Y/N)   (Requires profile)
     SQL Errors             ==> Y (Y/N/A) (Requires profile)
       SQL Error Text       ==> Y (Y/N)
     Host variables         ==> Y (Y/N)   (applies to all options)
    
    Collection Profile      ==> XXXXXXX_  Exception cache size MB ==> 0020 (1-20)

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(XXXX),
   
   INT(0015),EXT(Y),DST(DETECTOR),HLVL(PDT)
   
   
3. 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.