IT00257: USING "SET EXPLAIN ON AVOID_EXECUTE" AND "SET EXPLAIN FILE TO <L OCATION>" AS TWO STATEMENTS IGNORES THE AVOID_EXECUTE DIRECTIVE

APAR status

• Closed as program error.

Error description

• In the 12.10 documentation, we show the following for using the
  AVOID_EXECUTE directive when gathering an explain plan:
  http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.
  ibm.sqls.doc/ids_sqs_1153.htm
  
  SET EXPLAIN ON AVOID_EXECUTE;
  SET EXPLAIN FILE TO '/tmp/explain.out';
  
  However, if you run these two commands in the sequence
  documented, as separate statements, you get both the warning
  that AVOID_EXECUTE is active AND a successful execution of the
  query with a result set.   For example, if I run the following
  against the stores_demo database's customer table:
  
  set explain on AVOID_EXECUTE;
  set explain file to '/tmp/explain.out';
  select FIRST 1 * from customer;
  
  You receive the following output:
  
  customer_num  101
  fname         Ludwig
  lname         Pauli
  company       All Sports Supplies
  address1      213 Erstwild Court
  address2
  city          Sunnyvale
  state         CA
  zipcode       94086
  phone         408-789-8075
  
   Warning! avoid_execute has been set
   1 row(s) retrieved.
  
  
  
  Note how the warning that avoid_execute has been set is
  displayed, yet clearly the statement executes and returns a
  result set.
  
  This does not occur if you reverse the order and execute the
  statements as:
  
  set explain file to '/tmp/explain.out';
  set explain on AVOID_EXECUTE;
  

Local fix

• This does not occur if you reverse the order and execute the
  statements as:
  
  set explain file to '/tmp/explain.out';
  set explain on AVOID_EXECUTE;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * 11.70 users through 11.70.xC8                                *
  * 12.10 users through 12.10.xC4                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-11.70.xC9 or IDS-12.10.xC5                     *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-11.70.xC9 and IDS-12.10.xC5
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RB70 PSN

     UP

• RC10 PSN

     UP