scd with oracle tables -- problem

[kiran0435]

The design for my job is as follows:
The version of datastage is 8.0.1v.

Code: Select all

                 Oracle Enterprise stage 
                                  | 
                                  | Reference link 
             Primary link        | 
Source table----------------------SCD---------------------------dataset
Oracle Enterprise stage    |           Output link                                           
                                   | Dim changes link 
                                  | 
                                   Peek stage                                          

I am using oracle enterprise stage as source and reference tables.

The column list for reference link is sno, eno, ename, salary in which sno is surrogate key, eno is business key, ename and salary are type 1 attributes.
The metadata for the columns is ( sno: decimal[38] ,eno: decimal[38] , ename : varchar[50], salary : decimal[10,2])
The column list for source link is eno, ename ,salary.

Both metadata are same and I imported from table definitions.

The problem I am facing is :
Example records:

Reference Table:
1, 6789, kiran,1000
2,6790, raj, 10000

source table:
6789, kirankumar, 1000
567, somu, 19990

The expected output of scd stage is
1, 6789, kirankumar, 1000
3, 567, somu, 19990

But the output i am getting is

1, NULL, kirankumar, 1000
3, 567, somu, 19990

I am getting Null for the matching business key. Even when I view data from oracle enterprise stage I am viewing the data as expected.

I used sequential files as input but got the expected result.

Is there anything I missed.

This runs fine when i replace the oracle table with sequential files.

[SURA]

I not yet worked with SCD stage, but my guess is, did your ref table is created as a proper type 2 table?

[kiran0435]

It is not type 2 , it is type 1.

I have worked in 8.1v , with db2 tables and I was getting the expected output.

This 8.0.1v , i am using with oracle tables. With sequential file as reference and source... i am getting the expected output........ but with oracle tables i am not getting the output...........

[chulett]

The 8.0.x release was notoriously buggy, have you checked to see if there are any fixpacks available for this?

[BillB]

Hi

Also consider that it may not matter that the business key is null. I would think that in the end you'd want two statements like this:

Code: Select all

UPDATE table SET ename = 'kirankumar', salary = 1000 WHERE sno = 1;

INSERT INTO table (sno, eno, ename, salary) VALUES (3, 567, 'somu', 19990);

and your output is giving you all the data you need to do that. You just need to code up user-defined update & insert statements when you come to maintain your table.

Cheers
Bill

[kiran0435]

yes, Bill. I have implemented the logic in that manner only as of now.

But I want to know why the output is like that from SCD Stage.