PI37820: ABEND0C4 RC00000038 IN DSNXOB2 OFFSET699A WHEN TRYING TO PREPARE OR BIND A COMPLEX QUERY WITH OUTER JOINS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND0C4 RC00000038 IN DSNXOB2 OFFSET699A WHEN TRYING TO PREPARE
  OR BIND A COMPLEX QUERY WITH OUTER JOINS
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of a SQL statement *
  *                 containing INNER JOIN and OUTER JOIN with ON *
  *                 clause referencing the YEAR, DATE, or SUBSTR *
  *                 function.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND0C4 RC00000038 at location      *
  *                      DSNXGRDS DSNXOB2 OFFSET699A may occur   *
  *                      when a statement satisfies all of the   *
  *                      following conditions:                   *
  *                                                              *
  *                      1. The statement contains INNER and     *
  *                         OUTER JOINs.                         *
  *                      2. The statement contains a WHERE       *
  *                         clause with function YEAR, DATE, or  *
  *                         SUBSTR.                              *
  *                      3. The statement contains an ON clause  *
  *                         with function YEAR, DATE, or SUBSTR. *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following example helps to illustrate the problem.
  
  Step 1. Create TABLE T1, T2, T3, T4 and VIEW V1.
  
  CREATE TABLE T1(
    C1 CHAR(3),
    C2 DATE
  );
  
  CREATE TABLE T2(
    C1 CHAR(2),
    C2 DATE,
    C3 CHAR(4)
  );
  
  CREATE TABLE T3(
    C1 TIMESTAMP,
    C2 DATE,
    C3 CHAR(3)
  );
  
  CREATE TABLE T4(
    C1 CHAR(3),
    C2 CHAR(3),
    C3 CHAR(1)
  );
  
  CREATE VIEW V1 (D1, D2) AS
    SELECT ALL T3.C1, T3.C2
    FROM T3 LEFT OUTER JOIN T4
    ON T3.C3 NOT IN ('   ','EUR','DM ')
    AND T4.C2 = T3.C3
    AND T4.C1 = 'EUR'
    AND T4.C3 = 'B';
  
  Step 2. The statement which can cause ABEND0C4.
  
  SELECT 1
  FROM T1 INNER JOIN V1
  ON V1.D1  > '2015-03-03-09.11.22.000000'
  AND YEAR(V1.D2) > YEAR(DATE('03.03.2015'))
  LEFT JOIN T2
  ON T2.C1 = T1.C1
  WHERE YEAR(V1.D2) > YEAR(DATE('03.03.2015'));
  
  DB2 does not process the ON clause with the YEAR function
  properly which can result in the ABEND.
  
  After applying this APAR, access path may be changed.
  

Problem conclusion

• DB2 has been modified correctly to process ON clause
  with function YEAR, DATE, or SUBSTR to avoid the ABEND.
  
  Additional keywords: SQLWHERE SQLDATE
                       SQLYEAR SQLOUTERJOIN SQLOUTER
                       SQLFULLJOIN SQLFULL SQLJOIN
                       SQLLEFTJOIN SQLLEFT SQLRIGHTJOIN
                       SQLRIGHT
  

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:

  UI27725

Modules/Macros

• DSNXOONY
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI27725

     UP15/06/02 P F506

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.