A fix is available
APAR status
Closed as program error.
Error description
A stored procedure input parameter is used as parameter to call a nested stored procedure in a signal handler. The input parameter in the nested stored procedure is unexpected null, which causes the logic to fail. A simplified test case follows here, the logic raises a signal 990C1 if the unexpected NULL value is detected. The test logic is as follows: 1) call TEST_UPDATE(1) to initiate the test 2) TEST_UPDATE establishes a signal handler 3) TEST_UPDATE calls TEST_CHECK_ROW. 4) TEST_CHECK_ROW.raises a signal to simulate some error situation 5) The signal handler in TEST_UPDATE calls TEST_CHECK_UPD 6) TEST_CHECK_UPD checks the value of it's input parameter which is unexpected NULL and raises a signal to indicate the NULL value --#SET TERMINATOR # CREATE PROCEDURE TWFNI1C.TEST_CHECK_ROW ( IN srcRowId DECIMAL(19 , 0) ) VERSION TEST_VERSION_DEBUG LANGUAGE SQL DETERMINISTIC READS SQL DATA DISABLE DEBUG MODE DYNAMICRULES RUN APPLICATION ENCODING SCHEME EBCDIC VALIDATE BIND BEGIN SIGNAL SQLSTATE '990C0' SET MESSAGE_TEXT='RAISED EXCEPTION'; END # CREATE PROCEDURE TWFNI1C.TEST_CHECK_UPD ( IN srcRowId DECIMAL(19 , 0) ) VERSION TEST_VERSION_DEBUG LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA DISABLE DEBUG MODE DYNAMICRULES RUN APPLICATION ENCODING SCHEME EBCDIC VALIDATE BIND BEGIN -- wants to do something with the input parameter IF srcRowId IS NULL THEN SIGNAL SQLSTATE '990C1' SET MESSAGE_TEXT='UNEXPECTED ERROR VARIABLE IS NULL'; END IF; END # CREATE PROCEDURE TWFNI1C.TEST_UPDATE ( IN srcRowId DECIMAL(19 , 0) ) VERSION TEST_VERSION_DEBUG LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA DISABLE DEBUG MODE DYNAMICRULES RUN APPLICATION ENCODING SCHEME EBCDIC VALIDATE BIND BEGIN DECLARE EXIT HANDLER FOR SQLSTATE VALUE '990C0' BEGIN -- the variable srcRowId is here null - WHY ? CALL TWFNI1C.TEST_CHECK_UPD(srcRowId); END; -- RAISES A SQLSTATE CALL TWFNI1C.TEST_CHECK_ROW(srcRowId); END# -- SO THEN WE CALL THE TEST_UPDATE METHOD WITH a DECIMAL which is not NULL and get the unexpected error that srcRowId is NULL call TWFNI1C.TEST_UPDATE(1)# --SQL error: SQLCODE = -438, SQLSTATE = 990C1, SQLERRMC = UNEXPECTED ERROR VARIABLE IS NULL. SQLCODE=-438, SQLSTATE=990C1, DRIVER=4.18.60 Keywords: SQLSP SQLSTOREDPROC
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All Db2 11 and Db2 12 for z/OS users of * * native SQL procedures. * **************************************************************** * PROBLEM DESCRIPTION: An SQL variable can be assigned a null * * value when the variable is passed as * * an input parameter to a stored * * procedure. * **************************************************************** * RECOMMENDATION: Apply corrective PTF when available. * **************************************************************** An SQL variable can be incorrectly assigned a null value when the variable is passed as an input parameter to a stored procedure. The problem occurs when both of the following conditions are met: - The data type of SQL Variable is decimal. - The CALL statement returns an error. The following example helps illustrate the problem. 1. Create a native stored procedure PROC1 with one input parameter of type Decimal. The procedure returns with an error. CREATE PROCEDURE PROC1(IN pIN DECIMAL(19,0)) SIGNAL SQLSTATE '75002'# 2. Create a second native stored procedure PROC2 that invokes the first procedure. CREATE PROCEDURE PROC2() BEGIN DECLARE var1 DECIMAL(19,0) DEFAULT 0; CALL PROC1(var1); END# 3. Invoke procedure PROC2. CALL PROC2()# Inside procedure PROC2, variable var1 is incorrectly assigned a null value after the invocation of procedure PROC1. The value of variable var1 should remained unchanged.
Problem conclusion
Db2 has been modified to process the CALL statement such that variables that are passed as input parameters remain unchanged on the occurrence of error. Additional Keywords: SQLNATIVESQLPL SQLSP SQLSTOREDPROC SQLUDF SQLTRIGGER ZSA2
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PH09665
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
2019-03-14
Closed date
2019-04-19
Last modified date
2019-06-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI62629 UI62634
Modules/Macros
DSNXRSPL
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 June 2019