CA Detector exception processing is displaying incomplete SQL statements, the text is truncated.

Products

RC Compare for DB2 for z/OS Bind Analyzer for DB2 for z/OS SQL-Ease for DB2 for z/OS SYSVIEW Performance Management Option for DB2 for z/OS Plan Analyzer for DB2 for z/OS Subsystem Analyzer for DB2 for z/OS Database Analyzer for DB2 for z/OS Fast Unload for DB2 for z/OS Fast Check for DB2 for z/OS Fast Index for DB2 for z/OS Rapid Reorg for DB2 for z/OS

Issue/Introduction

CA Detector displays the an incomplete SQL statement at
screen "Exception SQL Call Text". This can occur for a very large
complex SQL statements. Why?

Environment

Release:
Component: PDT

Resolution

Check the value set for parameter "Max Dynamic SQL Statement text captured " at the
"View Collection Profile Global Defaults" panel.
This parameter sets the maximum length of the SQL text to be captured

If the value is less than the SQL statement size it will get truncated.

The panel can be reached from CA Detector Main Menu:

select     4  Maintain collect/report profiles
select     1  View Collection Profiles - Create, view, and modify collection profiles
select     S -View/Modify Profile

enter "s" into field Set Global Defs 
PDTE0006   ------ DETECTOR View/Modify Application Groups ----------- 08/09/18 
Command ==>                      Scroll ==> PAGE         
View Group Type ==> A  (A=Application, R=Resource, X=EXclude, K=Keys) 
Set Global Defs ==> s  Add Application Group ==> _ 

Profile ID ==> USERID                                    Profile SSID ==> D81A

------------------------------------------------------------------------------
     
S -View/Modify Appl Group    D -Delete Appl Group 

APPLICATION_GROUP    UUSERID    UDATE      UTIME      CUSERID    CDATE 
------------------   --------   --------   --------   --------   -------- 
_  SAMPLE APPL GRP #1   CA         09/18/08   07:17:46   CA         09/18/08 
_  SAMPLE APPL GRP #2   CA         09/18/08   07:17:46   CA         09/18/08 
_  SAMPLE APPL GRP #3   CA         09/18/08   07:17:46   CA         09/18/08 
******************************* BOTTOM OF DATA ***********
==============================================================
     
press ENTER
PDTE0060 ------ View Collection Profile Global Defaults ------------- 08/09/18
Command ==> 
     
Update Global Defs ==> _  (Select to update the global values) 
     
-------------------------------------------------------------------------
     
Profile ID         ==> USERID         Profile SSID  ==> D81A 
     
Max Exception Requests captured per collection interval ==> 010000 
Max Host Variable data captured per exception request   ==> 002500 
Max Dynamic SQL Statement text captured                 ==> 0032767 
     
Enable global dynamic SQL exception collection          ==> Y 
Enable global static SQL exception collection           ==> Y 
Enable global exception host variable collection        ==> Y 
     
===============================================================
     
PDTXH872 ------------------------- Field Help ------------------------- 
COMMAND ===>

Field: Max Dynamic SQL Statement text captured

Description: The maximum amount of storage (in bytes) that will be allocated to store dynamic
SQL statement text. If the amount of storage required to store the SQL statement text
exceeds this amount then the SQL text collected will be truncated to this amount.

This value applies to the dynamic SQL text retained for SQL exceptions and standard activity dynamic
SQL text collection. Static SQL exception text is restricted to a maximum of 4000 bytes. Static text viewed
from the standard activity displays is presented in its entirety.

This field can range in value from 0 to 2097152 bytes. A value of 0 suppresses dynamic SQL text collection.