Rejecting all rows which cannot be loaded in ORACLE

[ray.wurlod]

Mostly we rely on the SQL*Loader bad file.

[chrisk]

The Oracle bulk loader is great for inserts!

What do you do if you need to do bulk updates?

Ta,
C.

[chulett]

Regardless of the array size when writing to Oracle, I have noticed that DataStage will still log a warning in the log if a DBMS reject occurs (irrespective of the reject checkbox in the constraint being ticked).

True, DBMS rejects are always logged... and the checkbox has nothing to do with that. All the checkbox does is A) automate the contraint, meaning you can just check the box and put no derivation there and B) make DS log an extra Warning row with the number of rejected rows in the job.

Ray, this suggests to me that DataStage might know which row(s) generated the warning(s)? I concede that it might be difficult (or impossible!) for DataStage to marry these DBMS messages back to the offending row. I have asked Ascential the same question but have not received a definitive answer yet.

It only knows the row for certain if the array size is 1. Bump it up to something larger and nothing is reliable. When you have a problem, it will get the number of rejects wrong, it will log the wrong record as being the offender, all kinds of goofiness. That's just The Way It Is.

Craig, since you are using an array size of 1, does that mean that you are relying on the DBMS reject method to identify your rejects? I'm curious as we have decided to check for data problems ourselves rather than rely on DBMS rejects as we find that an array size of 1 does degrade our performance when writing to Oracle.

Actually, I'm relying on nothing being rejected. Meaning, we do everything we can to ensure that nothing about the record will cause a DBMS error. We logically reject records that we 'know' are going to cause a problem without logging warnings to the job. If something slips by and actually has a problem, because we keep the Abort after X Warnings low the job craters and Oracle roll back what's been done so far.

Needless to say, it is very unusual for us to have any physical rejects on any given night.

[johm73]

I think this is an old topic, but I'm having issues here I need help with.

I read the quote below and my DataStage rejects do not act this way. I'm on v7.5. I have an input data file into a transformer. I also have an Oracle target table and a sequential file for the reject path.

I set the reject "check-box" and leave the constraint blank. The output link to my Oracle target is also left blank.

The Oracle target table contains test records. The input set I'm trying to insert are the same as to generate Oracle unique constraint violations on the target table.

I run the job and and nothing gets loaded into the reject path file. It simply tries to insert the records into the Oracle table and put the unique constraint warnings into the DS log file.

PLEASE HELP!

Thanks!


[quote="chulett"]All you really need to do is have a second link defined (with no constraint) and mark it as a Reject Row. Make sure it is the last link when looking at the link ordering and it will capture all database rejects.

What is your target stage and array size?[/quote]

[ray.wurlod]

What stage type are you using?

[chulett]

...and what order are your links in?

[arun_im4u]

U can capture the reject rows without warning by writing the rejected row through an interprocess stage into a sequential file. When u specify reject row and try to capture the rejected rows into a seq file then it generates a warning. Capture the rejected rows into a IP stage and then into a seq file.

Thanks,
Arun.