onmode -Y: Dynamically change SET EXPLAIN
| 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.