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
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.
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 Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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.
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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
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
- Posts: 4
- Joined: Thu Oct 28, 2010 7:12 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.
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
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 Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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.
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
6 posts
Page 1 of 1