How to capture Dynamic SQL within Detector for Db2?
search cancel

How to capture Dynamic SQL within Detector for Db2?

book

Article ID: 55254

calendar_today

Updated On:

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.