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;'
- 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.
- 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.
- 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 |
— |
| return the value of the specified name in the current query band or NULL |
GetQueryBandValue
or |
— |
| 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 | — |