Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: Any way to truncate some data before inserting?

    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

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    You could create a view on that table, and put an INSTEAD OF INSERT trigger on it which does the necessary SUBSTR().
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or you adjust the INSERT statement like this (just a sketch):
    Code:
    INSERT INTO t
    VALUES ( SUBSTR(value, 0, CASE WHEN length(value) > max-length THEN max-length ELSE length(value) END) )
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    @stolze, probably 1 is correct for start substr:
    Code:
    INSERT INTO t 
    VALUES ( SUBSTR(value, 1, CASE WHEN length(value) > max-length THEN max-length ELSE length(value) END) )
    But the real question is can db2random influence on changing SQL. If not...

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right, start position is 1. (I wrote too much C++ code in the past few weeks. ;-))
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Oct 2008
    Posts
    6
    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.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2random
    The problem is I need to avoid substr.
    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...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •