SqlException with message "Caught java.io.CharConversionException." and errorcode=-4220

Technote (troubleshooting) problem (Abstract)

When an application uses the IBM Data Server Driver for JDBC and SQLJ (also known as the JCC Driver) and are connected to a Database with code set UTF-8 (code page 1208), it throws a SqlException with message including "Caught Java.io.CharConve Rsionexception "and errorcode=-4220 if the data in a character column that it queries contains a sequence of bytes Not a valid UTF-8 string.

Symptom

An exception are thrown similar to this:

com.ibm.db2.jcc.am.SqlException: [jcc][t4][1065][12306][XXX.XXX.XXX] Caught java.io.CharConversionException. See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null [...] Caused by: java.nio.charset.MalformedInputException: Input length = XXX [...] Caused by: sun.io.MalformedInputException at sun.io.ByteToCharUTF8.convert(ByteToCharUTF8.java:XXX) [...]

Cause

The JCC driver throws the exception when data in a character column that it queries was not a valid string in the database code page.
The invalid data could has been input to the database in the following ways:

• By running a SQL statement which writes byte values to the database. For example:INSERT INTO tab1 VALUES (X‘C3‘)

• By running the IMPORT, or LOAD commands for files which contain character data, is not in the code page of the client m Achine, so the appropriate code page conversion is not done. To make sure, the the the appropriate code page conversion was done when running IMPORT or LOAD, specify the code page of T He input file by including the "codepage=x" file type modifier (specifying the code page in place of X).

Diagnosing the problem

You can use the HEX function to find out what is the byte values in a character column.

For example, to find out the byte values in column COL1 in table TAB1, run: SELECT HEX(col1) FROM tab1

Resolving the problem

Update the invalid data with a valid UTF-8 character string.

Alternatively, for JCC driver versions This support it can set the JCC configuration property db2.jcc.charsetDecoderEn Coder=3 so, instead of throwing an exception the JCC driver returns the Unicode replacement CHARACTER (U+FFFD) in Plac E of a sequence of bytes a valid UTF-8 string. The JCC configuration property Db2.jcc.charsetDecoderEncoder are supported in versions of the JCC driver that come with DB2 LUW 9.5 Fix Pack 8 and later (APAR IC74896), DB2 LUW 9.7 Fix Pack 5 and later (APAR IC74895), and all DB2 LUW releases FR Om DB2 10.1 onwards.
For example, suppose you has a Java program Myapp.java this executes an SQL query of a database column that contains a SE Quence of bytes is not a valid string. When you run: java MyAppIt throws an exception. If you run: java -Ddb2.jcc.charsetDecoderEncoder=3 MyAppIt returns the string with any invalid sequence of bytes replaced by the Unicode replacement CHARACTER.
The Unicode replacement CHARACTER often appears like this (a diamond with a question mark inside it):

Related information

APAR IC78495 APAR IC74896 Making the setting for IBM Data Studio Making the setting for IBM Content Collector UDFs to ident Ify invalid data in a UTF-8 character colum what invalid data can get into a UTF-8 character column LOAD command IMPORT com Mand HEX function

Community Questions and discussion

By adding a comment, we accept our Terms of use. Your comments entered on this IBM support site does not represent the views or opinions of IBM. IBM, in it sole discretion, reserves the right-to-remove any comments from this site. IBM is isn't responsible for, and does isn't validate or confirm, the correctness or accuracy of any comments you post. IBM does not endorse any of your comments. All IBM comments was provided "as is" and was not a warranted by the IBM in any.

SqlException with message "Caught java.io.CharConversionException." and errorcode=-4220