Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
Inconsistent error message returned for empty host variables because the length is calculated incorrectly. In the example below the host variable STMT was initialized with an empty ('') string with datatype VARCHAR which would trigger this issue. USERS AFFECTED: Affects only host variables with datatype VARCHAR. CHAR is not affected. Applies to EXECUTE IMMEDIATE and PREPARE. repro.db2 ----------- connect to sample@ drop table t1 @ create table t1 (c1 char(10), c2 char(10) @ insert into t1 values('aa','bb') @ drop procedure tran@ create procedure tran(IN colname varchar(10), IN first_cmpl_flag char(1)) specific tran language sql p1: begin declare stmt varchar(4000) default ''; declare outputcol varchar(10) default ''; declare attr_rule1 cursor for s2; if first_cmpl_flag = 'y' then set stmt = 'select c1 from t1 where c1 = ?'; end if; prepare s2 from stmt; open attr_rule1 using colname; fetch attr_rule1 into outputcol; close attr_rule1; return 0; end p1 @ -- SQL0198 is returned as expected call tran('a','n') @ -- Works as expected call tran('aa','y') @ -- Why does this work? call tran('a','n') @ terminate @ Output -------- call tran('a','n') SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty. SQLSTATE=42617 call tran('aa','y') Return Status = 0 call tran('a','n') Return Status = 0
Local fix
Initialize the host variable with a blank space instead of ''; Example: DECLARE STMT VARCHAR(4000) DEFAULT ' ';
Problem summary
SEE ERROR DESCRIPTION.
Problem conclusion
Problem was first fixed in Version 9 Fixpak 5 (s080512). At a minimum this fix should be applied on the server side.
Temporary fix
SEE LOCAL FIX.
Comments
APAR Information
APAR number
IZ06331
Reported component name
DB2 UDB WSE AIX
Reported component ID
5765F3500
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-10-10
Closed date
2008-07-12
Last modified date
2008-07-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 UDB WSE AIX
Fixed component ID
5765F3500
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
Document Information
Modified date:
03 October 2021