Data Truncation Error on IBMi Table insert

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

vbanks

Posts: 2
Joined: Fri Feb 01, 2013 6:00 pm

Post by vbanks » Fri Feb 01, 2013 6:06 pm
Hello,

I am getting a truncation error on a project that has been running for some time now. Can you take a look at the job log that I will paste in and let me know your thoughts?

Thanks in advance.

Code: Select all
2/1/13 4:43:35 PM             INFO      Start Date and Time: 2/1/13 4:43:34 PM
2/1/13 4:43:35 PM             INFO      Job Number: 1359228475413 
2/1/13 4:43:35 PM             INFO      Project Name: /Uat/Material Transfer/Refresh MTITRKCAT-MTITRKITM
2/1/13 4:43:35 PM             INFO      Submitted By: vbanks
2/1/13 4:43:35 PM             INFO      GoAnywhere Director 4.2.0 running on OS/400 V7R1M0 (PowerPC)
2/1/13 4:43:35 PM             INFO      Executing project 'Refresh MTITRKCAT-MTITRKITM' 
2/1/13 4:43:35 PM             INFO      Project location: /linoma/goanywhdev/userdata/projects/Uat/Material Transfer/Refresh MTITRKCAT-MTITRKITM.xml
2/1/13 4:43:35 PM             INFO      Executing module 'Main'
2/1/13 4:43:35 PM             INFO      Executing task 'createWorkspace 1.0 (Create Workspace)'
2/1/13 4:43:36 PM             INFO      Workspace directory for this job is set to '/linoma/goanywhdev/userdata/workspace/1359228475413'.
2/1/13 4:43:36 PM             INFO      Finished task 'createWorkspace 1.0 (Create Workspace)'
2/1/13 4:43:36 PM             INFO      Executing task 'sql 1.0 (Connect to PSTFB)'
2/1/13 4:43:36 PM             INFO      Executing sub-task 'query'
2/1/13 4:43:36 PM             INFO      Query completed successfully and the rowset 'dataset' was created
2/1/13 4:43:36 PM             INFO      Finished sub-task 'query'
2/1/13 4:43:36 PM             INFO      Executing sub-task 'query'
2/1/13 4:43:36 PM             INFO      Query completed successfully and the rowset 'dataset2' was created
2/1/13 4:43:36 PM             INFO      Finished sub-task 'query'
2/1/13 4:43:36 PM             INFO      Finished task 'sql 1.0 (Connect to PSTFB)'
2/1/13 4:43:36 PM             INFO      Executing task 'sql 1.0 (Connect to iSeries)'
2/1/13 4:43:36 PM             INFO      Executing sub-task 'query'
2/1/13 4:43:37 PM             INFO      Query completed successfully and 9,291 row(s) were affected
2/1/13 4:43:37 PM             INFO      Finished sub-task 'query'
2/1/13 4:43:37 PM             INFO      Executing sub-task 'query'
2/1/13 4:43:41 PM             ERROR     [8044 - Connect to iSeries] Last known record near row 9292
2/1/13 4:43:41 PM             ERROR     [8098 - Connect to iSeries] Data truncation 
2/1/13 4:43:41 PM             INFO      Finished project 'Refresh MTITRKCAT-MTITRKITM'
2/1/13 4:43:41 PM             ERROR     [8098 - Connect to iSeries] Data truncation 
                                        com.linoma.dpa.runtime.JobFailedException: [8098 - Connect to iSeries] Data truncation 
                                        	at com.linoma.dpa.runtime.Job.run(Unknown Source)
                                        	at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
                                        	at com.linoma.dpa.j2ee.forms.ProjectListForm.runProject(Unknown Source)
                                        	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                                        	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
                                        	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                                        	at java.lang.reflect.Method.invoke(Method.java:618)
                                        	at org.apache.el.parser.AstValue.invoke(AstValue.java:191)
                                        	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
                                        	at org.apache.jasper.el.JspMethodExpression.invoke(JspMethodExpression.java:68)
                                        	at javax.faces.component._MethodExpressionToMethodBinding.invoke(_MethodExpressionToMethodBinding.java:78)
                                        	at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:57)
                                        	at javax.faces.component.UICommand.broadcast(UICommand.java:143)
                                        	at javax.faces.component.UIData.broadcast(UIData.java:696)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:329)
                                        	at org.ajax4jsf.component.AjaxViewRoot.broadcastEventsForPhase(AjaxViewRoot.java:304)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:261)
                                        	at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:474)
                                        	at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)
                                        	at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:103)
                                        	at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:76)
                                        	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:151)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.apache.myfaces.webapp.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:341)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
                                        	at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
                                        	at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
                                        	at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at com.linoma.dpa.j2ee.IECompatibilityModeFilter.doFilter(Unknown Source)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at com.linoma.dpa.security.SecurityFilter.doFilter(Unknown Source)
                                        	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                                        	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                                        	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
                                        	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                                        	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
                                        	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
                                        	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
                                        	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
                                        	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
                                        	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
                                        	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
                                        	at java.lang.Thread.run(Thread.java:811)
                                        Caused by: com.linoma.dpa.ExecutionException: [8098 - Connect to iSeries] Data truncation 
                                        	at com.linoma.dpa.ProjectUtilities.getExecutionException(Unknown Source)
                                        	at com.linoma.dpa.tasks.sql.Query.execute(Unknown Source)
                                        	at com.linoma.dpa.tasks.sql.SQLTask.execute(Unknown Source)
                                        	at com.linoma.dpa.Module.execute(Unknown Source)
                                        	at com.linoma.dpa.Project.execute(Unknown Source)
                                        	at com.linoma.dpa.runtime.Job.executeProject(Unknown Source)
                                        	... 49 more
                                        Caused by: java.sql.DataTruncation: Data truncation
                                        	at com.ibm.as400.access.AS400JDBCPreparedStatement.testDataTruncation(AS400JDBCPreparedStatement.java:2904)
                                        	at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:2870)
                                        	at com.ibm.as400.access.AS400JDBCPreparedStatement.setObject(AS400JDBCPreparedStatement.java:2343)
                                        	at com.linoma.dpa.tasks.sql.Query.executeUsingColumnIndices(Unknown Source)
                                        	at com.linoma.dpa.tasks.sql.Query.executeUsingInput(Unknown Source)
                                        	... 54 more
