IBM Support

PI27560: INCORROUT USING APPLCOMPAT V11R1 WITH NATIVE STORED PROCEDURE

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PI27560

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-10-14

  • Closed date

    2015-01-18

  • Last modified date

    2015-03-03

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



Document information

More support for: DB2 for z/OS

Software version: 11.0

Reference #: PI27560

Modified date: 03 March 2015