DSXchange

Hi
I am using Oracle Stage in 7.5.1.A almost 130765 records are getting rejected from TARGET ORACLE Stage can any one help me on this.
I am doing user defined upset (insert and load) method.

bye

How can we help with so little information? Why are they getting rejected? Why are you doing user-defined? That way only leads to the Dark Side...

sendmk wrote:Hi
I am using Oracle Stage in 7.5.1.A almost 130765 records are getting rejected from TARGET ORACLE Stage can any one help me on this.
I am doing user defined upset (insert and load) method.

bye

Tried capturing the rejects through a reject link from the target stage? If so whats the error code tell?

Hi

My question was what might be the reason for rejection in general

here i am updating the old record and inserting the new using Update and Insert Method

Thanks

Hi

The records are already there in the DB instead them being updated they are getting rejected here.

thanx..

Details, man, details. Not knowing what you are doing makes it really hard to offer constructive help. All we know is they are not getting updated, so it sounds like you are inserting (instead? first?) and that is failing due to a unique constraint being violated more than likely.

Post details of your job design. Post your user-defined sql. Post the actual errors you are seeing. Post anything you think would help people help you.

The general reason can be many. Majorly Unique constraints violation, metadata mismatch or condition based rejects.

Hi Kumar,

Really thanx for the information can you please let me know is there any Env Var to increase the Commit Interval in oracle Stage and how to do that while using Oracle stage in DS 7.5.1A PX and what extractly Insert Array Size does.

Regards
Srireddy.

Hi

I was just asking the general reason why the rejections happen at ORACLE STAGE END. Can u pls tell me how to increase the commit interval in oracle stage.

Thanx
sendmk.

Ok. They don't generally happen at the Oracle stage, per se - they happen inside your database and can be captured in the Oracle stage if you like. Any operation that results in an Oracle error - and Kumar noted there are many - like constraint violations (unique, value or FK), metadata mismatches (invalid number, precision), database failures (tablespace, media, locks) etc,e tc. There's a large loverly manual just chock full of 'em you could get from your DBA if you like.

In the Server product, the DataStage Engine can do some metadata based 'pre-qualification' of information just before it ships it off, all in an effort to avoid Oracle errors coming from the database. For example, if your metadata says a Varchar field is 80 characters and it finds 82 characters in it, it will truncate the string and log a warning but still send the record to Oracle. This regardless of the actual size of the Oracle field and if 82 would have fit - the metadata is your Bible. I'm not sure if the PX version of the stage attempts to do the same but I would think it happens in some fashion.

For the 'commit interval' question, this has been asked and answered a number of times. There are APT variables you can set that a Search of the forum will turn up - one for commit INTERVAL and one for commit FREQUENCY, from what I recall.

The 'Insert Array Size' would be exactly that - the size of the array used for the inserts. In other words, the number of insert operations (or collection of records) sent at the same time to Oracle.

If nothing else, post the text of any warning that appears in the job log. At least we get a shot at narrowing down the possible cause. Oracle has over 8000 different error codes, if I recall correctly.

sendmk wrote:Hi Kumar,

Really thanx for the information can you please let me know is there any Env Var to increase the Commit Interval in oracle Stage and how to do that while using Oracle stage in DS 7.5.1A PX and what extractly Insert Array Size does.

Regards
Srireddy.

APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL


are the environment variables used to increase the commit interval in Oracle stage. Setting these to lower values may affect the performance, you have to strike a balance there.

Hope this would help

Ajith