DB2 FOR LUW / DB2FORLUW / A10 - 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 DESCRIPTION:                                         * 
* See Apar Description                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v97fp6                                            * 
**************************************************************** 
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 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in v9.7fp6

Use the same  data type to avoid conversion.

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

13.06.2012
20.11.2012
20.11.2012

9.7.FP6