Displaying Query Plans with onmode -q explain Commands

Version 8.32 of Extended Parallel Server introduced the -q explain option to the onmode utility to turn SET EXPLAIN output on and off for a specified session, using the following syntax:

>>-onmode -q explain--session_id-------------------------------->
 
>--+-+-file-------+--filename-+--------------------------------><
   | '-fileappend-'           |
   +-on-----------------------+
   +-off----------------------+
   '-avoid execute------------'
 

Here session_id specifies the identifying number of a session.

The filename specifies the file to which output is written, and can include a pathname from the current working directory. If you include no pathname, then filename is created or updated in the current working directory.

The keywords that follow session_id have the following effects:

• file writes the query optimizer plans of subsequently executed DML statements of the session to the specified filename. If this file is not empty, then the database server overwrites its contents with the query plan.
• fileappend writes the query optimizer plans of subsequently executed DML statements to the specified filename. If this file is not empty, then the database server appends subsequent query optimizer plans to its existing contents.
• on appends the query optimizer plans of subsequent DML statements to the file sqexplain.out in the current working directory of the session.
• off disables the effect of a previous onmode -q explain on command, so that query plans of subsequent DML statements are not written to a file.
• avoid_execute writes the query optimizer plans for subsequent DML statements that the session issues, but prevents execution of those DML statements.

These command line options to the onmode -q explain session_id utility correspond to options of the SET EXPLAIN statement of SQL:

• The on option is equivalent to SET EXPLAIN ON
• The off option is equivalent to SET EXPLAIN OFF
• The file filename option is equivalent to SET EXPLAIN FILE TO filename
• The fileappend filename option is equivalent to SET EXPLAIN FILE TO filename WITH APPEND
• The avoid_execute option is equivalent to SET EXPLAIN ON AVOID_EXECUTE

See the IBM Informix Guide to SQL: Syntax for more information about these options to the SET EXPLAIN statement of SQL.

You must run the onmode -q explain session_id command on the coserver to which the specified session is connected, or else the command fails with an error.

In versions 8.50 and earlier of Extended Parallel Server, the resulting output from onmode -q explain session_id commands is restricted to the most recently executed query plan, but in most contexts there is no facility of XPS that can display the plan of a query that the database server is currently executing.