Archives of the TeradataForum

Message Posted: Wed, 13 Aug 2003 @ 10:55:35 GMT

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

Craig,

In general I try to avoid the drop/create/load processing, instead I use Delete/Load. The Delete code checks that the table exists and creates it if necessary. Taking this approach means less processing against the dictionary, no adding back 'loads' of (usually) redundant AccessRights.

To avoid the problems of user queries interfering with data loads (particularly where you're refreshing the table each day) I've often loaded into a separate, identical table, done any processing and then renamed the existing table to 'table_old' and renamed the new one from 'table_new' to 'table'. You also have to include processing to drop 'table_old' at the appropriate time. There is a small time window where an object called 'table' does not exist, but user queries will fail very quickly, and the rename will need to wait for any existing queries to complete. This has always worked well in situations where I've used it.

If you specifically want to check for existing users running queries against the table, assuming that you're using Bteq, try the following;

locking table 'table name' for exclusive no wait;

This query will try and place an exclusive lock on the table and will fail if anyone else currently has it locked. I can't remember the return code you get for a failure.

At this point your code can issue a notify message to the ops to abort existing users or whatever it is you need to do.

Cheers,

Dave

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