15.00 - Query Bands and Session-Level Performance - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Query Bands and Session‑Level Performance

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:

  • Database Administration
  • The RedriveProtection and RedriveDefaultParticipation DBS Control fields in Utilities
  • “Reserved Redrive Query Band” on page 873