High CPU consumption for a Detector unload datastore Job with a lot of PDT0220 messages

Document ID : KB000010026
Last Modified Date : 14/02/2018
Show Technical Document Details

Running a Detector unload datastore Job and a high CPU consumption was reported for the Job without an explanation, additionally there are a lot of PDT0220 messages in the output.    


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 & 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 you have 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.