Data Truncation Error on IBM ISeries

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts Page 1 of 1

satyatiwari

Posts: 4
Joined: Thu Oct 28, 2010 7:12 am

Post by satyatiwari » Fri Apr 12, 2013 7:45 am
Hi

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.

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Fri Apr 12, 2013 8:30 am
Satyatiwari,

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:
Code: Select all
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;
What this change does is overrides the Resource Connection to the DB2 Database and tells the JDBC Connection to not throw an error when a truncation event occurs.

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.

Example:
Code: Select all
Source Field Value: "This is my Big Brown Dog"
Target Field Width: 20
Target Field Value after Insert:  "This is my Big Brown"
If you have any questions, please feel free to contact Linoma Support at Support@Goanywhere.com
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts Page 1 of 1