Hi, this question is *not* about how to truncate a table.

I have an sql statement that is trying to insert a row where the data is too large for one of the column, so I'm getting the error:

"SQL: -404:22001: Value for column or variable <column_name_here> too long. Statement..."

Is there a way to ignore this error, and have db2 truncate the data and have it still insert?

I know in sql server you can do it by issuing the command SET ANSI_WARNINGS OFF;

I'm hoping there is a way without having to validate the data i'm inserting, or using substr to truncate it myself.

Thanks

You could create a view on that table, and put an INSTEAD OF INSERT trigger on it which does the necessary SUBSTR().

Or you adjust the INSERT statement like this (just a sketch):
I think you should have a really good reason for doing that. The truncation warning tells you that your database schema doesn't align with your data. That's typically not so good.

@stolze, probably 1 is correct for start substr:
But the real question is can db2random influence on changing SQL. If not...

Right, start position is 1. (I wrote too much C++ code in the past few weeks. ;-))

Thanks for the suggestions guys.

But the problem is I need to avoid substr. The problem is there is one procedure which handles sql insertion into the database - it basically accepts an sql insert statement as a string param and dynamically runs it (as well as other stuff, like logging, etc). So it's not known what table or what columns will be affected. Obviously this is not idea, and the code generating the insert statement should do validation, but I cannot control that.

So I was hoping I could use a handler in the procedure to catch these 404 erros, log it, and still have the data insert.

But it appears there might not be an easy solution.

Actually, any "automated" solution will use SUBSTR in one way or the other.
Even SqlServer's "SET ANSI_WARNINGS OFF" uses an (automatic) SUBSTR.

The only "fully automatic" way I can see right now is through triggers on each and every table (or view) involved in those inserts. (See my previous post.)

Maybe the "optimal" solution in your case would be a redesign of the database:
Issue an ALTER TABLE on all text columns (where this makes sense), changing their datatype to VARCHAR(2000) (or whatever large value is appropriate).
Since most of the time the values will be short enough, you don't loose storage space with this operation.
Only, now, all SELECTs should be revisited since they could start receiving longer data...