PM81771: INCORROUT NRF, SQLCODE100 IS RETURNED WHEN A SQL WITH UNION ALL IN SUBQUERY EXECUTES SECOND TIME

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • This issue is about incorrout of query with UNION ALL in the
    subquery. The first run can return correct result, but the
    SQLCODE 100 will be returned for the second run.
    
    eg:
    SELECT B3.C1
    FROM ( SELECT T1.C1
        FROM T1
        UNION ALL
        SELECT T2.C1
        FROM T2
        FETCH FIRST 1 ROWS ONLY ) T3
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of UNION ALL       *
    *                 with FETCH FIRST n ROWS ONLY clause.         *
    ****************************************************************
    * PROBLEM DESCRIPTION: This apar fixes two problems for UNION  *
    *                      ALL when the FETCH FIRST n ROWS ONLY    *
    *                      clause is used against a UNION ALL      *
    *                      result.                                 *
    *                                                              *
    *                      1. INCORROUT happens when a UNION       *
    *                         ALL with FETCH FIRST n ROWS ONLY     *
    *                         clause is used against the UNION     *
    *                         ALL result in a loop.                *
    *                                                              *
    *                      2. DB2 incorrectly issue SQLCODE -811   *
    *                         when a UNION ALL with FETCH FIRST    *
    *                         n ROWS ONLY clause is used against   *
    *                         a UNION ALL result and the result    *
    *                         is used in an INSERT with SELECT     *
    *                         clause for MERGE                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The following two examples illustrate the problems.
    
    Example 1.
    
    1. Create a table and populate it.
    
       CREATE TABLE T1 (ID INT, ID2 INT, C1 CHAR(10));
    
       INSERT INTO T1 VALUES (1, 1, 'A1');
       INSERT INTO T1 VALUES (2, 2, 'A2');
    
    2. SELECT INTO from a UNION ALL with FETCH FIRST 1 ROWS ONLY
       clause.
    
      DCL HVC CHAR(10) VARYING;
      DCL ID  BIN FIXED(31);
    
      DO ID = 1 TO 2 WHILE(SQLCODE >= 0 );
    
       EXEC SQL
        SELECT B1.C1
        INTO :HVC FROM
         (SELECT C1
           FROM T1
           WHERE ID = :ID
           UNION ALL
          SELECT C1
           FROM T1
           WHERE ID2 = :ID
         FETCH FIRST 1 ROWS ONLY) B1
        WITH UR;
    
      END;
    
    The application expects HVC return values of A1 and A2, but DB2
    only returns A1 and incorrectly returns SQLCODE +100 for the
    second row.  DB2 code does not clean up the internal counter for
    FETCH FIRST 1 ROWS ONLY for UNION ALL before the second SELECT
    INTO in the loop, and it leads to SQLCODE +100 eventually.
    
    Example 2.
    
    1. Create a table and populate it.
    
       CREATE TABLE T1 (ID INT, C1 CHAR(10));
    
       INSERT INTO T1 VALUES (1, 'A1');
       INSERT INTO T1 VALUES (1, 'A2');
    
    2.INSERT with SELECT from UNION ALL with FETCH FIRST 1 ROWS ONLY
      for a MERGE statement
    
      DCL ID BIN FIXED(31);
      DCL ID3 BIN FIXED(31);
      DCL HVC CHAR(10) VARYING;
    
      ID = 20;
      ID3 = 1;
      HVC = 'A20';
    
      EXEC SQL
       MERGE INTO T1 T11
       USING (VALUES (:ID, :HVC)
       FOR 1 ROWS)
       AS MT1 (ID, C1)
       ON (T11.ID = MT1.ID)
       WHEN MATCHED THEN UPDATE SET C1=MT1.C1
       WHEN NOT MATCHED THEN INSERT (C1)
        VALUES(SELECT B1.C1 FROM (
              SELECT C1 FROM T1
              WHERE ID = :ID3
              UNION ALL
              SELECT C1 FROM T1
              WHERE ID = :ID3
              FETCH FIRST 1 ROW ONLY) B1)
       NOT ATOMIC CONTINUE ON SQLEXCEPTION;
    
    DB2 is expected to insert a new row with C1='A20' into table T1,
    but instead DB2 incorrectly returns SQLCODE -811.  DB2 code does
    not set internal structure for FETCH FIRST 1 ROWS ONLY with
    UNION ALL correctly, and it eventually leads to -811.
    
    Please note, to make the fix for the second problem effective
    for a static application, it must be rebound after application
    of this PTF.
    

Problem conclusion

  • DB2 code has been fixed to address the above two problems.
    
    Additional Keywords: SQLUNIONALL SQLCODE811 SQLINCORR
                         INCORROUT SQLINCORROUT DB2INCORR/K
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM81771

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-01-31

  • Closed date

    2013-03-12

  • Last modified date

    2013-04-02

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

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

    UK92460

Modules/Macros

  •    DSNXGMRG DSNXRUNA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK92460

       UP13/03/27 P F303

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

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM81771

Modified date:

2013-04-02

Translate my page

Machine Translation

Content navigation