Validate all the column data in a Table

Terala · Post by **Terala** » Mon Jun 13, 2005 8:30 am

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!

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 13, 2005 9:31 am

Are you performing a slowly-changing-dimension?

Search for that topic in the forum for more info.

Terala · Post by **Terala** » Mon Jun 13, 2005 10:10 am

no we are not using slowly-changing-dimension.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 13, 2005 10:13 am

Even then, you can search for that topic as it contains the steps to achieve your requirement.

You may have to decide on which columns you wish to track because finding changes on even one column - especially if it is a varchar - can be tedious and time consuming during execution.

ketfos · Post by **ketfos** » Mon Jun 13, 2005 12:00 pm

Hi,
You can use in the taget stage - Update Action - Update Existing rows or Insert News.

Thks
Ketfos

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 14, 2005 1:51 am

I agree with keftos. If you want to update change to even one column, you may be better-off performing 'insert new or update existing rows' or vice-versa as that will give you the flexibility to track any change in any column.

Terala · Post by **Terala** » Wed Jun 15, 2005 7:53 am

Thanks for the info,
But the solution to use Update or Insert option will hit the data base for each and every record, and i guess sometimes for each record multiple times.. so, i believe it is extensive DB call process. Please advise me if i am wrong.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jun 15, 2005 7:57 am

As you are attempting to check for any changes in even one column, you are in a better situation to decide this.

Also you can improve performance using hash files, checksum etc. Search this forum for more info.

elavenil · Post by **elavenil** » Wed Jun 15, 2005 9:56 am

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

lebos · Post by **lebos** » Wed Jun 15, 2005 11:20 am

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