Detector zIIP Statistics in SQL Detail Display

book

Article ID: 144180

calendar_today

Updated On:

Products

CA Subsystem Analyzer for DB2 for z/OS CA Database Management for DB2 for z/OS - Performance Suite CA Database Management for DB2 for z/OS - SQL Performance Suite CA Detector for DB2 for z/OS

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          ->

 

                                      

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.