16.20 - Query Band API Features - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Query banding is a method for tracking system usage and managing task priorities. A query band is a list of “name=value” pairs in a string contained within apostrophes that is defined by the user or middle-tier application as shown below.

'org=Finance;report=EndOfYear;universe=west;'
The name-value pairs are separated by a semicolon.
There are three types of query bands:
  • A session query band, which is stored in the session table and recovered after a system reset.
  • A transaction query band, which is discarded when the transaction ends (for example, a commit, rollback, or abort).
  • A profile query band, which is set for the session at logon. The profile query band is not saved in the session table, so after a restart the session is initialized with the profile query band based on the current profile setting.

You can set a query band for the transaction and session using the SQL statement, SET QUERY_BAND. For information on SET QUERY_BAND, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

You can set a default query band in a profile with the CREATE PROFILE statement and assign it to a user with CREATE USER or MODIFY USER. For information on CREATE PROFILE, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

By setting a query band you can:
  • Identify the user, application, or report that originated the request from a middle-tiered application.
  • Identify what user, application, report, and even what part of an application issued a request (for example, a query band can be used for accounting, troubleshooting, and in other types of system management operations).
  • Give requests a higher priority. For example, a query band can make a request issued for an interactive report a higher priority than one issued for a report that generates output files.
  • Increase the priority of an urgent job. For example, if the CEO needs a report for a board review that starts in 20 minutes, a query band can be used to expedite the job.
  • Create requests that make up a “job” to be grouped for accounting and control purposes.
There are several uses for query bands. A query band can be:
  • Logged by Database Query Log (DBQL). DBQL reports are created using the query band name-values pairs to provide additional refinement for accounting and resource allocation purposes and to assist in troubleshooting performance problems.
  • Used for rule checking and Workload Classification. Query band name-value pairs can be associated with TASM Filter rules and defined as workload attributes (see Teradata® Viewpoint User Guide, B035-2206 for details on these rules).
  • Used to determine the origin of a request that may be consuming system resources or blocking other requests.
  • Used as a system variable. A query band can be set for a session and retrieved using APIs.
    Through these interfaces, the following information can be retrieved:
    • The concatenated transaction and session query band for the specified session.
    • The concatenated query band for the current transaction and session.
    • The name and value pairs in the query band.
    • The value of the specified name in the current query band.

      For examples on performing Query band requests and functions, see Examples Using PM/API and Open APIs.

      To learn more about these interfaces and how to retrieve query bands, see Workload Management: Query Band APIs.

Examples Using PM/API and Open APIs

The following table describes the different uses of the query band APIs.

You can use ... To ...
MONITOR QUERYBAND or MonitorQueryBand return the concatenated query band for session number 1102 on host ID 20 running on vproc 16383.
GetQueryBand or GetQueryBandSP return the concatenated query band string for the current transaction, session, and profile.
GetQueryBandValue query the DBQLogTbl based on names and values specified in the query band name input argument.
GetQueryBandValueSP search the session name-value pairs in the query band and return the value that corresponds to the query band name “aa.”
GetQueryBandPairs return the query band in name and value columns.

Functionality

The following table describes the query band interfaces that are used to track system usage and manage task priorities.

If you want to ... Use the following SQL interface ... Or, the following CLIv2 or Teradata JDBC Driver request ...
return the name and value pairs in the query band GetQueryBandPairs
return the concatenated query band for the current transaction and session GetQueryBand

or

GetQueryBandSP

return the value of the specified name in the current query band or NULL GetQueryBandValue

or

GetQueryBandValueSP

return the concatenated query band for the specified session MonitorQueryBand MONITOR QUERYBAND
return all query band names and descriptions, including those dropped from a release QueryBandReservedNames_TBF