Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Table level checksum for managing offline table storage
 
From:   DWellman

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023