Archives of the TeradataForum

Message Posted: Wed, 03 Mar 2010 @ 14:57:24 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

How about:

(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.

Or:

(2) again using the LastAlterTimeStamp column yuo could simply issue:

     Select *
     From control-table
     Where lastaltertimestamp > 'value stored locally on pc'
     Order by ...;

Or:

(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.

Or:

(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).

Some ideas.

Cheers,

Dave

Ward Analytics Ltd - Information in motion ( www.ward-analytics.com )

	<Prev	Next>		<<First	<Prev	Next>	Last>>