Accounting for nested activities

The accounting class 1 and class 2 CPU and elapsed times for triggers, stored procedures, and user-defined functions are accumulated in separate fields and exclude any time accumulated in other nested activity.

These CPU and elapsed times are accumulated for each category during the execution of each agent until agent deallocation. Package accounting can be used to break out accounting data for execution of individual stored procedures, user-defined functions, or triggers. The following figure shows an agent that executes multiple types of Db2 nested activities.

Figure 1. Time spent executing nested activities

 Time   Application   DB2                           Stored procedure            User-defined function
 -----  ------------  ----------------------------  --------------------------  --------------------------
  T0    Code
  T1    SQL---------->
  T2       <---------
  T3    SQL---------->
  T4                  Trigger
  T5                  SQL
  T6                  CALL triggered--------------->
  T7                                                Stored procedure code
  T8                                  <-------------SQL
  T9                                  ------------->Stored procedure code
  T10                                 <-------------SQL(User-defined function)
  T11                 Start User-defined function
  T12                 --------------------------------------------------------->User-defined function code
  T13                 <---------------------------------------------------------SQL
  T14                 --------------------------------------------------------->User-defined function code
  T16                 <---------------------------------------------------------User-defined function ends
  T17                 Back to Stored procedure----->Stored procedure code
  T18                 SQL             <-------------Back to trigger
  T19                 Trigger ends
  T20  Code<----------Return to Application
  T21  End

The following table shows the formula used to determine time for nested activities.

Table 1. Sample for time used for execution of nested activities
Count for	Formula	Class
Application elapsed	T21-T0	1
Application task control block (TU)	T21-T0	1
Application in Db2 elapsed	T2-T1 + T4-T3 + T20-T19	2
Application in Db2 task control block (TU)	T2-T1 + T4-T3 + T20-T19	2
Trigger in Db2 elapsed	T6-T4 + T19-T18	2
Trigger in Db2 task control block (TU)	T6-T4 + T19-T18	2
Wait for STP time	T7-T6 + T18–T17	3
Stored procedure elapsed	T11-T6 + T18-T16	1
Stored procedure task control block (TU)	T11-T6 + T18-T16	1
Stored procedure SQL elapsed	T9-T8 + T11-T10 + T17-16	2
Stored procedure SQL elapsed	T9-T8 + T11-T10 + T17-T16	2
Wait for user-defined function time	T12-T11	3
User-defined function elapsed	T16-T11	1
User-defined function task control block (TU)	T16-T11	1
User-defined function SQL elapsed	T14-T13	2
User-defined function SQL task control block (TU)	T14-T13	2

Note: In the preceding table, TU = time used.

The total class 2 time is the total of the "in Db2" times for the application, trigger, stored procedure, and user-defined function. The class 1 "wait" times for the stored procedures and user-defined functions need to be added to the total class 3 times.