Times - Class 2 - DB2 Time

This topic shows detailed information about Accounting - Times - Class 2 - DB2 Time.

This block shows information for DB2 class 2.

The following example shows both layouts, the report on the left, and the trace layout on the right.

Accounting - Times - Class 2 - DB2 Time

The field labels shown in the following sample layout of Accounting - Times - Class 2 - DB2 Time are described in the following section.


Report:                                         Trace:

AVERAGE       APPL(CL.1)  DB2 (CL.2)            TIMES/EVENTS  APPL(CL.1)  DB2 (CL.2)
------------  ----------  ----------            ------------  ----------  ----------
ELAPSED TIME  ..........    1.392412            ELAPSED TIME  ..........    1.358710
 NONNESTED    ..........    1.392412             NONNESTED    ..........    1.358710
 STORED PROC  ..........    0.000000             STORED PROC  ..........    0.000000
 UDF          ..........    0.000000             UDF          ..........    0.000000
 TRIGGER      ..........    0.000000             TRIGGER      ..........    0.000000

CP CPU TIME   ..........    1.703134            CP CPU TIME   ..........    1.672292
 AGENT        ..........    0.689782             AGENT        ..........    0.689738
  NONNESTED   ..........    0.689782              NONNESTED   ..........    0.689738
  STORED PRC  ..........    0.000000              STORED PRC  ..........    0.000000
  UDF         ..........    0.000000              UDF         ..........    0.000000
  TRIGGER     ..........    0.000000              TRIGGER     ..........    0.000000
  SQL DI      ..........    0.000000              SQL DI      ..........    0.000000
 PAR.TASKS    ..........    1.013352             PAR.TASKS    ..........    0.982554
  SQL DI      ..........    0.858103              SQL DI      ..........    0.827305

SE CPU TIME   ..........    2.645366            SE CPU TIME   ..........    2.716853
 NONNESTED    ..........    0.000000             NONNESTED    ..........    0.000000
 STORED PROC  ..........    0.000000             STORED PROC  ..........    0.000000
 UDF          ..........    0.000000             UDF          ..........    0.000000
 TRIGGER      ..........    0.000000             TRIGGER      ..........    0.000000
 SQL DI       ..........    0.000000             SQL DI       ..........    0.000000

 PAR.TASKS    ..........    2.645366             PAR.TASKS    ..........    2.716853
  SQL DI      ..........    2.150585              SQL DI      ..........    2.217647

SUSPEND TIME  ..........    0.717750            SUSPEND TIME  ..........    0.681337
 AGENT        ..........    0.699529             AGENT        ..........    0.665070
 PAR.TASKS    ..........    0.018222             PAR.TASKS    ..........    0.016268
 STORED PROC  ..........         N/A             STORED PROC  ..........         N/A
 UDF          ..........         N/A             UDF          ..........         N/A

NOT ACCOUNT.  ..........    0.003102            NOT ACCOUNT.  ..........         N/C
DB2 ENT/EXIT  ..........       46.00            DB2 ENT/EXIT  ..........          46
EN/EX-STPROC  ..........        0.00            EN/EX-STPROC  ..........           0
EN/EX-UDF     ..........        0.00            EN/EX-UDF     ..........           0
EN/EX-SQL DI  ..........  1371968.00            EN/EX-SQL DI  ..........     1371968
DCAPT.DESCR.  ..........         N/A            DCAPT.DESCR.  ..........         N/A
LOG EXTRACT.  ..........         N/A            LOG EXTRACT.  ..........         N/A

ELAPSED TIME

The class 2 elapsed time of the allied agent accumulated in DB2.

Field Name: ADDB2ETT

ELAPSED TIME - NONNESTED

The class 2 elapsed time for nonnested activity accumulated in DB2 for the allied agent. This time does not include the time spent in DB2 processing SQL statements issued by stored procedures, user-defined functions, or triggers.

