Query Banding

Teradata Preprocessor2 for Embedded SQL Programmer Guide

brand
Teradata Tools and Utilities
prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

A query band is a set of name‑value pairs that can be set on a session or transaction to identify a query's originating source. The query band enables users to add their own identifiers to the current set of session identification fields. Without query bands, the pooling mechanisms that SQL‑generating tools and web applications use hide the identity of users because each connection in the pool logs into the database using the same user account. Therefore, there is no way to tell the source of the request when the request comes from a multi‑tiered application.

The Query Band feature enables requests coming from a single logon process to be classified into different workloads based on the query band that is set by the originating application. Query banding also enables an application to set different priorities for different requests. The application can set a different query band for each type of job, causing the requests to be classified into different workloads. Then the different workloads can run at different priorities, instead of running the entire application at a high priority. By adjusting the priorities of its requests, the application can enable better use of system resources.

A session query band is stored in a session table and recovered after a system reset. A transaction query band is discarded when the transaction ends (commits, rolls back, or aborts).

Syntax for Setting Query Band

EXEC SQL
SET QUERY_BAND = 'pair_name=pair_value;[...pair_name=pair_value;]'|NONE [UPDATE] FOR SESSION|TRANSACTION;

 

Syntax for Removing Query Band

EXEC SQL
SET QUERY_BAND = NONE FOR SESSION|TRANSACTION;
 
 

Variable or Keyword

Description

pair_name

This variable is the name component of a query band specification.

pair_value

This variable is the value component of a query band specification.

UPDATE

(Optional) This keyword adds a name‑value pair to the current session or transaction.

To set the priority at 1 for a high workload in the current session:

EXEC SQL
  SET QUERY_BAND = 'priority=1;workload=high;' FOR SESSION;

To set the same values for the current transaction:

EXEC SQL
  SET QUERY_BAND = 'priority=1;workload=high;' FOR TRANSACTION;

To set the proxy user cluster with proxy role role1 for the current session:

EXEC SQL
  SET QUERY_BAND = 'PROXYUSER=cluser1; PROXYROLE=role1;' FOR SESSION;

To set the proxy user cluster with proxy role role1 for the current transaction:

EXEC SQL
  SET QUERY_BAND = 'PROXYUSER=cluser1; PROXYROLE=role1;' FOR TRANSACTION;

To add a name‑value pair to the current session, use UPDATE in a second SET QUERY_BAND statement:

EXEC SQL
  SET QUERY_BAND = 'city=san diego;' FOR SESSION;
...
 
EXEC SQL
  SET QUERY_BAND = 'state=california;' UPDATE FOR SESSION;

To remove query banding from the current session:

EXEC SQL
  SET QUERY_BAND = NONE FOR SESSION;

To remove query banding from the current transaction:

EXEC SQL
  SET QUERY_BAND = NONE FOR TRANSACTION;