15.10 - Query Band API Features - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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;'

Note: 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 SQL Data Definition Language.

    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 SQL Data Definition Language.

    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 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” on page 51.

    To learn more about these interfaces and how to retrieve query bands, see
    Chapter 6: “Query Band APIs.”