Special Considerations

The time for most thread allocation and certain abend conditions is not reflected in this time.
The elapsed time for distributed processing is included in the elapsed time of allied-distributed threads.
In query CP, sysplex query, or utility parallelism, this is the time shown in the originating record, which overlaps the elapsed times shown in the parallel records.

Note: This field is not normally shown in the short layouts but can be included with UTR.

Field Name: QWACASC

ELAPSED TIME - STORED PROC

An accumulated and consumed time for stored procedures that consists of following parts:

The total elapsed time that the allied agent spent when running SQL in the stored procedures or WLM address space. A stored procedure may initiate a trigger or invoke a user-defined function. This time is not included in this counter.
Accumulated elapsed time consumed when running stored procedure requests on the main application execution unit. As these stored procedures run entirely within DB2, this time represents class 1 and class 2 time.

Field Name: ADELTSP2

ELAPSED TIME - UDF

The total elapsed time that is spent executing SQL using user-defined function (UDF) requests that are processed in a DB2 stored procedure or WLM address space. This time includes time required to connect and disconnect the UDF task. Non-inline UDF times are included in this time if the native UDF was called on a nested task and was not invoked by the main application execution unit.

This time includes the elapsed time that is consumed when executing user-defined functions on the main application execution unit.

A user-defined function may initiate a trigger or invoke a stored procedure. Any time spent there is not included in this counter.

Field Name: AWAELUD2

ELAPSED TIME - TRIGGER

The total elapsed time spent by the allied agent in triggers.

A trigger may invoke a stored procedure or a user-defined function. The time spent there is not included in this counter.

For triggers there is no distinction between class 1 and class 2 CPU time: all processing controlled by a trigger is within DB2.

Note: This field is not normally shown in the short layouts but can be included with UTR.

Field Name: ADTRET

CP CPU TIME

The class 2 CPU time (in DB2). It indicates:

The class 2 CPU time for the allied agent. This includes the accumulated class 2 TCB time for processing any stored procedures, user-defined functions, and triggers.
The accumulated CPU time for processing parallel tasks. This is valid for query CP parallelism, sysplex query parallelism, and parallel tasks generated by utilities.
For batch reporting, in sysplex query parallelism, the individual CPU times are normalized by the conversion factor of the parallel tasks, related to the originating task.
For online monitoring, in sysplex query parallelism, only CPU times of parallel tasks, running on the same member of the sysplex group as the originating task, are included.

This CPU time does not include time that is consumed on an IBM specialty engine.

Field Name: ADDBCPUT

CP CPU TIME - AGENT

It comprises the class 2 CPU time of the allied agent. This time includes the accumulated class 2 CPU time for processing stored procedures, user-defined functions, and triggers, if present. CPU time for processing parallel tasks is not charged to this counter.

This CPU time does not include the CPU time that is consumed on an IBM specialty engine.

Field Name: ADAGENT2

CP CPU TIME - AGENT - NONNESTED

The class 2 CPU time of the nonnested activity of the allied agent.

This value indicates the CPU time the allied agent spent in DB2 for nonnested activity. This time does not include the time for processing SQL statements issued by stored procedures, user-defined functions, or triggers.

Special Considerations:

For allied-distributed threads, this does not include the time used to process distributed SQL. For DBAT-distributed threads, this includes only processing at this location.
Most thread allocation and certain abend conditions are not included.
This time does not include the time for processing parallel tasks generated by utilities or in query CP or sysplex query parallelism.

Field Name: ADNNEST2

CP CPU TIME - AGENT - STORED PRC

An accumulated and consumed time for stored procedures that consists of the following information:

The CPU time accumulated in DB2 for processing SQL statements issued by stored procedures processed in a DB2 stored procedure or WLM address space. This time is only calculated if accounting class 2 is active.
In DB2 time needed to connect and disconnect the SP task for non-SQL stored procedures.
SQL procedure times are included in this time if the SQL procedure was called on a nested task and was not invoked by the main application execution unit.
CPU time that is consumed when running stored procedure requests on the main application execution unit.

