While inserting data from a Row set variable (XML Data) , if the data base field length is smaller the the data inserted, it fails with data truncation error.
Is there a way to avoid it ?
If goanywhere can substring the data, to move, only part of data, depending on the length of field.
By default, GoAnywhere's JDBC connections to the DB2 Engine will automatically throw an error if truncation occurs during an insert. There are a few things you can do to eliminate Truncation of data being inserted into smaller field sizes.
1. You can clean your data prior to insert, making sure that all values are within limits
2. You can loop through your Rowset performing an SQL Insert for each row, allowing you to groom your data prior to performing the insert (using setVariable commands and GoAnywhere Functions -OR- using DB2 SQL Functions within the insert values clause)
3. You can copy the JDBC URL from your resource that is defined to connect to your DB2 system, and paste it into the JDBC URL entry under the DATABASE SERVER tab under your SQL Task within your project. Then, you could add "data truncation=false;" to the end of that URL Definition, making sure you have a semi-colon ";" following the last entry of the URL prior to appending. At that time, your JDBC URL would look something like this:
jdbc:as400://192.168.1.53;date format=iso;errors=full;naming=sql;prompt=false;time format=iso;transaction isolation=none;translate binary=true;data truncation=false;
PLEASE NOTE -- What this means is .. ANY insert of ANY value would be placed into the target field and no error would be reported if truncation occurs.
Source Field Value: "This is my Big Brown Dog" Target Field Width: 20 Target Field Value after Insert: "This is my Big Brown"
Lead Solutions Consultant