IBM Support What's New?

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

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 number

    IT00257

  • Reported component name

    INFORMIX SERVER

  • Reported component ID

    5725A3900

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-12

  • Closed date

    2015-10-16

  • Last modified date

    2015-10-16

  • 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



Document information

More support for: Informix Servers

Software version: C10

Reference #: IT00257

Modified date: 2015-10-16