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 within apostrophes, defined by the user or middle-tier application as follows.
'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.
You can set a default query band in a profile with the CREATE PROFILE statement and assign the profile to a user with CREATE USER or MODIFY USER. See CREATE PROFILE.
- 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.
- 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.
| Query Band API | Description |
|---|---|
| MONITOR QUERYBAND or MonitorQueryBand | Returns the concatenated query band for session number 1102 on host ID 20 running on vproc 16383. |
| GetQueryBand or GetQueryBandSP | Returns the concatenated query band string for the current transaction, session, and profile. |
| GetQueryBandValue | Queries the DBQLogTbl based on names and values specified in the query band name input argument. |
| GetQueryBandValueSP | Searches the session name-value pairs in the query band and return the value that corresponds to the query band name “aa.” |
| GetQueryBandPairs | Returns 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.
| Goal | SQL Interface to Use | CLIv2 or Teradata JDBC Driver Request to Use |
|---|---|---|
| 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 | — |