I have a job where data goes from a lookup stage to a transformer on to an Oracle Enterprise stage. All data need to be inserted. I have Upsert with "Insert than Update" as there is no "Insert Only" option. I saw the rejected records and it tries to update all records which means all inserts fail (I captured the rejected records and saw the Oracle code).
This job has been working perfectly before for many months. The only change I made is, in the lookup stage, I changed incoming partitioning to hash based on business key for both incoming links (this is due to a different error in the lookup due to data volume). I also changed the "Preserve partitioning" to Clear. Since then this problem started happening. There are no other messages in the log.
What's interesting is, this problem does not occur on the develoment box, eventhough the datasets involved are much smaller.
Seems to me the first thing to determine is why the inserts are failing. That should be some form of constraint violation - unique index, check constraint, foreign key constraint, etc. And there is a way to do inserts only with that stage, though from what I remember it is a little counter-intuitive - I think you have to select 'update only' then custom sql and use insert DML there... something like that, anyway.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I looked at the table structures and other database objects. There are no error messages of any kind. Job runs perfectly but all rows get rejected. This only happens in the test environment not in the development environment.
I did add a reject link as I said in my earlier post. The SQLCode I get is 1403 which is "no data found" which means that all rows are going to update instead of insert. I put in a condition like 1=2 in the update so that nothing gets updated as I am only looking to do insert only.
I can't use the Load option because I have to supply a value for a sequence column which requires me to do an "User-defined Update & Insert". This works fine in Development and Production environements but not In Test. Has to be some setting difference which I cannot find.
You can't check the sql code with this design because all you'll see is the reason why the second action, the update failed... 1403... and we don't care. You need to perform strictly the insert so you can determine why it is failing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
can you identify the cause for that by spliting the job into 2 jobs, do insert in one stage and then update later or vice versa? that could get you to the core cause.
They really don't have any updates to perform. As noted, set Upsert Mode to User-defined Update Only and use an insert statement in the Update SQL field.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Regardless of platform, any log files would be in the scratch space defined in your config file. However, only the load option (which leverages sqlldr) creates log files, insert/update does not.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I understand the insert fails and update went good, it could be possible cause of violation of constraints. It shows up in the log and here the case is failure of inserts so it should be saved in the log
