PI25743: ABEND04E RC00E72018 WHEN A MISMATCHED JOIN PREDICATE IS FOUND WITH PARALLELISM SORT.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E72018 when a mismatched join predicate
  is found. The mismatch can be either the same data type
  but a different length or different nullability. When
  this occurs DB2 needs to use regular SORT instead of the
  merge-sort for parallelism.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS      *
  *                 parallelism user of sort-merge join with     *
  *                 merge-composite (MERGC = 'Y' in the PLAN_    *
  *                 TABLE).                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00E72018 at DSNXSORI      *
  *                      OFFSET 5E02 may occur when a sort-      *
  *                      merge join query with merge-composite   *
  *                      (MERGC = 'Y' in the PLAN_TABLE) runs in *
  *                      parallel and the sort-merge join join   *
  *                      predicate has different nullability,    *
  *                      data type, or data length.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00E72018 at DSNXSORI OFFSET 5E02 may occur when a
  sort-merge join query with merge-composite (MERGC = 'Y' in the
  PLAN_TABLE) runs in parallel and the sort-merge join join
  predicate has different nullability, data type, or data length.
  
  When DB2 processes the merge-composite, it does not handle the
  mismatched join predicate correctly in parallel code path.
  So, SORT misinterprets the value of the join column and caused
  the abend.
  
  Following is an example of such failing query:
  SELECT V3.C1, V1.C4, V2.C4, V3.C5, V3.C6
    FROM VIEW1 V1, VIEW2 V2, VIEW3 V3
   WHERE ((V3.C1 BETWEEN '2014-08-01' AND '2014-08-31'))
     AND V3.C2 = V2.C2
     AND V3.C3 = V1.C3
     FOR FETCH ONLY
  ;
  
  NOTE: VIEW1, VIEW2, and VIEW3 are complex VIEWs.
  

Problem conclusion

• DB2 is modified to handle the mismatched join predicate of
  sort-merge join with merge-composite correctly in parallelism.
  
  Additional Keywords: PARALLELISM SQLPARALLELISM DB2PARALL/K
                       SQLSMJ SQLMSJ SMJ MSJ ABEND04E
                       RC00E72018 DSNXSORI OFFSET5E02
  

Temporary fix

Comments

APAR Information

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

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

  UI23245 UI23246

Modules/Macros

•    DSNXOGP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI23245

     UP14/12/06 P F412

• RB10 PSY UI23246

     UP14/12/06 P F412

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.