Query Bands and Session-Level Performance - 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™

The session-level query band request is more expensive than the transaction-level query band request because the FOR SESSION option adds the overhead of updating the queryband column in DBC.SessionTbl, which enables it to be recovered after a system restart.

The VOLATILE option for SET QUERY_BAND FOR SESSION requests enables you to select performance over recoverability by reducing the cost of the request at the expense of being able to recover it after a system restart occurs.

For example, if you want a query band to be restored after a restart, you should submit a SET QUERY_BAND FOR SESSION request. However, if you prefer performance over the capability to restore an active query band, you should specify the VOLATILE keyword with a SET QUERY_BAND FOR SESSION request using this syntax:

SET QUERY_BAND = 'name=value;' FOR SESSION VOLATILE;

Note that you can specify both the UPDATE and the VOLATILE option within the same SET QUERY_BAND request. You can also use a combination of both versions of a SET QUERY_BAND FOR SESSION request. An application can set some name:value pairs FOR SESSION that are saved in DBC.SessionTbl, while specifying those name:value pairs that are specific to the request using the SET QUERY_BAND UPDATE and VOLATILE options. On a restart, Vantage will restore the query band saved in DBC.SessionTbl, but not the query band name-value pairs set using the VOLATILE option. For example:

SET QUERY_BAND = 'clientuser=x11;group=acct;' FOR SESSION;
SEL ...
SET QUERY_BAND = 'job=a1;' UPDATE FOR SESSION VOLATILE;
INS ...
INS ...
SET QUERY_BAND = 'job=b2;' UPDATE FOR SESSION VOLATILE;
INS ...
INS ...
If Redrive protection is active for the session and a SESSION VOLATILE query band is set, the query band will be recovered after a restart as part of the Redrive feature. This is true only for database restarts that are hidden from the application. For details about Redrive protection, see:
  • Teradata Vantage™ - Database Administration, B035-1093
  • The RedriveProtection and RedriveDefaultParticipation DBS Control fields in Teradata Vantage™ - Database Utilities, B035-1102
  • Reserved Redrive Query Band