2/1/13 4:43:41 PM             INFO      End Date and Time: 2/1/13 4:43:41 PM

Support_Rick

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

Post by Support_Rick » Mon Feb 04, 2013 9:25 am
My initial thought is that you are running a connection to an iSeries Table and executing 2 queries retrieving (Selecting) data into Dataset and Dataset2. These are running fine ...

Next, it looks like you're performing an Insert or Delete of records and the 2nd Insert is failing with the Truncation Error. But, prior to that, you have a "Last known record near row 9292". This usually indicates invalid data (something like having an " inside of text data) or missing and EOR Marker (CRLF, LF, CR, etc).

Please take a look at your data and validate that there are no invalid characters that might be causing the error.

Otherwise, we really need to see your Project XML and your Project Log executed under DEBUG to get a better understanding of what it is you're trying to do. If you would like for us to evaluate that, please send them to our Support Email -- [email protected] -- and we will be happy to take a look.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

vbanks

Posts: 2
Joined: Fri Feb 01, 2013 6:00 pm

Post by vbanks » Mon Feb 04, 2013 3:10 pm
Rick,

I did as you asked and looked at the source data. Apparently, a column size increased in the source table, and the table I was inserting to had a shorter length. I increased the column in the destination table to match the source, and all is well now. Thanks so much for pointing me in the right direction!

Valeria

satyatiwari

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

Post by satyatiwari » Fri Apr 12, 2013 7:38 am
Is there a way to avoid data truncation errors. I meant if the field length is less then the data inserted, is there a way to avoid data truncation error, it should just move how much it can to the field.

Rocky

Posts: 13
Joined: Wed Jan 25, 2012 10:38 am

Post by Rocky » Fri Sep 27, 2013 9:29 am
I agree with satyatiwari. GoAnywhere needs to be able to deal with the truncation error. Maybe thru the on error tab have a way to allow the data to truncate and keep the project running.

We suffer from a trading partner who is sending XML that we need to insert into an AS400 file. It's XML to them. So they don't care about field sizes and sometimes send us data that leads to truncation in our field sizes. Would it help if we define the fields as VARCHAR?

I understand we just don't want every project to truncate data and we never find out. But there should be a way to tell the project to truncate the data and keep running AND write out a warning message to the joblog that data was truncated from whatever field name was being inserted.

- Roc

Support_Rick

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

Post by Support_Rick » Fri Sep 27, 2013 12:14 pm
Roc,

Doing a search on TRUNCATION here in the forum will give you a link to this answer... I posted this for Satyatiwari back in April. Check out the following link:

http://www.goanywheremft.com/forum/dire ... -t396.html

This will help you ignore the Truncation Errors with a specific insert if needed, but .. not for ALL inserts if desired.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
6 posts Page 1 of 1