Function of Query Bands - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

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).

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 the 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 Vantage 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.

Following are 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, and Teradata Vantage™ - Application Programming Reference, B035-1090.

  • 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. For more information, see Teradata Vantage™ - Application Programming Reference, B035-1090.

    For directory-based connections and applications that all connect to Vantage 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.