This time does not include the CPU time consumed on an IBM specialty engine.

Field Name: ADCPUSP2

CP CPU TIME - AGENT - UDF

The accumulated CPU time consumed executing user-defined functions. This time does not include CPU time consumed on an IBM specialty engine. It consists of following parts:

The accumulated CPU time consumed in DB2 when processing SQL statements that were issued by UDFs in a DB2 stored procedure or WLM address space. This time also includes the DB2 time required to connect ot disconnect the UDF task. It is only calculated if accounting class 2 is active.
The accumulated CPU time consumed executing user-defined functions on the main application execution unit. This time represents class 1 and class 2 time, because these UDFs run entirely within DB2.

Field Name: AWACPUD2

CP CPU TIME - AGENT - TRIGGER

The accumulated CPU time consumed while executing under the control of triggers.

For triggers there is no distinction between class 1 and class 2 CPU time. All processing controlled by a trigger is within DB2.

Field Name: ADTRCP

CP CPU TIME - AGENT - SQL DI

The accumulated CPU time main agent thread spent processing SQL Data Insights functions. This time is included in Class 2 elapsed time. It does not include CPU consumed on an IBM speciality engine.

Field Name: QWSDICTM

CP CPU TIME - PAR.TASKS

The sum of the CPU times of the parallel tasks running in DB2. These tasks can be query CP, sysplex query parallel tasks, parallel tasks produced by utilities, or rollup autonomous tasks.

In sysplex query parallelism, the accumulated time reflects only parallel tasks running on the same DB2 subsystem as the originating task.

This time does not include the CPU time consumed on an IBM specialty engine.

Field Name: ADDBCPC2

CP CPU TIME - PAR.TASKS - SQL DI

The accumulated CPU time child agent threads spent processing SQL Data Insights functions. This time is included in Class 2 elapsed time. It does not include CPU consumed on an IBM speciality engine.

Field Name: QWASDICT

SE CPU TIME

The accumulated and consumed class 2 time on an IBM specialty engine (SE) that consists of times for non-nested, stored procedures, user-defined functions, triggers, and parallel tasks.

Note: All CPU times of an IBM specialty engine that are reported in DB2 trace records are already normalized by DB2 to the speed of the general purpose processor.

Field Name: AWACC2Z

SE CPU TIME - NONNESTED

The class 2 CPU time for nonnested activity on the main application task consumed while running on an IBM specialty engine.

Field Name: ADSENNC2

SE CPU TIME - STORED PROC

An accumulated and consumed time for stored procedures on an IBM specialty engine that consists of following parts:

The time consumed in DB2 in a DB2 stored procedure or WLM address space. SQL procedure times are included in this time if the SQL procedure was called on a nested task and was not invoked by the main application execution unit.
The time when running on the main application execution unit. This part of time counts for class 1 and class 2 time, because these stored procedures run entirely within DB2.

This time is a subset of QWACSP_CLS1SE.

Field Name: ADSESP2

SE CPU TIME - UDF

The accumulated and consumed time for user-defined functions on an IBM specialty engine that consists of following parts:

The accumulated CPU time consumed in DB2 processing SQL statements issued by UDFs processed in a DB2 stored procedure or WLM address space. This time is a subset of QWACUDF_CLS1SE.
The accumulated CPU time that is consumed when running user-defined functions on the main application execution unit. This time represents class 1 and class 2 time, because these UDFs run entirely within DB2.

Field Name: AWACSEU2

SE CPU TIME - SQL DI

The accumulated CPU time main agent spent processing SQL Data Insights functions on an IBM speciality engine. This time is included in Class 2 CPU time.

Field Name: QWSDIZTM

SE CPU TIME - TRIGGER

