IBM Support

PH04273: INCORROUT CAN OCCUR WHEN QUERY HAS A SIDEWAY REFERENCE AND A JOIN PREDICATE IS PUSHED DOWN

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Incorrect output can OCCUR when a query has a sideway reference
    and a join predicate is pushed down. The sideway reference
    can be explicit or done internally.
    Here is an example query that could have the issue:
    SELECT DISTINCT B.R1
                  , B.D1
                  , A.U1 AS AREA1
                  , B.U1 AS FLT1
    FROM D123.TB1 A,
    (SELECT TAB.R1, TAB.D1, USR.U1
          , USR.RPT1
      FROM D123.TB1 USR,
         (SELECT DISTINCT R1, D1, FLT1
           FROM D123.MYVIEW1
         ) TAB
      WHERE USR.R1 = TAB.R1
      AND USR.D1 = TAB.D1
      AND USR.U1 = TAB.FLT1
      AND USR.CD1 = 1
      AND USR.RPT1 <> 0) B
    WHERE A.R1 = B.R1
      AND A.D1 = B.D1
      AND A.SPV1 = B.RPT1
      AND A.U1 =   'XXX YYYYY'   ;
    .
    As an example the join predicate A.R1=B.R1 could be
    pushed down into the view MYVIEW1 .
    In the reported issue no rows were found/returned NRF .
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Db2 12 for z/OS users who have a query                       *
    * using join predicate pushdown and a                          *
    * sideway reference                                            *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Incorrect output or ABEND04E                                 *
    * RC00E70005 in DSNXOGA M014 can                               *
    * occur for a query using join                                 *
    * predicate pushdown and a sideway                             *
    * reference                                                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    Incorrect output (too few rows returned)
    or ABEND04E RC00E70005 in DSNXOGA M014 can occur for a query
    when a join predicate is pushed down into a materialized view
    and the same query block includes a sideway reference.
    
    For example:
    SELECT DISTINCT B.R1
                  , B.D1
                  , A.U1 AS AREA1
                  , B.U1 AS FLT1
    FROM D123.TB1 A,
    (SELECT TAB.R1, TAB.D1, USR.U1
          , USR.RPT1
      FROM D123.TB1 USR,
         (SELECT DISTINCT R1, D1, FLT1
           FROM D123.MYVIEW1
         ) TAB
      WHERE USR.R1 = TAB.R1
      AND USR.D1 = TAB.D1
      AND USR.U1 = TAB.FLT1
      AND USR.CD1 = 1
      AND USR.RPT1 <> 0) B
    WHERE A.R1 = B.R1
      AND A.D1 = B.D1
      AND A.SPV1 = B.RPT1
      AND A.U1 =   'XXX YYYYY'   ;
    
    Predicate A.R1=B.R1 is pushed down into MYVIEW1.  The sideway
    reference may be generated internally by Db2.
    
    Additional Keywords:
    SQLINCORROUT SQLINCORR INCORROUT DB2INCORR/K ZSA3
    SQLTABLEEXPR SQLVIEW SQLTABLEUDF SQLTUDF SQLPREDPUSHDOWN
    

Problem conclusion

  • Db2 code is updated to correct the incorrect output
    and remove the abend.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PH04273

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-10-18

  • Closed date

    2019-01-04

  • Last modified date

    2019-02-02

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

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

    UI60523

Modules/Macros

  • DSNXORPP DSNXODAO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI60523

       UP19/01/19 P F901 ¢

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 February 2019