15.00 - Function of Query Bands - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Function of Query Bands

Query bands provide you with a way to set your own unique identifiers on Teradata sessions and transactions and to add those identifiers to the current set of session identification fields maintained in DBC.SessionTbl (see “HELP SESSION” on page 946).

A query band is a set of user‑ or middle tier application‑defined name:value pairs that can be set on a session or a transaction to identify the originating source of a query. You can think of a query band as metadata that is wrapped around a request. Once a query band has been defined, the system passes it to Teradata Database as a list of name:value pairs in a string literal such as the following examples.

     'org=Finance;report=EndOfYear;universe=west'
 
     'Job=payroll;Userid=dg120444;Jobsession=1122;'

Although Teradata Database has several request identifiers such as user ID, account string, client ID, client address, application name, and so on, query bands provide the following additional useful functionality.

  • A means for multitiered applications such as SQL generators and web applications that use session pooling to identify users and applications that would otherwise be unidentifiable for the management of chargeback, account, troubleshooting, and related tasks.
  • Assigning different performance priorities for doing things like directly raising the priority of a high priority job.
  • For example, some applications need to give requests such as those issued for interactive reports a higher priority than requests issued for reports that generate output files.

  • A means for grouping requests into arbitrary jobs for accounting and control purposes.
  • Query banding also provides a means for managing the application end of accounting and control.

    The name and the value for each pair can be defined either by a user or by a middle tier application, permitting query bands to be customized to the unique needs of each application.

    The following bullets list some of the uses for query bands.

  • You can create DBQL reports using query band name:value pairs to provide additional refinement for accounting and resource allocation purposes. Query bands are logged as a column in the DBQL detail logging table
  • You can also use query band name:value pairs for debugging performance problems.

  • You can use query band name:value pairs as system variables.
  • You can set a query band for a session and then retrieve information about it using UDFs or external procedures. See “Other Methods of Retrieving Query Band Information” on page 866, and Application Programming Reference.

  • You can set a query band to assert any of the following for a trusted session.
  • A proxy user
  • A proxy role
  • Both a proxy user and a proxy role
  • You can associate query band name:value pairs with Teradata dynamic workload management software filter rules. See Application Programming Reference.
  • For directory-based connections and applications that all connect to Teradata Database with a single logon using a web-based application server, you can distinguish the originating source of requests using the query band to permit resource control using Teradata dynamic workload management software rules.

  • You can define query band name:value pairs as workload classification attributes in Teradata dynamic workload management software.
  • This enables all requests from a single logon to be classified into different workloads based on the query band set by the originating application.

    It also enables an application to set different priorities for different requests. For example, a GUI application might have some dialogs that require quick responses and others that submit long running reports that run in the background. The application can set a different query band for each type of job, causing the requests to be classified into different workloads and thus running at different priorities. As a result, instead of running the entire application at a high priority, the application can adjust the priorities of its requests to enable more optimal use of system resources.

    Note that you cannot circumvent filter rules for Query Bands by declaring them to be bypassed. Note that Teradata dynamic workload management software refers to Query Bands by the object type code QRYBND.