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