How to find rows that have a DBCException: SQL Error: CharConversionExcetion in DB2?

Question

I am trying to find rows in a table, that seems to have a DBCException in the cell value. I cannot seem to find a quick way to figure out the unique rows that have this exception.

Error stored in the cell:

DBCException: SQL Error: [jcc][t4][1065][12306][4.18.60] Caught java.io.CharConversionException. See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null

PrimaryKey SomeColumn

1          A

2          B

3          C

4          DBCException: SQL Error...

5          DBCException: SQL Error...

On searching, this is the only link I came across with some help on this matter: https://www.ibm.com/support/pages/sqlexception-message-caught-javaiocharconversionexception-and-errorcode-4220

Here as a diagnosis, it mentions to find Hex(col). However, I cannot seem to narrow down the rows that have an error, so that I can fix it.

I was able to figure out which column has errors. My question here is, how do I narrow down the rows?

Answer 1

I have figured out how to query the rows that have an exception. So the exception is about invalid characters, so we will narrow down the results in the following way:

1. select all rows that have non null values
2. select all rows that have valid characters
3. subtract the two data sets, and you will get the rows that contain invalid characters.

Query: SELECT * FROM ( select id, column from table WHERE column IS NOT NULL minus select id, column from table where TRANSLATE(TRANSLATE(TRANSLATE(column,'','!@#$%^&*()-=+/\{}[];:.,<>?ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'),'','''"')) = '' AND column IS NOT NULL )

Now you can also replace the content in the affected rows, by removing the invalid characters, the following way.

UPDATE table SET column = regexp_replace(column,'[^a-zA-Z-\d]',' ') WHERE id IN ( SELECT id ( select id from table WHERE column IS NOT NULL minus select id from table where TRANSLATE(TRANSLATE(TRANSLATE(column,'','!@#$%^&*()-=+/{}[];:.,<>?ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'),'','''"')) = '' AND column IS NOT NULL ))