PM86069: ABEND04E RC00E2000F AT DSNSVSFB OFFSET0858 MAY BE ISSUED WHEN SQL INVOLVES UNION AND FIELDPROC, SQLCODE415 SHOULD BE ISSUED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E2000F at DSNXEFB .DSNSVSFB+0858 is issued because
  block trailer is overlaid. The block is allocated by module
  DSNXELX for DVS. The failing SQL involves UNION and FIELDPROC.
  
  Each branch of UNION needs to have the same FIELDPROC for the
  corresponding select list item, otherwise, DB2 should issue
  SQLCODE415 instead of ABEND04E RC00E2000F.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and 10 for z/OS user of using      *
  *                 field procedure (fieldproc) within set       *
  *                 operation (SETOP).                           *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00E2000F at location      *
  *                      DSNXEFB.DSNSVSFB: 0858 may occur when   *
  *                      a SQL statement satisfies all of the    *
  *                      following conditions:                   *
  *                      1. a table expression is defined with   *
  *                         set operation(such as: UNION ALL);   *
  *                      2. a column defined with fieldproc and  *
  *                         a constant are referenced in         *
  *                         different SELECT list of full-select *
  *                         under set operation from step #1;    *
  *                      3. an aggregate function with parameter *
  *                         which is from the table expression   *
  *                         defined in step #1 and corresponds   *
  *                         to the column defined with fieldproc *
  *                      4. a GROUP BY clause is used.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00E2000F at location DSNXEFB.DSNSVSFB: 0858 may
  occur when a SQL statement satisfies all of the following
  conditions:
  1. a table expression is defined with set operation (such as:
     UNION ALL);
  2. a column defined with fieldproc and a constant are
     referenced in different SELECT list of full-select under the
     set operation from step #1;
  3. an aggregate function with parameter which is from the
     table expression defined in step #1 and corresponds to the
     column defined with fieldproc;
  4. a GROUP BY clause is used.
  
  The following example helps to illustrate this problem:
  DDL:
  CREATE TABLE TEST.TB
  (C1 CHAR(4) FIELDPROC FPCVD4,
   C2 VARCHAR(4));
  
  INSERT INTO TEST.TB VALUES('ABCD', 'EFGH');
  
  SQL:
  SELECT MAX(COL1), COL2
  FROM (SELECT C1, C2
        FROM TEST.TB
          UNION ALL
        SELECT 'EFGH', C2
        FROM TEST.TB) AS TE1 (COL1, COL2)
  WHERE COL1 LIKE 'ABCD%'
  GROUP BY COL2;
  
  DB2 missed to issue SQLCODE -415 to block above usage which
  caused ABEND.
  

Problem conclusion

• DB2 has been modified correctly to issue SQLCODE -415 to block
  aforementioned usage for avoiding ABEND.
  
  Additional keywords: SQLFIELDPROC, SQLUNIONALL, SQLGROUPBY
  

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:

  UK95318 UK95319

Modules/Macros

• DSNXOCAS DSNXODSO DSNXOVD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK95318

     UP13/07/09 P F307

• R910 PSY UK95319

     UP13/07/09 P F307

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.