Query Bands and Session-Level Performance - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
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