Query banding within a single transaction

Connectivity
Enthusiast

Query banding within a single transaction

I tried to issue the query banding statement along with my SQL query in a single transaction, like:
SET QUERY_BAND='AppName=B;'FOR Session;SELECT ....

This ran successfully in the Teradata SQL Assistant but not in Teradata Administrator as well as not in my application which uses JDBC connection via Teradata's JDBC driver.

The error I'm getting is:
Only an ET or null statement is legal after a DDL Statement
2 REPLIES
Teradata Employee

Re: Query banding within a single transaction

SET QUERY_BAND ... FOR SESSION is a DDL statement, and must be committed afterward.

With the Teradata JDBC Driver, either use auto-commit ON, or call the Connection.commit method after executing the SET QUERY_BAND ... FOR SESSION command.

Alternatively, you can use the SET QUERY_BAND ... FOR TRANSACTION command. It is categorized as a DML statement, and can be used in a multi-statement request, such as:

SET QUERY_BAND='AppName=B;' FOR TRANSACTION ; SELECT ...

Please review the Teradata JDBC Driver sample programs that demonstrate the use of SET QUERY_BAND.

http://www.teradata.com/DownloadCenter/Topic9361-99-1.aspx

As for why Teradata SQL Assistant permits the use of SET QUERY_BAND ... FOR SESSION within a multi-statement request, perhaps either the Teradata ODBC Driver or Teradata SQL Assistant is splitting up the request and submitting the commands individually.

In contrast, the Teradata JDBC Driver does not alter the SQL request text submitted by the application.
Enthusiast

Re: Query banding within a single transaction

Thanks a lot Tom, I highly appreciate your descriptive explanation and looking into the matter.

I, on the other hand cannot call the transaction commit right after executing the query band SQL because currently I could only tweak my externally exposed SQL and connection URL. Therefore I tried auto-commit but that also didn't work because currently my only bet to turn on auto-commit was to change the jdbc connection URL to TMODE=ANSI and pray that my query wasn't already within a BT, but unfortunately it was and I got this:
Only a COMMIT WORK or null statement is legal after a DDL Statement.

I don't think I have any other way out of this other than making code changes. If you think otherwise, please let me know.

Much thanks