Oracle error messages relating to the rejected records

dstgx08 · Post by **dstgx08** » Tue Apr 20, 2010 7:36 am

Hi,
I have to retrieve Oracle error messages relating to the rejected records in parallel job.

I know only this way:
in OCI Stage I can set Upsert as "Write mode" and selecting "Output rejected records" I can retrieve sqlcode associate to rejected rows.

If I use Load as write mode, How can I do the same thing?

Are there any other ways to retrieve Oracle error in parallel job?

thanks

George

ArndW · Post by **ArndW** » Tue Apr 20, 2010 8:05 am

The Load method is executed outside of DataStage and that is why the single rejected rows and messages aren't visible the way they are with 'upsert'. I think that the error log files should be visible after the job run is finished and has errors, and that file can be read and interpreted.

dstgx08 · Post by **dstgx08** » Tue Apr 20, 2010 9:12 am

ArndW wrote:The Load method is executed outside of DataStage and that is why the single rejected rows and messages aren't visible the way they are with 'upsert'. I think that the error log files should be visible after the job run is finished and has errors, and that file can be read and interpreted.

thanks a lot for the response.
I focused on "LOAD" mode to be able to performe a truncate before insert besides retrieve Oracle error.

In "UPSERT" mode it's not possible performe a truncate before insert, so I have to find another way to truncate table before insert.

right?

thanks again

dstgx08 · Post by **dstgx08** » Tue Apr 20, 2010 9:16 am

ArndW wrote:The Load method is executed outside of DataStage and that is why the single rejected rows and messages aren't visible the way they are with 'upsert'. I think that the error log files should be visible after the job run is finished and has errors, and that file can be read and interpreted.

thanks a lot for the response.
I focused on "LOAD" mode to be able to performe a truncate before insert besides retrieve Oracle error.

In "UPSERT" mode it's not possible performe a truncate before insert, so I have to find another way to truncate table before insert.

right?

thanks again

ArndW · Post by **ArndW** » Tue Apr 20, 2010 10:03 am

Why not do your truncate as part of the before-sql?

dstgx08 · Post by **dstgx08** » Thu Apr 22, 2010 1:58 am

ArndW wrote:Why not do your truncate as part of the before-sql? ...

Because there isn't before-sql part in stage OCI of parallel job

dstgx08 · Post by **dstgx08** » Thu Apr 22, 2010 2:00 am

ArndW wrote:Why not do your truncate as part of the before-sql? ...

Because there isn't before-sql part in stage OCI of parallel job

ArndW · Post by **ArndW** » Thu Apr 22, 2010 10:06 am

That's a pretty compelling argument, in my opinion!

sohasaid · Post by **sohasaid** » Sun Apr 25, 2010 4:04 pm

dstgx08 wrote: Because there isn't before-sql part in stage OCI of parallel job

I didn't work with OCI stage, but if there's a 'user-defined upsert' it can fix the problem by putting the 'truncate' statement instead if the insert statement and the 'insert' statement instead of the update one at the stage.
But you won't get the sqlloader advantages here with this suggestion.

Hope this could help,
Regards.