Why am I getting a "[SQL0802] Data conversion of data mapping error" exception?

Question

I am not very familiar with iseries/DB2. However, I work on a website that uses it as its primary database.

A new column was recently added to an existing table. When I view it via AS400, I see the following data type:

Type: S
Length: 9
Dec: 2

This tells me it's a numeric field with 6 digits before the decimal point, and 2 digits after the decimal point.

When I query the data with a simple SELECT (SELECT MYCOL FROM MYTABLE), I get back all the records without a problem. However, when I try using a DISTINCT, GROUP BY, or ORDER BY on that same column I get the following exception:

[SQL0802] Data conversion of data mapping error

I've deduced that at least one record has invalid data - what my DBA calls "blanks" or "4 O". How is this possible though? Shouldn't the database throw an exception when invalid data is attempted to be added to that column?

Is there any way I can get around this, such as filtering out those bad records in my query?

Answer 1

"4 O" means 0x40 which is the EBCDIC code for a space or blank character and is the default value placed into any new space in a record.

Legacy programs / operations can introduce the decimal data error. For example if the new file was created and filled using the CPYF command with the FMTOPT(*NOCHK) option.

The easiest way to fix it is to write an HLL program (RPG) to read the file and correct the records.

Answer 2

The only solution I could find was to write a script that checks for blank values in the column and then updates them to zero when they are found.