The accumulated CPU time consumed on an IBM specialty engine while running triggers on a nested task or on the main application execution unit.

Field Name: AWACTRZ

SE CPU TIME - PAR. TASKS - SQL DI

The accumulated CPU time child agent threads spent processing SQL Data Insights functions on an IBM speciality engine. This time is included in Class 2 CPU time.

Field Name: QWASDIZT

SE CPU TIME - PAR.TASKS

The sum of the CPU times of the parallel tasks, or roll-up autonomous task that are running in DB2 on an IBM specialty engine.

It contains the accumulated CPU time that is consumed in DB2 when processing SQL statements that are issued by UDFs processed in a DB2 stored procedure or WLM address space while running on an IBM specialty engine.

Field Name: ADSEPLL2

SUSPEND TIME

The waiting time for all types of class 3 suspensions by the originating task and parallel tasks, if parallelism is employed.

Background and Tuning Information

The waiting time for all types of class 3 suspensions by the originating task and parallel tasks, if parallelism is employed.

Field Name: ADTSUST

SUSPEND TIME - AGENT

The waiting time of the allied agent for all types of class 3 suspension

This counts class 3 suspension time within nested activity.

Suspension time of parallel tasks in query or utility parallelism is not included.

Field Name: ADTCBCL3

SUSPEND TIME - PAR.TASKS

The sum of the suspension times spent for parallel tasks. These tasks can be query CP or sysplex query parallel tasks, parallel tasks produced by utilities, or roll-up autonomous tasks.

Field Name: ADCPCL3T

NOT ACCOUNT.

The time not accounted in DB2®. This time determines whether there is a large percentage of time that has not been captured within the DB2 accounting record and whether system monitoring tools (such as RMF) should be examined to determine the cause of a performance problem.

In query or utility parallelism, it is the unaccounted time of the originating task only.

Check the DB2 accounting class 2 elapsed time that is not recorded as class 2 CPU time or class 3 suspensions. The following list shows why DB2 Class 2 Not Accounted time can be significant:

Too much detailed online tracing, or problems with vendor performance monitors. This situation is usually the primary cause of high not-accounted-for time on systems that are not CPU-constrained.
Running in a very high CPU utilization environment and waiting for CPU cycles if DB2 work WLM service class goals are not set properly.
Running in a high MVS™ paging environment and waiting for storage allocation.
The IBM specialty engines are highly utilized and the SYS1.PARMLIB(IEAOPTxx) member has the following settings: IIPHONORPRIORITY=NO and IFAHONORPRIORITY=NO.
Frequent gathering of data set statistics (SMF 46 Type 2 records) DD consolidation overhead (z/OS® parm DDCONS=YES DETAIL).
CF Lock Structure system managed DUPLEXing since DB2 is not informed about related suspensions waits.
In very I/O intensive environments, the Media Manager might be running out of request blocks.
Time spent waiting for parallel tasks to complete (when query parallelism is used for the query).
HSM (Hierarchical Storage Management) data set recall is an asynchronous process.
Waiting for requests to be returned from SNA DB2 Server.
Data set open contention related to PCLOSET being too small.
DB2 internal suspend and resume looping when several threads are waiting for the same resource.

Field Name: ADNOTACC

DB2 ENT/EXIT

The total number of DB2 entry and exit events processed by the allied address space to calculate the elapsed time in DB2 and the processor time.

This counter does not include the SQL entry and exit events processed by stored procedures.

Field Name: QWACARNA

EN/EX-STPROC

The number of SQL entry or exit events performed by stored procedures. This number is only calculated if accounting class 2 is active.

Field Name: QWACSPNE

EN/EX - SQL DI

The number of entry/exit events performed by SQL Data Insights functions.

Field Name: QWASDINO

EN/EX-UDF

The number of SQL entry/exit events performed by user-defined functions.

This is only calculated if accounting class 2 is active.

Field Name: QWACUDNE