IN-SQL and IN-DB2 time
Time type | Elapsed | CPU | zIIP |
|---|---|---|---|
IN-SQL | Clock time that elapses while a SQL statement runs, regardless of the number of parallel tasks | CPU time that accumulates on general and zIIP processors for SQL runs | CPU time accumulated on zIIP processors for SQL runs |
IN-DB2 | Accounting class 2 clock time that accumulates across all tasks for setup, running, and termination of an SQL statement | Accounting class 2 CPU time that accumulates on general processors only for setup, running, and termination of an SQL statement. | Accounting class 2 CPU time that accumulates on zIIP processors only for setup, running, and termination of an SQL statement. |
IN-SQL elapsed time provides the most accurate data available for determining the cost of running an SQL statement. It excludes time spent on associated Db2 housekeeping tasks, and measures only the time spent running an SQL statement. Measurements based on IN-DB2 time are less focused and overestimate the actual cost of SQL statement runs.
The following figure illustrates an SQL statement run within the context of class 2 (IN-DB2) elapsed time class 1 (in-application) elapsed time. The SQL statement on the left depicts the three distinct phases of SQL statement run: prologue time, IN-SQL elapsed time, and epilogue time.
Prologue time
Prologue time is the first phase of activity during an SQL statement run. Examples of tasks performed during prologue time include:
- Thread creation or reuse
- Plan allocation
- Authorization checking
- Determination of the implicit qualifier to use for unqualified tables
IN-SQL time
IN-SQL time is the second phase of activity during an SQL statement run, 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
Epilogue time is the third phase of activity during an SQL statement run. Examples of tasks performed during epilogue time include:
- 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 the time that Db2 spent on a general processor, not on a zIIP processor. For IN-SQL times, CPU time includes zIIP time.
workload reports compare costs of statements by using IN-SQL time to identify tuning opportunities that economize on both general CP and zIIP processing.
Nested time
Time nesting in SQL runs 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.
IN-SQL elapsed and CPU times for a CALL statement includes time spent or consumed in the statements that are run under the calls stored procedure. When a report reports elapsed or CPU time above the statement level (for example, program level), the nested time is not included.
Parallel query time
For a query that runs 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 an accumulation of all child task elapsed times.
Parallel query nested time
When a parallel query runs, tasks can run even after the query has finished. When this happens, parent statements cannot accumulate nested CPU or zIIP time. As a result, a parent statement might show less CPU or zIIP time than the nested statement that performs the parallel query.