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

Obtain the fix for this APAR.

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