SET EXPLAIN

>>-SET EXPLAIN-------------------------------------------------->
 
>--+-+-OFF-------------------+-------------------------+-------><
   | '-ON--+---------------+-'                         |
   |       '-AVOID_EXECUTE-'                           |
   |  (1)                                              |
   '--------FILE TO--+-'filename '--+--+-------------+-'
                     +-filename_var-+  '-WITH APPEND-'
                     '-expr---------'

Notes:

Extended Parallel Server only

Element	Description	Restrictions	Syntax
expr	Expression that returns a filename specification	Must return a string satisfying the restrictions on filename	Expression, p. Expression
filename	Path and filename of the file to receive the output. For the default, see Location of the Output File.	Must conform to operating- system rules. If an existing file, see Using the WITH APPEND Option.	Quoted String, p. Quoted String
filename_var	Host variable that stores filename	Must be a character data type	Language specific

Output from a SET EXPLAIN ON statement is directed to the appropriate file until you issue a SET EXPLAIN OFF statement or until the program ends. If you do not enter a SET EXPLAIN statement, then the default behavior is OFF, and the database server does not generate measurements for queries.

The SET EXPLAIN statement executes during the database server optimization phase, which occurs when you initiate a query. For queries that are associated with a cursor, if the query is prepared and does not have host variables, optimization occurs when you prepare it. Otherwise, optimization occurs when you open the cursor.

The SET EXPLAIN statement provides various measurements of the work involved in performing a query.

Option: Effect
ON: Generates measurements for each subsequent query and writes the results to an output file in the current directory. If the file already exists, new output is appended to the existing file.
AVOID_EXECUTE: Prevents a SELECT, INSERT, UPDATE, or DELETE statement from executing while the database server prints the query plan to an output file
OFF: Terminates activity of the SET EXPLAIN statement, so that measurements for subsequent queries are no longer generated or written to the output file
FILE TO: Generates measurements for each subsequent query and allows you to specify the location for the explain output file. If the file already exists, new output overwrites the contents of the file unless you use the WITH APPEND option.

SET EXPLAIN

Syntax

Usage