Dynamic SQL Statements in Cache Statistics are all zeros

Products

• SYSVIEW Performance Management Option for DB2 for z/OS
• Database Management for DB2 for z/OS - SQL Performance Suite
• Insight Performance Monitor for DB2 UDB for z/OS

Issue/Introduction

After applying maintenance  all the stats( Exec , Accum Elapsed etc...)  for  Dynamic SQL Statements in Cache  Statistics in R/DYNSQLST  are zeros.

To get to R/DYNSQLST from IDB2 Main Menu select  Option 10 SQL Exec Current Stats

             

Environment

Release : 20.0

Component : CA Insight Database Performance Monitor for DB for z/OS

Resolution

This problem is caused by Sysview for DB2(IDB2) trying to start IFCID 503 on the DB2 subsystem without specific DB2 PTF:

DSNW130I - MON TRACE STARTED, ASSIGNED TRACE NUMBER 02
DSN9022I - DSNWVCM1 '-START TRACE' NORMAL COMPLETION
DBG37045W DSNP 13:47:01 Unsupported IFCIDs removed from START/MODIFY TRACE command
DBG43004I DSNP 13:47:01 IFI FUNCTION COMMAND COMPLETED WITH RETURN CODE 0008 AND REASON CODE 00E60820
DSNW138I - IFCID 503 IS INVALID
DSN9023I - DSNWVCM1 '-MODIFY TRACE' ABNORMAL COMPLETION

The CA SYSVIEW Performance Management Option for DB2 for z/OS (IDB2)
tries to use DB2 command MODIFY TRACE with IFCID 503 to collect
statistics about discarded pages, which are included in IFCID 225.
The command will fail if it's a Db2 11 without Db2 PTF PI82191 or Db2 12
without DB2 PTF PI78979. There is a new system parameters IFCID503=ON|OFF
allows to control the tracing of IFCID 503.

Either install PTF SO09556 and after that modify data collector's system parameter and use IFCID503=OFF.
Or apply DB2 PTFs without the need to modify DC system parameters.