hi All,
i am extracting data from one table of teradata and putting it to another making some necessary transformations. if i run this job today then i am supposed to get the new records in the input table since last run date. but my input table doesn't have any column as timestamps to compare. if i save the last run date still i don't have anything in the table to get records after the last run date.
can anyone suggest anything for this?
thanks.
records sincs last run date
Moderators: chulett, rschirm, roy
records sincs last run date
g.kiran
-
Sainath.Srinivasan
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I assume there will be some column for the people populating it to determine whether the changes are successful as otherwise you may landup updating all rows.
Alternate options are
1.) Set a trigger to populate another temp table for any DML actions and use this temp table in your job.
2.) Use DataStage to generate checksum for each row and store in some table. So next time you receive the same key (assume there is some key), you can match this checksum to determine whether there has been any change since last capture.
In software like Oracle, you have CDC (Change Data Capture) mechanism for this purpose that you can use.
Alternate options are
1.) Set a trigger to populate another temp table for any DML actions and use this temp table in your job.
2.) Use DataStage to generate checksum for each row and store in some table. So next time you receive the same key (assume there is some key), you can match this checksum to determine whether there has been any change since last capture.
In software like Oracle, you have CDC (Change Data Capture) mechanism for this purpose that you can use.
My first suggestion is to bone up on Kimball's concept behind data warehousing.
My second suggestion is to implement a local resource file that is compromised of the previous data (and updated every time via lookup within Transformer), and compare the key fields with the input data. It's not as robust as EE's Change Capture stage, but it's a simple solution to identify new records.
My second suggestion is to implement a local resource file that is compromised of the previous data (and updated every time via lookup within Transformer), and compare the key fields with the input data. It's not as robust as EE's Change Capture stage, but it's a simple solution to identify new records.
Doing comparision with all your records will cost you lot in terms of time and resource.
How hard is to add a new column with date and timestamp in your table. Make the column defaulted to currect date and timestamp. Create an index to this new date column. Use this column to extract data which will cost you less.
Thanks
How hard is to add a new column with date and timestamp in your table. Make the column defaulted to currect date and timestamp. Create an index to this new date column. Use this column to extract data which will cost you less.
Thanks
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar