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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM86069

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-02

  • Closed date

    2013-06-21

  • Last modified date

    2013-08-02

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM86069

Modified date:

2013-08-02

Translate my page

Machine Translation

Content navigation