16.20 - Query Bands and Session-Level Performance - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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, Teradata Database 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