Issue with updating partitioned table

[abhinavsuri]

My target is range partitioned table and partitioning key is month. My source is a sequential file which has around 15 rows which pass through a transformer before trying to update the oracle target( Oracle connector) based on key columns. I also have a reject link for the target with "Reject if row not updated" and "Reject for unique constraint violation checked".

When I run the job with the target link hash partitioned on update keys, it rejects some rows with "row not updated". However, for these rows there already exists a match in the target table.

However, if I hash partition the target link and check the in-link sort option, it doesn't reject any rows.

Can anybody please explain whether it is necessary to specify the sort option while doing a hash partition? Why are the rows rejecting if not sorted?

I am on DS8.5

[felixyong]

It will be easier if you can provide a screenshot of the job.

When you didn't use Auto? There's no value in Hash partition the Key of Update in the case of Oracle since it is Range Partition by month. In whatever way, you've to do a Re-Partition.

[abhinavsuri]

Hi Felix

My job looks as follows

Code: Select all

Seq File --->Copy Stg ---> Oracle connector
                                         |
                                         |
                                         V
                                  Reject Seq File

I didn't use auto partition because the job would fail as it was trying to insert two rows with same partition keys into the table and thus facing Unique constraint violation.

So, I tried hashing the input link to the connector based on update keys the data but did not sort. It still failed with unique constraint violation while trying to insert duplicate rows.

Then, I also tried hashing and sorting on update keys. It initially worked but failed in later runs. Thus this approach was also incorrect.

Finally, I hashed based on update keys, sorted and selected unique. This obviously worked because now there were no duplicates within the job. However, It should have worked even if I didn't select unique because I had the "update else insert statement" in the job.

What I understand is that the datastage partitioning is totally independent from oracle partitioning and job design doesn't need to be changed based on table partitioning scheme. Hence, if if the DS partitions data differently Oracle will automatically determine which partition to load the data to when the record hits the DB.

This used to work perfectly with the enterprise stage but somehow seems to have problem with the oracle connector.