15.10 - FOR TRANSACTION - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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, Teradata Database 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, Teradata Database 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, Teradata Database 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.

Teradata Database 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 SQL Data Manipulation Language, B035-1146.

Teradata Database 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.