SET QUERY_BAND Syntax Rules
- Each name:value pair must be separated from other name:value pairs by a SEMICOLON character.
No other separator is valid.
- The entire string of name:value pairs for a given SET QUERY_BAND request must be delimited by APOSTROPHE characters.
- The value component of a name:value pair can contain an = (equals) sign, but the name component cannot.
- Pair names cannot be repeated within a query band definition.
- When a query band string has pad characters are in the following positions, Vantage removes them:
- Beginning or end of the query band string
- Before and after an EQUALS SIGN (=) character
- Before and after a SEMICOLON (;) character
- ODBC and JDBC APIs support parameterized requests only for SET QUERY_BAND … FOR TRANSACTION.
Query bands support the QUESTION MARK (?) parameter marker for parameterized requests submitted by the ODBC and JDBC APIs.
For example, the following SQL procedure definition is not valid because it attempts to pass a query band as the parameterized value :qbin.
CREATE PROCEDURE setqbmsr(IN qbin VARCHAR(60)) BEGIN SET QUERY_BAND = :qbin FOR TRANSACTION; INSERT INTO abc (1,2); END;
Open API SQL Interfaces to Support Query Banding
Teradata provides scalar and table UDFs and external procedures to support query banding.
For more information, see Query Band Open APIs (SQL Interfaces) .
Methods of Retrieving Query Band Information
Teradata supplies equivalent CLIv2 and SQL interfaces to return the query band for a specified session.
| Interface | Command or Function Name |
|---|---|
| CLIv2 | MONITOR QUERYBAND |
| SQL | MonitorQueryBand |
Preventing Unauthorized Use of Query Banding by Proxy Users
If you impose no restrictions, a proxy user can use a SET QUERY_BAND statement to change the proxy user for the session and gain unauthorized access to the database. Use the GRANT CONNECT THROUGH statement and the WITH TRUST ONLY clause to instruct the database to honor SET QUERY_BAND statements that set or update a proxy user only if they are part of a trusted request. Program the application trusted user to flag each request as trusted or not trusted to prevent unauthorized use of SET QUERY_BAND to change of the proxy user for a trusted session.
Query Bands and Row-Level Security Constraints
When an application logs on and initiates a pooled session, the row-level security constraints assigned to the application are in effect.
When SET QUERY_BAND is used to assert a proxy user, the row-level security constraints assigned to the proxy user are in effect, regardless of whether these constraints have been assigned directly or through a profile.
SET QUERY_BAND FOR SESSION, Proxy Users, and the Deletion of Volatile and Global Temporary Tables
When a SET QUERY_BAND FOR SESSION request sets, changes, or removes a Proxy User, Vantage also removes all volatile and materialized temporary tables from the session. For materialized global temporary tables, this causes additional locks to be placed on the DBC.TempTables and DBC.TempStatistics system tables.
Vantage does not remove the volatile and materialized temporary tables when you set a Proxy User in a SET QUERY_BAND FOR TRANSACTION request.