Detector zIIP Statistics in SQL Detail Display
Article ID: 144180
Updated On:
Products
Issue/Introduction
What is the difference between ZIIP_CPU and CL2_ZIIP_CPU in the SQL Detail Display?
In the following display, ZIIP_CPU showes over 02:11 while the CL2_ZIIP_CPU shows 0:00.
20.0
------- DETECTOR DS Group Plan SQL Detail Display ------- 01-22-20 14:08
Command
==>
Scroll ==> CSR Group name ==>
DSNDOP0 Members ==>3
Selected ==> 3
Planname ==>
DISTSERV Program ==>N472ODC2
Type ==> PROC
Collid ==>D472P00
Version ==>V1
Total/Avg=>T
Interval Date =>
01-20-20 Interval Time => 16:00:00 Elapsed Time => 01:00
-------------------------------------------------------------------------------
SQL Statement Information SQL_CALL ->OPEN
STMT# ->0000388
SECT# ->00005
SQL ->4608
TIMEPCT ->95.11%
CPUPCT ->98.48%
INDB2_TIME -> 03:51.195152
INDB2_CPU ->03:36.044485
Additional Time and Metrics
UNACC_TIME -> 00:14.961080
ZONCP_CPU -> 00:01.292932
ZIIP_CPU -> 02:11.555943 <===============
CL2_ZIIP_CPU -> 00:00.000000 <===============
XML_MAX_ST ->0
AA_TIME ->00:00.000000
AA_CNT ->0
Environment
Release : 20.0
Component : CA Detector for DB2 for z/OS
Resolution
In calculating the ZIIP_CPU metric, Detector executes a z/OS macro to obtain the zIIP CPU time consumed under the thread before and after each SQL call is executed. After each SQL call, Detector calculates the difference between the before and after zIIP CPU times. This difference is the zIIP CPU time consumed during the execution of a single SQL call, e.g. a single OPEN. The screenshot shows that this OPEN was executed 4608 times. This means that before and after zIIP CPU times were obtained 4608 times and a difference was calculated 4608 times. The value reported on our display is the sum of the 4608 values calculated.
In calculating the CL2_ZIIP_CPU metric, Detector attempts to obtain the value in DB2’s QWACCLS2_zIIP field before and after each SQL call and calculate the difference each time. However, Detector has found that the DB2 control block containing the QWACCLS2_zIIP field is not always accessible prior to an SQL call. When Detector is not able to obtain the QWACCLS2_zIIP value before an SQL call, it reports 0 as the CL2_ZIIP_CPU for that SQL call.
In summary, the ZIIP_CPU metric does not rely upon the accessibility of a DB2 control block and the CL2_ZIIP_CPU metric does. And Detector has found that this DB2 control block cannot always be accessed before SQL call execution. In environments in which the DB2 control block sometimes cannot be accessed, the CL2_ZIIP_CPU value reported may be 0 or at least less than the ZIIP_CPU value reported. You shouldn’t ever see a CL2_ZIIP_CPU value reported that is greater than the ZIIP_CPU value reported.
Feedback
