DB2 Version 9.7 for Linux, UNIX, and Windows

QUERY_PREP_COST administrative view - Retrieve statement prepare time information

The QUERY_PREP_COST administrative view returns a list of statements with information about the time required to prepare the statement.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the QUERY_PREP_COST administrative view
CONTROL privilege on the SNAPAGENT administrative view
DATAACCESS authority

Example

Retrieve a report on the queries with the highest percentage of time spent on preparing.

SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, PREP_TIME_PERCENT, 
   SUBSTR(STMT_TEXT, 1, 30) AS STMT_TEXT, DBPARTITIONNUM 
   FROM SYSIBMADM.QUERY_PREP_COST ORDER BY PREP_TIME_PERCENT

The following example is a sample output for this query.

NUM_EXECUTIONS     AVERAGE_EXECUTION_TIME_S ...
--------------...- ------------------------ ...
                 1                       25 ...

  1 record(s) selected.

Output for this query (continued).

... PREP_TIME_PERCENT STMT_TEXT                      DBPARTITIONNUM
... ----------------- ------------------------------ --------------
...               0.0 select * from dbuser.employee               0

Usage notes

When selecting from the view, an order by clause can be used to identify queries with the highest prep cost. You can examine this view to see how frequently a query is run as well as the average execution time for each of these queries. If the time it takes to compile and optimize a query is almost as long as it takes for the query to execute, you might want to look at the optimization class that you are using. Lowering the optimization class might make the query complete optimization more rapidly and therefore return a result sooner. However, if a query takes a significant amount of time to prepare yet is executed thousands of times (without being prepared again) then the optimization class might not be an issue.

Information returned

Table 1. Information returned by the QUERY_PREP_COST administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
NUM_EXECUTIONS	BIGINT	num_executions - Statement executions
AVERAGE_EXECUTION_TIME_S	BIGINT	Average execution time (in seconds).
AVERAGE_EXECUTION_TIME_MS	BIGINT	Average execution time (fractional, in microseconds).
PREP_TIME_MS	BIGINT	prep_time_worst - Statement worst preparation time (in milliseconds).
PREP_TIME_PERCENT	DECIMAL(8,2)	Percent of execution time spent on preparation. Calculated as prep time divided by total execution time.
STMT_TEXT	CLOB(2 M)	stmt_text - SQL statement text
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.