Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Output table step - use batch update for Inserts

  1. #1

    Default Output table step - use batch update for Inserts

    Hello,

    I am trying to load data using output table step.

    I have around 2.5 mil records. And

    Target table: TRUNCATE and load
    Indexes: indexes all are non unique

    1) When i try to load that using the Output table step with
    a) use batch update for Inserts - Enabled
    b) Commit Size 100,000

    I get 808,206 records in ouput

    2) And with the same step by
    a) use batch update for Inserts - Enabled
    b) Commit Size 10,000

    It loads all the 2.5 mil records.

    Just was trying to understand what does the change in COMMIT SIZE has to do in inserting the records.

    Thanks in advance

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    If it's on the same connection... commit size is only an optimizable parameter, it should/does not make a difference datawise.

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I know that for example Oracle has a bug in their database or driver somewhere in certain versions that silently gives problems with batch sizes over 64k. You might very well be hit with that.

    Matt

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    And also if batching is on in Oracle and a row in the batch fails, all of the rows in that batch are rolled back.

    Regards,
    Sven

  5. #5

    Default

    Is there a way to avoid this?

    I want to upload millions of records, but few have issues. And because of this feature, uploaded data in my table is incomplete. After first error, everything is rejected. Resolution to this would be appreciated.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    For Oracle, the problem is in the driver.
    Turning off the batch mode fixes the issue though.

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.