DB2 FOR LUW / DB2FORLUW / 970 - DB2

Execution of the SQL stored procedure fails with SQL0303N error 
if it involves conversion between CHAR types and NCHAR/GRAPHIC 
types for UTF-8 databases. 
 
SQL0303N  A value cannot be assigned to a host variable in the 
SELECT, VALUES, FETCH or assignment statement because the data 
types are  not compatible. 
 
Sample: 
 
Execution of the following stored procedure fails with SQL0303N 
error: 
 
create procedure prc1( IN action integer,INOUT p2 nvarchar(240)) 
BEGIN 
  declare wiac_var nvarchar(240); 
  DECLARE lec_amrapp CURSOR WITH HOLD 
  FOR 
  SELECT 'X' FROM sysibm.dual ; 
   OPEN lec_amrapp; 
   FETCH lec_amrapp INTO wiac_var; 
   CLOSE lec_amrapp; 
   set p2=wiac_var; 
   return 1; 
END 
@ 
 
C:\Program Files\IBM\ESEV975\BIN>db2 call prc1(1,'a') 
SQL0303N  A value cannot be assigned to a host variable in the 
SELECT, VALUES, 
FETCH or assignment statement because the data types are not 
compatible. 
SQLSTATE=42806 
 
 
Here is a summary of conversions that we will support for this 
APAR : 
 
CHAR/VARCHAR to and from GRAPHIC/VARGRAPHIC/NCHAR/NVARCHAR in 
Unicode 
Databases only  (1208->1200).

Users affected: All 
Problem details: Execution of the SQL stored procedure fails 
with SQL0303N error 
if it involves conversion between CHAR types and NCHAR/GRAPHIC 
types for UTF-8 databases.

Use the same  data type to avoid conversion. 
 
For above testcase use 
 
SELECT N'X' FROM sysibm.dual;

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in v9.7fp6

Use the same  data type to avoid conversion.

forerunner  : APAR is sysrouted TO one or more of the following: IC84185 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

30.11.2011
13.06.2012
13.06.2012

9.7.FP6