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.

Data Truncation Error on IBM ISeries

Postby 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.
satyatiwari
 
Posts: 4
Joined: Thu Oct 28, 2010 7:12 am

Re: Data Truncation Error on IBM ISeries

Postby 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
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 196
Joined: Tue Jul 17, 2012 2:12 pm


Return to Community Forum

Who is online

Users browsing this forum: No registered users and 1 guest