IBM Support

IT03174: RUNNING A PROCEDURE WHICH CONTAINS MULTIPLE SUB-QUERIES ON VIEWS CAN RETURN ERROR -5849 OR -640 WITH NO_SUBQF SET

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

  • Running a complex stored procedure with multiple sub queries
    against views can fail with error -5849
    
      -5849   The sub-query flattening settings for the query plan
    and the current
      value of NO_SUBQF environment variable do not match.
    
    Setting NO_SUBQF in the environment does not help, as the same
    query then fails with error -640
    
      -640    QPlan sanity failure line-number.
    
    This is observed after a fresh dbimport of the database. Running
    update statistics does not correct the problem.
    
    If you set a trap for error -5849 using 'onmode -I -5849', you
    will observe the following stack in the AF file it generates:
    
      (oninit) afstack
      (oninit) afhandler
      (oninit) affail_interface
      (oninit) check_traperror
      (oninit) sqerr
      (oninit) sqerr1
      (oninit) d_qplan
      (oninit) qd_getqplan
      (oninit) ip_evalsql
      (oninit) runproc
      (oninit) udrlm_spl_execute
      (oninit) udrlm_exec_routine
      (oninit) udr_execute
      (oninit) exroutine
      (oninit) execproc
      (oninit) aud_execproc
      (oninit) excommand
      (oninit) sq_execute
      (oninit) sqmain
      (oninit) spawn_thread
      (oninit) th_init_initgls
      (oninit) startup
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users of 12.10.xC1 through 12.10.xC4.                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Execution of a stored procedure may return -5849 or -640     *
    * errors under the following conditions:                       *
    *   - a SQL statement inside the stored procedure references a *
    * view that is materialized into a temp table                  *
    *   - the view definition has a correlated subquery that is    *
    * internally transformed into a join (subquery does not        *
    * contain any complex operations such as group by,             *
    * aggregation, remote references, references to VTI/VII, etc)  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to IDS-12.10.xC5                                      *
    ****************************************************************
    

Problem conclusion

  • Problem Fixed In IDS-12.10.xC5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT03174

  • 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-07-15

  • 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

  • RC10 PSN

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 October 2015