Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: AS400 Bulk Load

  1. #1

    Default AS400 Bulk Load

    I am loading data into a AS400 DB2i system and is using bulk load option. But it is giving me the error. Where as the data is getting loaded if I disable the batch update option

    2015/04/23 18:22:18 - BADTEL_TGT.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Because of an error, this step can't continue:
    2015/04/23 18:22:18 - BADTEL_TGT.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Error batch inserting rows into table [BADTEL_T].
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Errors encountered (first 10):
    2015/04/23 18:22:18 - BADTEL_TGT.0 -
    2015/04/23 18:22:18 - BADTEL_TGT.0 -
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Error updating batch
    2015/04/23 18:22:18 - BADTEL_TGT.0 - [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
    2015/04/23 18:22:18 - BADTEL_TGT.0 -
    2015/04/23 18:22:18 - BADTEL_TGT.0 -
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:342)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:118)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at java.lang.Thread.run(Unknown Source)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Error updating batch
    2015/04/23 18:22:18 - BADTEL_TGT.0 - [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
    2015/04/23 18:22:18 - BADTEL_TGT.0 -
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1351)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:289)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - ... 3 more
    2015/04/23 18:22:18 - BADTEL_TGT.0 - Caused by: sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:285)
    2015/04/23 18:22:18 - BADTEL_TGT.0 - ... 3 more

  2. #2
    Join Date
    Apr 2008
    Posts
    146

    Default

    The AS400 is a very strange animal. A few things that I would look at. Why are you using an ODBC driver instead of the jt400.jar driver available to talk to this database?

    Some things we do:

    Read this for understanding how that incredibly strange database driver works and prevent problems:
    https://doctorjw.wordpress.com/2010/...the-jt400-jar/

    Use the latest jt400.jar available here:
    http://jt400.sourceforge.net/

    On your JDBC connection URL, enable the extra information on errors. It will tell you more about the SQL that DB2 doesn't like and suggest a way to fix it. After you iterate through those issues, batch inserts are much more likely to work.

    I've had to convert fields to space padded string fields, even if they contained numbers right before they are inserted into the database. I've also had to craft strings with spaces before the digits for things in WHERE clauses.. For example if a company number is 130, it may not work to use WHERE GLCO=130, but instead WHERE GLCO=' 130';

    Hope that helps you along a bit. It sure looks like Field 7, in your output is upsetting the database server some how. I would look at that carefully, even filtering down to the row that throws the issue.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.