All Forums Database
tclear 12 posts Joined 06/12
26 Jun 2012
Table Load Timestamp

Tables that are dropped and reloaded daily, how can I determine if the table has been today's load, not yesterday's?

D3V1L 10 posts Joined 01/12
26 Jun 2012
SEL createtimestamp, lastaltertimestamp FROM dbc.tables WHERE tablename LIKE '%TABLE%'

Cheers!
d3V1L

Experience is what we get, when we don't get what we want!

ulrich 816 posts Joined 09/09
27 Jun 2012

You might also ask the developers to add a load_date  or load_timestamp field to the table which contains the required information.

In case of load_date the impact of space usage can be limited by using MVC.

If this is not an option you can ask to maintain a log table where the load process which maintains this tables is adding log infos for each run. So you could check there.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

tclear 12 posts Joined 06/12
27 Jun 2012

D3V1L - In our environment, the timestamps (createtimestamp, lastaltertimestamp) in dbc.tables refer to when the table was created, or altered, not when the data was refreshed.

ulrich - appreciate your feedback. I will ask our folks about a log file.

the table is a forward looking table that is dropped and reloaded daily.

No history is kept.

our current process is to run

select min(sched_date) from table

if the output is today's date, the table has been loaded.

we can continue to do this but it is a frustrating and inefficient process.

if the table is not loaded, we have to wait, run the query again.

repeat until we get today's date.

 

 

 

 

ulrich 816 posts Joined 09/09
27 Jun 2012

You mentioned in your post "dropped" and in this case it has to be created afterwards. In this scenario D3V1L solution would work.

I seems you delete and insert / select afterwards.

Ask for a log table, not for a log file.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Adeel Chaudhry 773 posts Joined 04/08
02 Jul 2012

Are there multiple tables you are working with or is it just one table?

-- If you are stuck at something .... consider it an opportunity to think anew.

tclear 12 posts Joined 06/12
05 Jul 2012

Sorry if my terminology is lacking. I am only a user.

Here is what happens. Every day, the previous day's data is no longer on the table.

Only the current day's data is on the table. Whether that means purged, dropped, deleted, erased, replaced, or ???, do not know or care.

Because of inconsistent load times, I need to know is when the table has the current day's data, not the previous day's data.

I have a query, select min(order_date) from databasename.tablename

If result is today, the table is current.

Otherwise the table has stale data.

It seems there should be a better way to determine if the table has the current day's data.

Adeel,

There may be other tables that are on a daily refresh but this table is the one that gives the most heartburn.

Thanks

 

 

 

ulrich 816 posts Joined 09/09
05 Jul 2012

As stated before you should ask IT that they maintain a log table wich contains information when this table was last time refreshed.

In this szenario you would query your log table to check if todays run ended successfully.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.