FOR TRANSACTION - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 sequential SET QUERY_BAND = … FOR TRANSACTION request within a transaction is executed, its query band replaces the current transaction query band.

SET QUERY_BAND … FOR TRANSACTION must be the first request specified if it is stipulated within a multistatement request. In a multistatement 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 multistatement request.

If a proxy user is set in a SET QUERY_BAND statement in a multistatement 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 multistatement request. Therefore, you can specify only one SET QUERY_BAND= … FOR TRANSACTION statement per multistatement 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:
  • ABORT
  • COMMIT
  • ROLLBACK

For more information about these statements, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

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 illustrates 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 obtains 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 EJBs Enterprise Java Beans) to do some work. For example, the first EJB inserts a row into table A, and the second EJB updates some 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.