IN-SQL and IN-DB2 time

Apptune

tracks time spent (elapsed time) and processor cycles consumed (CPU time) in SQL statements.

Apptune

reports show IN-SQL time and IN-DB2 time as displayed in the following table.

Time type	Elapsed	CPU	zIIP
IN-SQL	Clock time that elapses while a SQL statement executes, regardless of the number of parallel tasks	CPU time that accumulates on general and zIIP processors for SQL execution	CPU time accumulated on zIIP processors for SQL execution
IN-DB2	Accounting class 2 clock time that accumulates across all tasks for setup, execution, and termination of a SQL statement	Accounting class 2 CPU time that accumulates on general processors only for setup, execution, and termination of a SQL statement	Accounting class 2 CPU time that accumulates on zIIP processors only for setup, execution, and termination of a SQL statement

IN-SQL elapsed time provides the most accurate data available for determining the cost of executing an SQL statement. It excludes time spent on associated Db2 housekeeping tasks, and measures only the time spent executing an SQL statement. Measurements based on IN-DB2 time are less focused and overestimate the actual cost of SQL statement execution.

The following figure illustrates execution of an SQL statement within the context of class 2 (IN-DB2) elapsed time and the context of class 1 (in-application) elapsed time. The SQL statement on the left depicts the three distinct phases of SQL statement execution: prologue time, IN-SQL elapsed time, and epilogue time.

SQL statement execution measurement

Prologue time

The first phase of activity during the execution of an SQL statement. Examples of tasks performed during prologue time include:

Thread creation/reuse
Plan allocation
Authorization checking
Determination of the implicit qualifier to use for unqualified tables

IN-SQL time

The second phase of activity during the execution of an SQL statement, when an SQL query is satisfied. Examples of tasks performed during IN-SQL time include:

Parsing of dynamic SQL statements
Logical and physical I/O processing to access data
Required data set allocation and deallocation
Log writes

Epilogue time

The third phase of activity during the execution of an SQL statement. Examples of tasks performed during epilogue time include the following:

Return of result set
IFCID creation
Commit processing
Rollback processing
Thread termination

zIIP time and IN-DB2 time

For IN-DB2 times, CPU time includes only time spent on a general processor. Time that Db2 spends on a zIIP processor is excluded. For IN-SQL times, CPU time includes zIIP time. Apptune workload reports compare costs of statements using IN-SQL time in order to identify tuning opportunities that economize on both general CP and zIIP processing.

Nested time

Nesting of SQL execution occurs for the following types of statements:

Stored procedure CALL statements
Statements that generate triggers or user-defined functions

IN-SQL timings at the statement level includes nested time.

Example

In-SQL elapsed and CPU times for a CALL statement includes time spent or consumed in the statements that are executed under the called stored procedure. When reporting elapsed or CPU time above the statement level (for example, program level), the nested time is not included.

If Program A calls Stored Procedure B, at the statement level you see IN-SQL times for A's CALL statement that includes activity that occurred under Stored Procedure B. If you look at a program report for the same event, the times reported for Program A do not include time captured under Stored Procedure B. IN-DB2 timings that Apptune reports do not include nested time.

Parallel query time

For a query that executes in parallel:

IN-DB2 elapsed time is an accumulation of all tasks that execute the query in parallel.
IN-SQL elapsed time is a report of how much time elapsed while the query was in progress. It is not is not an accumulation of all child task elapsed times.