A query band is a set of name‑value pairs that can be set on a session or transaction to identify a query's originating source. The query band enables users to add their own identifiers to the current set of session identification fields. Without query bands, the pooling mechanisms that SQL‑generating tools and web applications use hide the identity of users because each connection in the pool logs into the database using the same user account. Therefore, there is no way to tell the source of the request when the request comes from a multi‑tiered application.
The Query Band feature enables requests coming from a single logon process to be classified into different workloads based on the query band that is set by the originating application. Query banding also enables an application to set different priorities for different requests. The application can set a different query band for each type of job, causing the requests to be classified into different workloads. Then the different workloads can run at different priorities, instead of running the entire application at a high priority. By adjusting the priorities of its requests, the application can enable better use of system resources.
A session query band is stored in a session table and recovered after a system reset. A transaction query band is discarded when the transaction ends (commits, rolls back, or aborts).
Syntax for Setting Query Band
EXEC SQL
SET QUERY_BAND = 'pair_name=pair_value;[...pair_name=pair_value;]'|NONE [UPDATE] FOR SESSION|TRANSACTION;
Syntax for Removing Query Band
EXEC SQL
SET QUERY_BAND = NONE FOR SESSION|TRANSACTION;
|
Variable or Keyword |
Description |
|
pair_name |
This variable is the name component of a query band specification. |
|
pair_value |
This variable is the value component of a query band specification. |
|
UPDATE |
(Optional) This keyword adds a name‑value pair to the current session or transaction. |
To set the priority at 1 for a high workload in the current session:
EXEC SQL
SET QUERY_BAND = 'priority=1;workload=high;' FOR SESSION;
To set the same values for the current transaction:
EXEC SQL
SET QUERY_BAND = 'priority=1;workload=high;' FOR TRANSACTION;
To set the proxy user cluster with proxy role role1 for the current session:
EXEC SQL
SET QUERY_BAND = 'PROXYUSER=cluser1; PROXYROLE=role1;' FOR SESSION;
To set the proxy user cluster with proxy role role1 for the current transaction:
EXEC SQL
SET QUERY_BAND = 'PROXYUSER=cluser1; PROXYROLE=role1;' FOR TRANSACTION;
To add a name‑value pair to the current session, use UPDATE in a second SET QUERY_BAND statement:
EXEC SQL
SET QUERY_BAND = 'city=san diego;' FOR SESSION;
...
EXEC SQL
SET QUERY_BAND = 'state=california;' UPDATE FOR SESSION;
To remove query banding from the current session:
EXEC SQL
SET QUERY_BAND = NONE FOR SESSION;
To remove query banding from the current transaction:
EXEC SQL
SET QUERY_BAND = NONE FOR TRANSACTION;