Code page issues have become unexpectedly common now that recent versions of DB2 LUW default to UTF-8 / 1208 for XML data type support. In recent days, two separate projects hit errors like the following:
SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.
The root cause was that, with code page 1208, certain extended ASCII values were each converted from one byte to two: “two bytes for the price of one.” This stretching data overflowed columns sized to expect one byte per character.
So if you get SQL0302 or similar errors, you can easily check the code page with: get db cfg for DBNAME | grep -i code (or get db cfg for DBNAME | find /i “code”). The quick fix is to specify a code page like 1252 during database creation: create database DBNAME using codeset ibm-1252 territory us. I do not recommend changing the db2codepage registry variable for this problem.
However, code page 1252 prevents you from using XML data types. So if this is an issue, there are at least two other options:
- If the data you’re storing is really binary data, define the column with for bit data. No codepage conversion will occur, and the data will typically come back to your application as raw bytes, not encoded strings.
- Expand the size of the column to accommodate some “two for one” conversions. Only a few extended ASCII characters get this conversion, but unless you go at least twice as large, this becomes a managed risk of how many of these you’ll get.
