Loading Self referencing table

[dodda]

Hello

I am loading self referecing data into table.
i.e i have Col1 and Col2 and couple of other fields. Col2 is a foreign key to the col1 which is PK.

When i try to laod my data is geting rejected because of FK vilation (sqlcode:-2291).

Is there a mechanisam tht this can be done.

Thanks

[ray.wurlod]

You (or the DBA) needs to have created a row in the table before applying the FOREIGN KEY constraint. It's not a DataStage problem.

[kwwilliams]

Hello

I am loading self referecing data into table.
i.e i have Col1 and Col2 and couple of other fields. Col2 is a foreign key to the col1 which is PK.

When i try to laod my data is geting rejected because of FK vilation (sqlcode:-2291).

Is there a mechanisam tht this can be done.

Thanks

Wow this sounds really painful. I personally would ask my DBA/modeler if we could change the design to contain two tables because it is going to be difficult to implement in any batch construct.

Your foreign key constraints for col1 data that need to be in the table may be in the same data set that you are attempting to load.

1. You could perform a lookup to the table to ensure that all of the values are present in col1 before you load (lookup with a reject link). However the data in the reject link could also contain col1 and col2 combinations which aren't in the table. That makes me think that this is not a viable design option.
2. You could have a reject link from your database stage to capture all of these violations. Then attempt to insert those rejects into the database again. If all of your rejects are because of foreign key constraints eventually you would reach a point where they all load.

All the solutions I can think of to your problem lack elegance. I don't mind brute force techniques as described above, but it always makes me wonder whether I have a proper design to start out.

[madhukar]

approach 1:
split the load into 2 jobs. in the first load insert col1 (pk) and in the next load update col2.

approach 2:
disable fk before load and enable it after load