SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003


I am getting the above error when i tried to do an insert. I am unable to track which column is causing the trouble as I am inserting about 25 columns in a table.

How are you doing the INSERT? If it is a JDBC/CLI application, turn on JDBC/CLI trace. Also, have a look at the various SQLCA.SQLERRD fields. Maybe those contain some further indication.

If you you're having a hard time capturing the SQL, you might be able to find the offending values by using triggers. You could create a 'before' trigger to capture the values. If you're working with the z/OS DB2 product, you could call a stored proc and display the values to sysout. I'm not as familiar with UDB, but you might be able to code a stored proc in C and write the values to a log file; I'm not sure about that. You could also validate the values in the 'before' trigger and change them to a valid value when the insert value is too large. If you had an unused char column on the table, you could convert the invalid value to char and insert it into the unused column just so you could identify the rows causing the problem.

Another option would be to create a temp table with numeric columns larger than the target table and code an 'instead of' trigger to insert the values to the temp table rather than the target table.

Writing to a log file (or sysout on z/OS) will work - but only if the value provided to DB2 (through whichever means the application uses) does not exceed the limits of the data type already. That means, if you provide value largen that MAX_INT for an integer column, then DB2 won't call the UDF or procedure in the before trigger at all. The error is raised before the actual statement execution happens.

That is correct. DB2 will raise the overflow condition in the trigger on the first reference to the column with the invalid value. I missed that point on my first response. However, we can still use the trigger/stored proc to locate the row causing the problem. I don't have enough details to know if this is practical. What I'm thinking is that DB2 fires the 'before' trigger before raising the overflow condition. This presents the opportunity to call a stored proc. The stored proc will accept 3 parms. The first parm will pass a key value that will be used to identify rows on the table. You might need more than one parm for the key. The second parm will pass the column name of a numeric column. The third parm will pass the numeric column value. The stored proc will write each field to a log file. The 'before' trigger will call the stored proc passing the key, column name, and column value once for each column suspected of causing the overflow problem. It would be a good idea for the 'before' trigger to call the stored proc initially with nulls for the column name and value parms just to get the key written to the log file. The 'before' trigger will call the stored proc successfully for each column with a valid numeric value and will fail when the first occurrence of a bad value is encountered. At this point, DB2 will raise the overflow condition and roll back the transaction. However, the log file will show the key, column name, and column values up, but not including, the point of failure. This could potentially generate a huge number of rows, but if other attempts to locate the offending row fail, it could serve as a last resort.