onmode -Y: Dynamically change SET EXPLAIN

Read syntax diagramSkip visual syntax diagram
Syntax:

>>-onmode-- -Y--session_id--+-0--------------------+-----------><
                            '-+-2-+--+-----------+-'   
                              '-1-'  '-file_name-'     

Element Purpose Key considerations
file_name The explain output file name. If the file's absolute path is not included, the example output file is created in the default example output file location. If the file exists, explain output is appended to it. If a file exists from the SET EXPLAIN statement, that file is not used until dynamic explain is turned off.
session_id Identifies the specific session. None.
-Y Dynamically change the value of the SET EXPLAIN statement. None.

You can use the SET EXPLAIN statement to display the query plan of the optimizer, an estimate of the number of rows returned, and the relative cost of the query. When you use the onmode -Y command to turn on SET EXPLAIN, the output is displayed in the explain output file.

The onmode -Y command dynamically changes the value of the SET EXPLAIN statement for an individual session. The following invocations are valid with this command:
Invocation Explanation
onmode -Y session_id 2 Turns SET EXPLAIN on for session_id
onmode -Y session_id 1 Turns SET EXPLAIN on for session_id and displays the query statistics section in the explain output file
onmode -Y session_id 1 /tmp/myexplain.out Turns SET EXPLAIN on for session_id and writes explain output to /tmp/myexplain.out.
onmode -Y session_id 0 Turns SET EXPLAIN off for session_id

This command has an equivalent SQL administration API function.


Copyright© 2019 HCL Technologies Limited