We need a way to know which rows changed in a TeraData database since the last time we imported data from that database. There is nothing about the table or data in the table in the TeraData database that proves to be a reliable change-signal we can use to determine this. In SQL Server it appears that the Change Tracking feature does exactly what we need, however the source system is TeraData. Does anyone know if there is a feature in TeraData, or a third-party tool that works with TeraData, that would effectively add the functionality provided by SQL Server Change Tracking, but to a TeraData database?
Asked
Active
Viewed 531 times
0
-
Are you looking at this from a replication perspective, failover system perspective, or merely an ETL perspective of being able to identify which records have been altered? – Rob Paller Mar 14 '13 at 20:51
-
This is strictly for ETL. – Orlando Colamatteo Mar 15 '13 at 19:19
1 Answers
0
Teradata is an OLAP database. The feature you are referring too in Sql Server is for OLTP type processing. Teradata is assuming you will be performing large set operations, not small single row level operations. Everything about Teradata's optimizer and feature set is based on generating performance for those large set operations.
The only way we have managed to track changes within Teradata databases was to:
- Add an update timestamp attribute to the table that is maintained by the code changing the data. (the UI or script or by a trigger within the database)
- OR create a trigger on each table you wish to track. This trigger can either: A. Append to another table just the record key and an update timestamp B. Or append to another table the entire set of attributes and an update timestamp
- OR capture the entire table at every time window, and compare the data. We only did this for very small datasets. (Domain data)
We have done this for a variety of source databases including Oracle and Teradata. The trigger is fairly simple and would append a new row to your "data capture" table for each Insert, Update AND Delete on your source table.
We ended up generating the triggers from a template for every table we needed to capture changes from, as well as the DDL for the Data Capture table. (The copy we are appending too). The data capture table has the exact same attributes as the source table PLUS at least 2 metadata attributes: Change type, and a Timestamp.
Additionally, we ended up adding a "Copy timestamp" to the metadata attributes that we could fill out as we picked up the data. This was required because the data could be appended out of TIME sequence. In other words, a short transaction may complete the append to the Data Capture table well before a long running transaction, that began a lot early, ever finishes.
This all may appear a little more complex than just referencing SQL Servers "change tracking" but it is quite simple and does not impact the overall performance of the database much. Our measurements were 3% impact because of all the triggers we added.
