Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Special Character in DB2 taking 2 bytes to store

 
Haroon Chaudhary
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic