A fix is available
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