Query band in Macros

Database
Enthusiast

Query band in Macros

I have a project specific requirement where I need to set a query band dynamically based on input parameters. I'm trying to achieve this via macros(we're on version 13.10). But the issue is that if I'm passing parameter to a macro that has only the "set query band" statement, I can only do it if the query is "for transaction" and not "for session".

Why do Teradata macro's allow both session and transaction query bands when they're hardcoded but only transaction query bands when parameterized?

Eg, the below code works fine(where I hardcode the query band value inside the macro):

create macro sysdba.mac1( QBIN varchar(60))

as

(

SET QUERY_BAND = 'org=Finance;report=Fin1234;' FOR session;

)

 exec sysdba.mac1('message');

 select getqueryband(); /*runs successfully and gives the output: "=S> org=Finance;report=Fin1234;"  */

But this statement fails:

create macro sysdba.mac1( QBIN varchar(60))

as

(

SET QUERY_BAND = :QBIN FOR session;

 )

with error: "A syntax error was found in the QUERY_BAND"

whereas this statement succeeds:

create macro sysdba.mac1( QBIN varchar(60))

as

(

SET QUERY_BAND = :QBIN FOR TRANSACTION;

 )

Regards,

Suhail

Tags (2)
4 REPLIES
Teradata Employee

Re: Query band in Macros

>>> allow both session and transaction query bands when they're hardcoded but only transaction query bands when parameterized?

That behavior is not specific to macros.

The SET QUERY_BAND ... FOR SESSION command is considered a DDL command, and like all DDL commands, it cannot be parameterized.

In contrast, the SET QUERY_BAND ... FOR TRANSACTION command is considered a DML command, and it can be parameterized.

Enthusiast

Re: Query band in Macros

Do you know why one is considered a DDL command and other a DML command?

I know for a fact that the session query band updates the DBC.SessionTbl and the transaction query band does not.

Is that the basis of deciding whether a command is DDL or DML?

-Suhail

Teradata Employee

Re: Query band in Macros

Generally speaking, a command must be classified as a DDL command if it changes any database objects. This relates to how SQL requests are parsed. A DDL command must be the last (or only) command in a transaction so that the parser doesn't have to consider pending database object changes when parsing a SQL request.

Regarding the SET QUERY_BAND ... FOR SESSION command -- While that command doesn't change any database objects, it does require an update to a Data Dictionary table, so that is certainly part of the reason why it is classified as a DDL command.

Enthusiast

Re: Query band in Macros

ok Thanks. Do you think it is reasonable to put in a ticket to Teradata regarding this?

So that in some future release they start considering the whole SET QUERY BAND command either as a DML command or a DDL command.

-Suhail