Hi,
Can anybody tell me how Referential integrity is handled in
DataStage,
Say we have two Oracle tables
i want to check if The column data present in table 1 has the corresponding value in table 2 this check has to be performed on Fk on the table2 PK is on the Tabe1
Thanks
von
Referential Integrity
Moderators: chulett, rschirm, roy
Von,
If you will not be relying on the DB to ensure RI then it is up to you as a developer to ensure RI is enforced. One way this can be accomplished would be to use Hash tables as lookups and reject rows where FK's are not found. I'm sure others will have more to say, but I believe the answer to be - it is a manual process if the DB is not enforcing RI.
Regards,
Michael Hester
If you will not be relying on the DB to ensure RI then it is up to you as a developer to ensure RI is enforced. One way this can be accomplished would be to use Hash tables as lookups and reject rows where FK's are not found. I'm sure others will have more to say, but I believe the answer to be - it is a manual process if the DB is not enforcing RI.
Regards,
Michael Hester
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
I think Mike has answered your question completely.
Load the data from table 2 into a hashed file.
Use a ODBC stage for table 1. Use a transformer which uses both ODBC & Hashed file (used as a lookup) stages as inputs and map the foreign key from ODBC stage to the primary key hashed file stage. Have a output link to write the records for which the foreign key from ODBC is found in hashed file. Have a rejects link from transformer to write the records for which the foreign key from ODBC is not found in hashed file.
I am not sure if there is some other way to do this. Only our experts can tell us about their experiences.
Regards,
Srikanth Reddy
Load the data from table 2 into a hashed file.
Use a ODBC stage for table 1. Use a transformer which uses both ODBC & Hashed file (used as a lookup) stages as inputs and map the foreign key from ODBC stage to the primary key hashed file stage. Have a output link to write the records for which the foreign key from ODBC is found in hashed file. Have a rejects link from transformer to write the records for which the foreign key from ODBC is not found in hashed file.
I am not sure if there is some other way to do this. Only our experts can tell us about their experiences.
Regards,
Srikanth Reddy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Handling referential integrity in DataStage - like in any other realm - is as simple as making sure that referential integrity is not violated.
To do this in DataStage, you need to have accessible a reference lookup to the referenced column values in the referenced table when loading the referring table, and not attempt to load the referring table with any row that would violate foreign key constraints.
Easiest is to pre-load the referred columns into a hashed file. This might be a key-only table, or might contain additional columns if you're using the reference lookup to obtain additional information for the load, such as performing de-normalization or maintenance of slowly-changing dimensions.
Some databases allow you to guarantee referential integrity by, instead of taking a violation, generating a new row in the referenced table, or changing the foreign key value to some predetermined "default". There is no reason DataStage could not take advantage of this; it would mean that there would be nothing to do in DataStage to ensure that referential integrity is not violated. In general, however, this approach appears not to be in favour.
To do this in DataStage, you need to have accessible a reference lookup to the referenced column values in the referenced table when loading the referring table, and not attempt to load the referring table with any row that would violate foreign key constraints.
Easiest is to pre-load the referred columns into a hashed file. This might be a key-only table, or might contain additional columns if you're using the reference lookup to obtain additional information for the load, such as performing de-normalization or maintenance of slowly-changing dimensions.
Some databases allow you to guarantee referential integrity by, instead of taking a violation, generating a new row in the referenced table, or changing the foreign key value to some predetermined "default". There is no reason DataStage could not take advantage of this; it would mean that there would be nothing to do in DataStage to ensure that referential integrity is not violated. In general, however, this approach appears not to be in favour.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.