Fixes are available
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
Codegen precomputes predicates in scan lolepops if we deem them invariant for the duration of the scan. This is causing problems with CASE statements and we may end up performing the THEN before the WHEN. CASE WHEN TRANSLATE (vc_in, '', '1234567890') THEN CAST (vc_in AS INTEGER) ELSE NULL Above is causing a SQL0420N casting error when the value of vc_in was 'T001', we would expect the WHEN to avoid doing the cast and avoiding the error. Here is reproduction testcase: CREATE OR REPLACE FUNCTION METADATA.SAFE_INT (vc_in VARCHAR (500)) RETURNS INTEGER NO EXTERNAL ACTION RETURN CASE WHEN TRANSLATE (vc_in, '', '1234567890') = '' THEN CAST (vc_in AS INTEGER) ELSE NULL END @ CREATE OR REPLACE FUNCTION METADATA.TEST_FUNC1( vc_in VARCHAR(4)) RETURNS VARCHAR(20) BEGIN ATOMIC DECLARE v_list_status_msg VARCHAR(20) DEFAULT 'START'; IF METADATA.SAFE_INT(vc_in) IS NULL THEN SET v_list_status_msg = 'Test Worked'; END IF; RETURN v_list_status_msg; END @ drop TABLE t1@ CREATE TABLE t1 ( c1 VARCHAR(4) NOT NULL )@ INSERT INTO t1 VALUES('T001')@ SELECT METADATA.TEST_FUNC1(c1) FROM t1@
Local fix
db2set DB2_TCG_DEFAULT_OPTIONS="set precompute_expr off" It needs an instance restart to take effect and any static SQL needs to be rebound to benefit from the workaround.
Problem summary
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to v11.1.3.3 or above * ****************************************************************
Problem conclusion
First fixed in v11.1.3.3
Temporary fix
Comments
APAR Information
APAR number
IT22392
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-09-13
Closed date
2017-10-12
Last modified date
2017-10-12
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
RB10 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2022