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
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.