SCD stage run on multiple nodes gives incorrect results

[BillB]

Code: Select all

                                  Dim table
                           Oracle Enterprise stage
                                      |
                                      | Reference link
                  Primary link        |
Source table-------------------------SCD----------------------------------.\NUL
Oracle Enterprise stage               |           Output link             Sequential file stage
                                      |
                                      | Dim changes link
                                      |
                                  Peek stage

As an exercise, I've created a simple job using the SCD stage, type 2 processing. I have 10 rows in the source table, and 10 rows in the dimension table. The business key column in each table contains the same 10 distinct values. The Type 2 columns also are identical in both tables. The SCD primary link input is hash partitioned on the business key, the reference link input is partitioned 'Entire'.

The expected result is for 0 rows to go down the dimension changes link, and this is what happens if I run the job on a single node.

But if I run the job on 2 nodes, I get 5 rows on the dimension changes link. If I run on 4 nodes I get 8 rows. These rows are insert (ie unexpired) rows, indicating that they were not found on the reference link.

I'd like this job to run consistently regardless of the number of nodes. Can anyone give me a hint on where to start looking?

Thanks

[ray.wurlod]

Your expectations are correct. Is this version 8.0 or 8.1? There were some SCD problems in 8.0 - check with your support provider.

[BillB]

Thanks Ray. Our version is 8.0.1. I'll raise a support request with IBM.