Missing CPUTime For Stored Procedures

Products

• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

Problem: 

When user drills down from Plan-Detector Planname Summary Display to the programs-Detector Plan Program Display, aggregating INDB2-TIME adds up while the roll-up of INDB2_CPU is missing the cpu time for Stored Procs as shown below:

19.0    >   --------- DETECTOR Planname Summary Display -------- 16/04/05 16:56

Command ==>                                                     Scroll ==> PAGE

                                                                    LINE 1 OF 8

DB2 SSID  ==> D10A                                                            

View Type ==> A * -Activity X -Exception E -Error O -Object                   

View By   ==> P * -Plan G -Prog S -SQL Q -DSQL F -Prof K -Key   Total/Avg ==> T

                                                                             

Interval Date => 16/03/29   Interval Time => 16:57:08    Elapsed Time => 00:30

-------------------------------------------------------------------------------

                                                                              

S -Programs, D -Detail, Q -Dynamic SQL, K -Keys, H -History, T -Active Threads

                                                                               

  PLANNAME COMMIT   ABORT  SQL        TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU    GETPAGE

  -------- -------- ------ ---------- ------- ------- ------------ ------------ ----------

_ RTPREG         39      0   12659495  59.42%  63.52% 04:47.072353 04:20.490521     128051

_ DISTSERV   343156      0     971512  37.58%  35.45% 03:01.577157 02:25.378508    5732957

_ SELTI01       748      7      72384   2.63%    .99% 00:12.737005 00:04.067469     157469

 

 

 

 

19.0    >   ----------- DETECTOR Plan Program Display ---------- 16/04/05 16:54

Command ==>                                                     Scroll ==> PAGE

                                                                    LINE 1 OF 8

Total/Avg ==> T              DB2 SSID  ==> D10A           Planname ==> SELTI01

                                                                               

Interval Date => 16/03/29   Interval Time => 16:57:08    Elapsed Time => 00:30

-------------------------------------------------------------------------------

                                                                               

S -SQL stmts, Q Dynamic SQL, E -Explain, D -Detail                            

                                                                               

  PROGRAM  TYPE SQL        TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU    GETPAGE  

  -------- ---- ---------- ------- ------- ------------ ------------ ----------

_ DBXIOI   PROC      19201  34.04%  59.68% 00:06.974201 00:02.427682     125027  Yellow is missing at the PLAN level.

_ DBXMAP   PKGE      41395   7.54%  18.71% 00:01.544768 00:00.761272      18105

_ DBXIO    PKGE       4330  52.54%  12.81% 00:10.763725 00:00.521307     134524

_ DBXIOS   PROC       2586   2.08%   4.00% 00:00.427303 00:00.162854       5932

_ CGETVER  PROC       4279   1.23%   3.20% 00:00.253696 00:00.130271       4828

_ DBXSCNJ  PKGE        405   2.08%    .89% 00:00.426835 00:00.036583       4832

_ CRTXT80  PROC        186    .44%    .66% 00:00.091682 00:00.027086       1006

_ DBXVCMD  PKGE          2    .00%    .01% 00:00.001675 00:00.000411          8

******************************* BOTTOM OF DATA ********************************

  

Resolution: 

Concerning the INDB2_TIME of the Proc not being added to the Plan level, please see below for detailed explanation 

 For Stored Procedures and UDFs and Triggers, the INB2_TIME is not aggregated with INDB2_TIME of the Packages executed under the Plan. However, the INDB2_CPU of  the stored PROC/UDF is aggregated with INDB2_CPU of the package since it reports the total CPU usage under that PLAN. 

 INDB2_TIME is the wall clock elapsed time, which means, the INDB2_TIME is calculated for each component from the start to the end and those processes are running concurrently. 

 

Field: INDB2_TIME 

Description: The total or average amount of elapsed time spent executing within DB2 for the thread, unit of work, plan, program, SQL activity, SQL call(s), application, or user.  So the INDB2_TIME for a PLAN is the aggregate of INDB2_TIME of all the Packages under that Plan. 

 The product is working as designed. 

 

 

Environment

Release:
Component: PSA