PI13724: Support suppression of +394/+395 SQLCODE during query execution

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Many 3rd party produced dynamic SQL applications do not check or
    tolerate +394/+395 SQLCODE when a query is executing.  This
    prevents the successful adoption use of statement hints, since
    introducing a hint can result in an application failure.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 10 and 11 for z/OS users of optimization *
    *                 hints.                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION: Many 3rd party produced dynamic SQL     *
    *                      applications do not check or tolerate   *
    *                      +394/+395 SQLCODE when a query is       *
    *                      executing.  This prevents the           *
    *                      successful use of statement hints,      *
    *                      since introducing a hint can result in  *
    *                      an application failure.                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR provides 2 usability enhancements for optimization
    hints.
    
    1. Many applications do not handle SQLCODEs +394/+395 when the
    optimization hint is applied to a dynamic SQL execution.
    Therefore, these customers cannot use optimization hints to
    influence access path selection for a dynamic query.
    
    This APAR introduces subsystem parameter
    SUPPRESS_HINT_SQLCODE_DYN to control whether SQLCODE +394/+395
    is returned for a dynamic query with optimization hint applied.
    
    Valid settings for SUPPRESS_HINT_SQLCODE_DYN are:
    
      * NO  : Means that DB2 will return SQLCODE +394 (if all hints
              are used) or SQLCODE +395 (if any hint was invalid).
              This is the default setting.
      * STMT: Means that DB2 will suppress SQLCODEs +394 and +395
              for statement level optimization hints if a statement
              level hint is applied successfully or unsuccessfully
              for a dynamic SQL execution.
      * ALL : Means that DB2 will suppress SQLCODEs +394 and +395
              for all optimization hints, including statement level
              optimization hints and classic PLAN_TABLE optimization
              hints, even if the hint is applied successfully or
              unsuccessfully for a dynamic SQL execution.
    
    Static queries and dynamic EXPLAIN statements are not affected
    by this subsystem parameter. DB2 will continue to return
    SQLCODEs +394/+395 when binding static queries or EXPLAINing
    dynamic queries.
    
    2. The cost estimation for a query with an optimization hint
    applied may not be accurate but it is not indicated in EXPLAIN
    output.
    
    To improve the usability of cost estimation with optimization
    hints, the cost estimation of a query with an optimization hint
    applied will be marked as COST_CATEGORY "B" in
    DSN_STATEMNT_TABLE. A new reason for putting the cost estimation
    into cost category B is "OPTIMIZATION HINTS" - "The optimization
    hint is applied to the statement or APREUSE(ERROR/WARN) is
    applied to the package".
    
    Additional Keywords:
    OPTHINT OPTHINTS SQLCODE394 SQLCODE395 SQLEXPLAIN
    

Problem conclusion

  • DB2 has been enhanced to improve the usability of optimization
    hints as described above.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI13724

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-15

  • Closed date

    2014-06-24

  • Last modified date

    2014-08-04

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

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

    UI19080 UI19081

Modules/Macros

  •    DSN@XAZP DSNDQWPZ DSNDQW00 DSNDSPRM DSNTIDXA
    DSNTIDXB DSNTIJUZ DSNTINST DSNTXAZP DSNWZIFA DSNWZIFB DSNXOD3
    DSNXOES5 DSNXOMPS DSNXOOP  DSNXOPCO DSNXOTR1 DSNXOTR2 DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI19080

       UP14/07/10 P F407

  • RB10 PSY UI19081

       UP14/07/10 P F407

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PI13724

Modified date:

2014-08-04

Translate my page

Machine Translation

Content navigation