IZ05102: INCONSISTENT ERROR MESSAGE RETURNED FOR BLANK HOST VARIABLES.

DB2 UDB Version 8.1 FixPak 16 (also known as Version 8.2 FixPak 9 )

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 PROBLEM DESCRIPTION.
  

Problem conclusion

• Problem was first fixed in Version 8 Fixpak 16 (s080111).  At
  a minimum this fix should be applied on the server side.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IZ06331

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