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