In our application we send data like product, prices etc from a central system to a local store system.
The central System is using MySQL (Swedish/Finnish charaterset) database and the local store system is using DB2 (UTF8). when we send data we take data from central database and create a java object and send it to local store using serialization. On the local store the data is deserialized and
inserted into local Db2 database. the trouble is when we have special character like 'Ä' in the product description then it takes 2 bytes, the column in MySQL and DB2 is VARCHAR(30) the MySQL handles the 30 character data (which has special character 'Ä') but when it tries to insert this data in the Db2 then it throws an exception
ERROR::2012-08-08 01:24:23,786::uk.co.datafit.system.error.ServerException: ServerError COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001
This error is thrown becuase a 30 character data with a special character takes 31 bytes to store but Db2 VARCHAR(30) will handle only 30 bytes.
Appraently changing the characterset for the databases is not an option. If anyone has faced similar issue before suggest a solution please.
I've found this page. It looks like you should declare the column with MIXED DATA. I don't do DB2, so I don't know the exact syntax here (and I don't know whether that would cause the limit to be enforced in characters, not bytes - the documentation I've found is a bit obscure about this).
subject: Special Character in DB2 taking 2 bytes to store