Help with logging errors during inserts on tPostgresqlOutput

One Star

Help with logging errors during inserts on tPostgresqlOutput

I'm new to TOS and am trying to copy table data from an Oracle database to PostgreSQL.
A few of the tables are in the range of 4 million rows, many are smaller.
I've set up simple tOracleInput => tMap => tPostgresqlOuptut jobs for each of the tables.
I ran all of the jobs and they are completed except one of the larger tables errored out with about 100K rows to go out of 4 million.
The error I got was just that an error had been thrown and "call getNextException" to get the cause of the error.
This job was truncating the table and then doing just inserts, with a "commit every" set to the default of 10,000 rows. I had "Die on error" checked.
Now I'd like to resync the target Postgres table with the source Oracle table, and having now turned off "Die on error", I'll use "Insert or Update", and I need to capture any rows that failed to insert.
Do I need to connect a tLogRow to the Rejects output of tPostresqlOutput? What about a tFileOutputDelimited instead of a tLogOutput. Do I need to change "commit every" to 1?
Kind of confused. Basically I want to run the job again, knowing it'll take much longer using "Insert or Update", and let it run to completion and then capture any rows that it failed to insert.
Is there a good mechanism for doing batches and then feeding a failed batch to another component that has a lower commit count? For example, if a 10,000-row batch fails, could I feed that batch to a separate tPostgresqlOutput that has a commit count of 1?
Thanks for any suggestions.
~Mike
Highlighted
One Star

Re: Help with logging errors during inserts on tPostgresqlOutput

Here's kind of what I was thinking; I've no idea if this is correct or will work (see screenshot).
Seventeen Stars

Re: Help with logging errors during inserts on tPostgresqlOutput

You will get the message getNextException if you use the batch mode. Batch mode is only available for only insert oder update - no mix is allowed. It is not helpful to derecrease the commit size.
I would disable the batch mode and try to find the error and if everything runs fine enable the batch mode.
If you don't use the batch mode and switch off die on error you will get a new output flow (rejects) the your database output component. This reject output flow provides the exception message for every failed data set.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch