SET QUERY_BAND Syntax Elements - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

pair_name

Name component of a query band specification.

The maximum size for each pair_name is 128 UNICODE characters. For more information about database object names, see Object Names.

The number of pair_name=pair_value pairs is limited to the maximum length of the string, which is 4,096 UNICODE characters, including pad characters.

Enclose the set of name=value pairs for the query band in APOSTROPHE characters. To embed an APOSTROPHE character in a pair name, type it twice. Otherwise, it is interpreted as a pair_name=pair_value string terminator.

pair_name cannot contain any of the following characters:
  • EQUALS SIGN (=)
  • SEMICOLON (;)
  • NULL
Do not specify any reserved pair name except BINPACKALGO4PARQUET, which sets the binpacking algorithm to use when reading Parquet files. The binpacking algorithm determines how the files are distributed on the AMPs.

For a list of reserved query band names, see "Reserved Query Band Names and Values" in Query Bands and Load Utilities.

pair_value

Value component of a query band specification.

pair_value can be up to 256 VARCHAR UNICODE characters in length.

The number of name=value pairs is limited to the maximum length of the string, which is 2,048 UNICODE characters, including pad characters.

pair_value cannot contain any of the following characters.
  • SEMICOLON (;)
  • NULL

To embed an APOSTROPHE character in a pair value, type it twice. Otherwise, it is interpreted as a pair_name =pair_value string terminator.

If pair_name is BINPACKALGO4PARQUET, which sets the binpacking algorithm to use when reading Parquet files, pair_value must be one of the following values:
pair_value Binpacking Algorithm
0 (Default) Distributes files across AMPs based on rowgroup boundaries (parallel distribution).
1 Distributes files across AMPs based on file size, balancing load across all AMPs.

Can cause skew if there are fewer files than AMPs or if files are of significantly different sizes.
2 Same as 0.

NONE

The previously specified query band is to be removed for the current session or transaction.

You cannot specify both the NONE option and the UPDATE option within the same request.

An APOSTROPHE-enclosed null string is equivalent to NONE.

UPDATE

Update the current query band name:value pairs as follows:
  • If the query band name matches a name in the current query band, then Vantage replaces the value.
  • If the query band name does not match a name in the current query band, then Vantage adds the name:value pair.

The only way to remove a name:value pair from a query band is to replace the entire query band.

If an update causes the new query band to exceed the maximum allowable length, Vantage returns an error to the requestor.

A name can be set to a zero-length string by specifying the following:

'pair_name;'

If you do not specify UPDATE, then the entire current query band is replaced by the new query band.

You cannot specify both the NONE option and the UPDATE option within the same request.

FOR SESSION

The set of name:value pairs applies to the current session.

You can set a session query band concurrently with an existing transaction query band.

Session query bands are stored in DBC.SessionTbl and are recovered after a system reset. The system populates additional columns in DBC.SessionTbl for a trusted session and uses the information in these session proxy columns to recover a trusted session after a system reset.

The query band for the session remains in effect until the current session ends or until you issue a:
  • SET QUERY_BAND FOR SESSION request to reset the query band.
  • SET QUERY_BAND NONE FOR SESSION request.
You can use SET QUERY_BAND FOR SESSION:
  • In a macro definition, as the single request in the macro.
  • With Teradata Parallel Transporter.
You cannot use SET QUERY_BAND FOR SESSION in:
  • Multiple-statement requests.
  • SQL procedure definitions.
VOLATILE
The system does not update the queryband column of DBC.SessionTbl and the FOR SESSION request is equivalent to a FOR TRANSACTION request.
Use caution if you mix SET QUERY_BAND requests. Suppose an application sets name:value pairs FOR SESSION that are saved in DBC.SessionTbl. If you then add query band name:value pairs using the SET QUERY_BAND VOLATILE option, the query band in the DBC.SessionTbl no longer matches the current session query band. The system restores the query band saved in DBC.SessionTbl on a restart.

FOR TRANSACTION

The query band set specified by the set of name:value pairs applies to the current transaction.

You can set a transaction query band concurrently with an existing session query band.

Only one transaction query band can be active at a time.

As each SET QUERY_BAND FOR TRANSACTION request in a transaction runs, its query band replaces the current transaction query band.

If you specify SET QUERY_BAND FOR TRANSACTION in a multiple-statement request, you must specify it as the first request. In a multiple-statement request, Vantage applies only one transaction query band to all statements in the request. Teradata Active Systems Management (TASM) classification occurs only once for all statements in a multiple-statement request.

If a proxy user is set in a SET QUERY_BAND statement in a multiple-statement request, Vantage applies the proxy user privileges to all statements in the request except when the transaction query band is specified by a:
  • ? parameter in a Java or ODBC program.
  • Macro.
  • SQL procedure.

For these cases only, Vantage does not apply the trusted session privileges to the statements in the multiple-statement request. Therefore, you can specify only one SET QUERY_BAND FOR TRANSACTION statement per multiple-statement request.

The query band for a transaction is not stored in DBC.SessionTbl.

Vantage discards the current transaction query band when the transaction ends, as determined by any of the following SQL transaction-terminating statements:
These statements are only supported on the Block File System on the primary cluster. They are not available for the Object File System.

Vantage does not restore the current transaction query band after a system restart.

SET QUERY_BAND FOR TRANSACTION is supported for both macros and SQL procedures.

SET QUERY_BAND FOR TRANSACTION is not supported for Teradata Parallel Transporter.

The following process shows a use case for query bands in the context of a SET QUERY_BAND FOR TRANSACTION request.

  1. A web service, implemented in Java, responds to an HTTP/SOAP (Hypertext Transfer Protocol/Simple Object Access Protocol) request.
  2. The web service uses JTA (Java Transaction API) to begin a user-managed transaction.
  3. The web service gets a JDBC connection from a connection pool managed by the application server.
  4. The web service uses JDBC (Java Database Connectivity) to submit the SQL request to establish the query band for the transaction.
  5. The web service invokes multiple Enterprise Java Beans (EJBs). For example, the first EJB inserts a row into table A, and the second EJB updates rows in table B. All calls to the EJBs participate in the same transaction, and therefore are identified with the same query band.
  6. The web service commits the transaction.

The query band value for the transaction is automatically cleared, so any subsequent SQL requests submitted on the pooled connection have a different query band value.