High CPU consumption for a Detector unload datastore job with many PDT0220 messages

Products

• Detector for DB2 for z/OS

Issue/Introduction

Running a Detector for Db2 for z/OS (PDT) unload datastore job and high CPU consumption was reported for the job without an explanation,
additionally there are a lot of PDT0220 messages in the output.    
  
PDT0220 PDTQDRCC DB2=ssid PKGE=<package name> REQ=CAF-CONN SQLCODE= 0

Resolution

With SQLCODE= 0 the problem was with the CAF-CONNECT or CAF-OPEN connection trying to access the Db2 Catalog to retrieve the Static SQL text.
Note UNLDTEXT parameter defaults to UNLDTEXT=Y, it indicates that static and dynamic SQL text with standard activity data is unloaded.
Detector User Guide manual already reports unloading static SQL text can have an adverse impact on batch utility performance.
 
The Static SQL statement text is not stored in the Detector datastore unless the statement is an Error or an Exception, then during
Detector unload Job, PDTBATCC program reads the SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT tables in the Db2 Catalog to get the text.
In summary to unload the SQL Text for static SQL statements for each static SQL statement the job has to query the Db2 Catalog, this is done with
the Xmanager address space and it is very resource intensive. There are some improvements in this process over each new release but the
access to the DB2 Catalog cannot be suppressed.
 
Normally PDT0220 messages are caused to reach one of the limits for connections CTHREAD, IDFORE or IDBACK, sometimes when the job
is run in other conditions in the Db2 Subsystem, Detector unload job runs fine without PDT0220 messages but the CPU consumption and
performance problems are still there.
 
The best option is to work with UNLDTEXT=D (Unload only Dynamic SQL text). There is no sense to work with this intensive work with
UNLDTEXT=Y unless you need the data to research a specific problem, additionally if you find any problem with the the static SQL
statements after the data has been loaded to the Detector Db2 tables you can always display the static SQL statement text thru RC/Query.