A fix is available
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 / Xsystem
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
06 May 2020