Proxy Users and Deleting Global Temporary/Volatile Tables | Teradata Vantage - SET QUERY_BAND FOR SESSION, Proxy Users, and the Deletion of Volatile and Global Temporary Tables - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

When a SET QUERY_BAND FOR SESSION request sets, changes, or removes a Proxy User, Vantage also removes all volatile and materialized temporary tables from the session. For materialized global temporary tables, this causes additional locks to be placed on the DBC.TempTables and DBC.TempStatistics system tables. Note the following explain report, with the locking behavior highlighted in boldface type.

EXPLAIN SET QUERY_BAND = 'PROXYUSER=dg12345;' FOR SESSION;

Result:

 *** Help information returned. 17 rows.
 *** Total elapsed time was 1 second.

Explanation
------------------------------------------------------------------
 1) First,  we lock DBC.TempStatistics for write on a RowHash, 
    and  welock DBC.TempTables for write on a RowHash.
 2) Next, we will apply the QUERY_BAND to the session.
 3) We get the volatile and temporary table ids and delete the
    corresponding table headers and data.
 4) We execute the following steps in parallel.
      1) We do a single-AMP DELETE from DBC.TempTables by way of
         the primary index with no residual conditions.
      2) We do a single-AMP DELETE from DBC.TempStatistics by way
         of the primary index with no residual conditions.
 3) We do a single-AMP UPDATE from DBC.SessionTbl by way of the
    primary index with no residual conditions.
 5) Finally, we send out an END TRANSACTION step to all AMPs 
    involved in processing the request.
 -> No rows are returned to the user as the result of statement 1.

Vantage does not remove the volatile and materialized temporary tables when you set a Proxy User in a SET QUERY_BAND FOR TRANSACTION request.