Validate all the column data in a Table
Moderators: chulett, rschirm, roy
Validate all the column data in a Table
Hi,
I have a job where i get input in flat file, i need to check the input data againest target DB2 table based on key values, if input row exists in DB2 table validate all the columns data, if any difference - update it in DB2 table, if row not exists insert as a new row.
Thanks in advance!
I have a job where i get input in flat file, i need to check the input data againest target DB2 table based on key values, if input row exists in DB2 table validate all the columns data, if any difference - update it in DB2 table, if row not exists insert as a new row.
Thanks in advance!
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Based on key value in the input file do a left outer join (Select all records from your input) and matching rows from target table. Based on the value of key columns the row existence can be determined and find the difference in existing records using Change capture stage and update the records if there is any change. new records can be inserted into target table.
HTWH.
Regards
Saravanan
HTWH.
Regards
Saravanan
1. Extract all (or some portion if you can predetermine what portion you will need) data from your target table and create two hash files: KeyHash with only key fields, and CDCHash with all fields (and with all defined as key fields).
2. To determine if a row already exists, do a lookup against the KeyHash file in one Transform.
3. If a row already exists, to validate it against current data do a lookup against the CDCHash in another Transform.
This method is detailed in the Best Practices class and document. Searching this forum and/or the ADN forum might find more detail also.
Good luck!!
Larry
2. To determine if a row already exists, do a lookup against the KeyHash file in one Transform.
3. If a row already exists, to validate it against current data do a lookup against the CDCHash in another Transform.
This method is detailed in the Best Practices class and document. Searching this forum and/or the ADN forum might find more detail also.
Good luck!!
Larry