File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Special Character in DB2 taking 2 bytes to store Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Special Character in DB2 taking 2 bytes to store" Watch "Special Character in DB2 taking 2 bytes to store" New topic

Special Character in DB2 taking 2 bytes to store

Haroon Chaudhary

Joined: Oct 28, 2010
Posts: 6
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, ServerError [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.

Haroon Chaudhary
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3732

Did you try to google DB2 varchar multibyte characters?

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).
I agree. Here's the link:
subject: Special Character in DB2 taking 2 bytes to store
jQuery in Action, 3rd edition