PM00174: SELECT IN NATIVE SQLPL RESULTS IN INCORRECT OUTPUT NRF NO ROWS FOUND, NULLS RETURNED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• null values on embedded select in native sql procedure as 0 rows
  are incorrectly fetched on set function. External sql proc
  worked for same scenario.
  
  DB2INCORR/K
  

Local fix

• use extarnal SQL procedure
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users of native sql       *
  *                 procedures.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: This apar addresses the problem of      *
  *                      incorrect output with a nested table    *
  *                      expression containing UNION ALL in a    *
  *                      native  sql procedure when there is a   *
  *                      predicate containing a NOT NULL         *
  *                      column and a parameter. The incorrect   *
  *                      output is also possible when a variable *
  *                      is used in place of the parameter.      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A nested table expression containing UNION ALL in a native sql
  procedure can result in incorrect output when there is a
  predicate containing a NOT NULL column and a parameter. The
  incorrect output is also possible when a variable is used in
  place of the parameter.
  
  The query is transformed such that the predicate is pushed
  down on all branches of UNION ALL. The buffer for the
  second branch of UNION ALL is not correctly initialized
  resulting in incorrect output.
  
  The following example illustrates the problem.
  
  1. Create and populate two tables containing NOT NULL columns.
  
     CREATE TABLE T1 ( C3 INTEGER NOT NULL)#
     CREATE TABLE T2 ( C3 INTEGER NOT NULL)#
     INSERT INTO T1 VALUES(10)#
     INSERT INTO T2 VALUES(30)#
  
  2. Create a stored procedure containing following nested table
  expression.
  
     CREATE PROCEDURE PROC1 (INOUT parm1 INT)
     LANGUAGE SQL
     L1:BEGIN
             SELECT MAX(V1.C3)
             INTO   parm1
             FROM
                    (SELECT C3
                    FROM    T2
  
                    UNION ALL
  
                    SELECT C3
                    FROM   T1
                    ) V1
             WHERE  V1.C3 < parm1 ;
     END L1#
  
  3. After CALL PROC1(:parm1:pind) with parm1 set to 31 and
     pind set to 0, parm1 is assigned the value of 10
     instead of the expected value of 30.
  

Problem conclusion

• DB2 is modified to correctly initialize buffers on all legs of
  UNION ALL when a predicate is pushed down in a nested table
  expression.
  
  Additional Keywords: SQLNATIVESQLPL SQLINCORR SQLUNIONALL
                       SQLINCORROUT
  

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:

  UK54063

Modules/Macros

• DSNXOB2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK54063

     UP10/02/17 P F002

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.