hello,
I'm failing in using reject links from transformer and from oracle ent.
I have a 2 sources as oracle ent stages,a change capture stage,a transformer and a destination oracle ent stage. I want to insert/update in the destination (which is also one of the sources) what differes in source1 from source2. The only diference in structure between source and destination is that at destination i have foreign key constraints and some of the records I collect from the change capture and then from the transformerr will fail upsert in the destination. I want to reject those rows. I tried having a reject link both from the destination oracle ent stage and the trasformer stages, but in either case the job failes due to ...fk contraints. It's like de reject link wouldn't be there.
I guess it is not a normal behavior .
Can anyone help ?
Regards,
Maria
reject link enterprise stages
Moderators: chulett, rschirm, roy
Maria,
the PX record rejection mechanism uses a reject link from the DB stage itself, not the from a preceding transform stage. If you draw the output link and activate reject handling in the stage itself, you should be getting reject records in raw format along with SQL error codes passed down that link.
Are you doing anything out of the ordinary (custom SQL) in your output Oracle stage?
the PX record rejection mechanism uses a reject link from the DB stage itself, not the from a preceding transform stage. If you draw the output link and activate reject handling in the stage itself, you should be getting reject records in raw format along with SQL error codes passed down that link.
Are you doing anything out of the ordinary (custom SQL) in your output Oracle stage?
</a>
Maria,
You are encountering a "known issue" - reject links won't work correctly unless the array size is set to 1. If a large block of records is written and one record in the block fails it doesn't handle the reject notification correctly.
Of course, with a large table that can drive performance way down (and upset your DBA's). Craig's suggestion is the best one if it is feasible - implement tests in your job to capture the bad records before they are written out.
You are encountering a "known issue" - reject links won't work correctly unless the array size is set to 1. If a large block of records is written and one record in the block fails it doesn't handle the reject notification correctly.
Of course, with a large table that can drive performance way down (and upset your DBA's). Craig's suggestion is the best one if it is feasible - implement tests in your job to capture the bad records before they are written out.
I'm afraid it is not related to the array size.
I want to use the reject link to reject those record that fail FK constraint. Is it possible to reject these records trough them (reject link from the transformer or from the destination db stage) and the job to succed or i must use look-ups for every table linked trough foreign key with my destination, check if the record can be inserted and reject them from the lookup if not ?
With look-ups it works just fine, but i'm dealing with a great number of jobs and with destination tables that have at least 3 FK constraints. I also will be using these jobs from time to time and i dislike the fact that i will have to update the job if the tables get updated with new FK.
I want to use the reject link to reject those record that fail FK constraint. Is it possible to reject these records trough them (reject link from the transformer or from the destination db stage) and the job to succed or i must use look-ups for every table linked trough foreign key with my destination, check if the record can be inserted and reject them from the lookup if not ?
With look-ups it works just fine, but i'm dealing with a great number of jobs and with destination tables that have at least 3 FK constraints. I also will be using these jobs from time to time and i dislike the fact that i will have to update the job if the tables get updated with new FK.
still,
i thought reject links were supposed to capture data that failed integrity constraints, so why does my job completelly ignore the existence of a reject link from the destination stage when it comes to FK contraints ?
What would capture that link if not exactly this type of things ? I thought that was what a reject link / reject error table was supposed to do.
Regards,
Maria
i thought reject links were supposed to capture data that failed integrity constraints, so why does my job completelly ignore the existence of a reject link from the destination stage when it comes to FK contraints ?
What would capture that link if not exactly this type of things ? I thought that was what a reject link / reject error table was supposed to do.
Regards,
Maria
Hi Maria,
I think there may be some misunderstanding, or it could be that the documentation just doesn't explain this well enough. On the DB2 stage, using this same method of outputting rejects, I'm pretty sure this is only to capture those records where it fails to make an update. That is to say, where it doesn't match on the predicates defined. So, when it comes to a failure due to a FK violation or otherwise, these won't be treated in the same way, in fact, I'd bet these come as fatals rather than warnings (as failed updates would) and abort your job (which is also a reason why message handling is not an option in this scenario).
This leads me on to my next question really - does your job as-is abort or finish OK/with warnings?
This is all on the proviso that the Oracle stages work similarly to the DB2 ones, of course
I think there may be some misunderstanding, or it could be that the documentation just doesn't explain this well enough. On the DB2 stage, using this same method of outputting rejects, I'm pretty sure this is only to capture those records where it fails to make an update. That is to say, where it doesn't match on the predicates defined. So, when it comes to a failure due to a FK violation or otherwise, these won't be treated in the same way, in fact, I'd bet these come as fatals rather than warnings (as failed updates would) and abort your job (which is also a reason why message handling is not an option in this scenario).
This leads me on to my next question really - does your job as-is abort or finish OK/with warnings?
This is all on the proviso that the Oracle stages work similarly to the DB2 ones, of course
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
chulett:
Yes, exactly. Nothing goes down on it. Except when i set InsertAraySize =1, when i get always (on any job) 2 records on the reject link.
miwinter :
What kind of error captures the reject link in DB2 concerning inserts ?
Regards,
Maria
What do you mean by they are "completely ignored"? Are you saying that, even with the reject link, nothing goes down it for a FK violation?
Yes, exactly. Nothing goes down on it. Except when i set InsertAraySize =1, when i get always (on any job) 2 records on the reject link.
miwinter :
It abords in any situation : without any reject links (the original situation) or with reject links on the transformer or on the destination stage.This leads me on to my next question really - does your job as-is abort or finish OK/with warnings?
What kind of error captures the reject link in DB2 concerning inserts ?
Regards,
Maria
OK, so these are treated as fatals, which will abort your job. As such, the job won't progress and/or continue to send records down your reject link as it has actually stopped in failure. I would guess the array size of 1 is throwing things off slightly, in that not declaring this, it's taking a default and that entire block in that array is failing, with nothing somehow making it down the reject link. When it is added, it's changed the behaviour to some extent, clearly, namely forcing each statement to be processed individually (as opposed to in an array of many).
The issue overall though is that this FK violation is being treated as fatal within the DB stage, rather than a warning.
1. Can you confirm if the log is reporting warnings or fatals in relation to these rejects?
2. Would you be able to try a row of data you know will fail to match and make an update against an existing row, to check the behaviour in that scenario?
EDIT: Sorry, I neglected to answer your question. I don't think the rejects handling is involved with inserts, that's the crux of this. That's also the reason why the option only applies to an upsert and not a write I'd surmise.
The issue overall though is that this FK violation is being treated as fatal within the DB stage, rather than a warning.
1. Can you confirm if the log is reporting warnings or fatals in relation to these rejects?
2. Would you be able to try a row of data you know will fail to match and make an update against an existing row, to check the behaviour in that scenario?
EDIT: Sorry, I neglected to answer your question. I don't think the rejects handling is involved with inserts, that's the crux of this. That's also the reason why the option only applies to an upsert and not a write I'd surmise.
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
Andy, isn't that strictly a Server issue? I don't see anything like the OCI 'Array Size' option in the OE stage doing Write Method=Upsert. What am I missing?asorrell wrote:You are encountering a "known issue" - reject links won't work correctly unless the array size is set to 1.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Never mind, answering this myself. I was looking when doing a 'user-defined update only'. For some reason the array size property is only valid on what it thinks are inserts, not updates. I wonder what it uses for updates under the covers, especially when you take the manual option and put an insert in there?chulett wrote:Andy, isn't that strictly a Server issue? I don't see anything like the OCI 'Array Size' option in the OE stage doing Write Method=Upsert. What am I missing?asorrell wrote:You are encountering a "known issue" - reject links won't work correctly unless the array size is set to 1.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
to miwinter :
the log reports fatals. And the job fails.
I made some tests and it appears that the reject link works with ...warnings. Let me explain :
- i've tried to see if a null to a not null column would be rejected. Result : it did. In log appeared warning and the record got down the reject link.
- i've checked to see how it behaves concerning length of column. I've mapped a larger column on a smaller one. Result : It report fatal and the job failed.
Also on my test table on FK it reported fatals and aborted.Could everything work if i made fatals look like warnings ?
Regards,
Maria
the log reports fatals. And the job fails.
I made some tests and it appears that the reject link works with ...warnings. Let me explain :
- i've tried to see if a null to a not null column would be rejected. Result : it did. In log appeared warning and the record got down the reject link.
- i've checked to see how it behaves concerning length of column. I've mapped a larger column on a smaller one. Result : It report fatal and the job failed.
Also on my test table on FK it reported fatals and aborted.Could everything work if i made fatals look like warnings ?
Regards,
Maria