Archives of the TeradataForum
Message Posted: Wed, 03 Mar 2010 @ 14:57:24 GMT
(1) a LastAlterTimeStamp column on each control table ? This gets updated on a row when that row is inserted or updated. Your client app then issues:
Select max(lastaltertimestamp) From control-table Having max(lastaltertimestamp) > 'value stored locally on pc';
If this returns 0 rows then nothing in the control table has been updated.
If it returns 1 row then one or more rows have been updated. At this point you need to download the changed rows (or the whole table) and replace the local contents ? When you download changed rows store the max value locally for use next time.
(2) again using the LastAlterTimeStamp column yuo could simply issue:
Select * From control-table Where lastaltertimestamp > 'value stored locally on pc' Order by ...;
(3) whenever a control table is maintained (inserts, updates or deletes) the process that does this changes the tables COMMENTSTRING in the dictionary. (using the COMMMENT ON command). Your client application then does a single
Select databasename, tablename, commentstring From dbc.tables Where .....;
...and compares the returned values with those held locally. For any tables where the value has changed then those tables need to be downloaded.
An advantage of #3 commpared to #1 and #2 is that the 'check sql' is a single sql request that checks all tables. The first two require a separate sql request for each table. This may make a significant difference if your Teradata system is under heavy load.
(4) have a 'dummy row' on each control table which has a known PI value and a LastAlterTimeStamp column contains the date/time that the table was last maintained. The 'check sql' is still a separate request for each table, but those are now single-amp pi lookup requests, they'll be fast, less likely to be impacted by Teradata loading and you ** may ** be able to run them in an expedited AWT 'class' (can't remember the exact terminology off hand).
Ward Analytics Ltd: Information in motion (www.ward-analytics.com)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|