PI27560: INCORROUT USING APPLCOMPAT V11R1 WITH NATIVE STORED PROCEDURE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output results when a native stored procedure is
  created with APPLCOMPAT V11R1. When the procedure is executed,
  no rows are returned.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of a native SQL    *
  *                 procedure with an SQL statement containing a *
  *                 table alias and an SQL variable.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output could happen for an    *
  *                      SQL statement containing a table alias  *
  *                      and an SQL variable in a native SQL     *
  *                      procedure created without the option    *
  *                      ARCHIVE SENSITIVE NO, SYSTEM_TIME       *
  *                      SENSITIVE NO and BUSINESS_TIME          *
  *                      SENSITIVE NO. YES is the default value  *
  *                      for those options.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(
    C1 CHAR(3),
    C2 INT
  )
  #
  
  CREATE ALIAS A1 FOR T1
  #
  
  CREATE PROCEDURE SP1 (INOUT NUM INT)
  LANGUAGE SQL
   L1: BEGIN
    DECLARE A CHAR(2);
    DECLARE B INT;
    SET A   = 'AB';
    SET B   = 1;
  
    SELECT C2 INTO NUM
    FROM A1
    WHERE C1 = A
          AND C2 = B;
   END L1
  #
  
  DB2 did not correctly process the aforementioned SQL statement,
  which caused the incorrect output.
  

Problem conclusion

• DB2 has been modified to correctly process the SQL statement
  containing a table alias and an SQL variable in a native SQL
  procedure created without the option ARCHIVE SENSITIVE NO,
  SYSTEM_TIME SENSITIVE NO and BUSINESS_TIME SENSITIVE NO, so
  there will be no incorrect output.
  
  Additional keywords: SQLINCORR INCORROUT SQLINCORROUT
                       DB2INCORR/K SQLALIAS SQLSPTT SQLAPTT
                       SQLBITEMPORAL
  

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:

  UI24477

Modules/Macros

•    DSNXOOP  DSNXOP0  DSNXOYPB DSNXOYPL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI24477

     UP15/02/03 P F502 Ž

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.