Data Truncation Error on IBMi Table insert

[vbanks]

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]

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 -- Support@Goanywhere.com -- and we will be happy to take a look.

[vbanks]

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]

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.