Using the AVOID_EXECUTE Option

The AVOID_EXECUTE keyword prevents DML statements from executing. Instead, the database server prints the query plan to an output file.

The SET EXPLAIN ON AVOID_EXECUTE statement activates the Avoid Execute option for a session, or until the next SET EXPLAIN OFF (or ON) without AVOID_EXECUTE. If you activate AVOID_EXECUTE for a query that contains a remote table, the query does not execute at either the local or remote site.

The following example stores the output in the specified file. 
SET EXPLAIN ON AVOID_EXECUTE;
SET EXPLAIN FILE TO '/tmp/explain.out';
When AVOID_EXECUTE is set, the database server sends a warning message. If you are using DB-Access, it displays a text message 
Warning! avoid_execute has been set
for any select, delete, update or insert query operations. From ESQL, the sqlwarn.sqlwarn7 character is set to 'W'.

Use the SET EXPLAIN ON or the SET EXPLAIN OFF statement to turn off the AVOID_EXECUTE option. The SET EXPLAIN ON statement turns off the AVOID_EXECUTE option but continues to generate a query plan and writes the results to an output file.

If you issue the SET EXPLAIN ON AVOID_EXECUTE statement in an SPL routine, the SPL routine and any DDL statements still execute, but the DML statements inside the SPL routine do not execute. The database server prints the query plan of the SPL routine to an output file. To turn off this option, you must execute the SET EXPLAIN ON or the SET EXPLAIN OFF statement outside the SPL routine. If you execute the SET EXPLAIN ON AVOID_EXECUTE statement before you execute an SPL routine, the DML statements inside the SPL routine do not execute, and the database server does not print a query plan of the SPL routine to an output file.

Nonvariant functions in a query are still evaluated when AVOID_EXECUTE is in effect, because the database server calculates these functions before optimization.

For example, the func( ) function is evaluated, even though the following SELECT statement is not executed: 
SELECT * FROM orders WHERE func(10) > 5;

For other performance implications of the AVOID_EXECUTE option, see your HCL OneDB™ Performance Guide.

If you execute the SET EXPLAIN ON AVOID_EXECUTE statement before you open a cursor in the program, each FETCH operation returns the message that the row was not found. If you execute SET EXPLAIN ON AVOID_EXECUTE after the program opens a cursor, however, this statement has no effect on the cursor, which continues to return rows.