Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Database
Enthusiast

Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Example :

I try to add "SET QUERY_BAND = 'TEST = 1.0;' UPDATE FOR SESSION;"

Error: [Teradata Database] [TeraJDBC 13.10.00.01] [Error 5526] [SQLState HY000] SPL1027:E(L45), Missing/Invalid SQL statement'E(5568):SQL statement is not supported within a stored procedure.'.

if used : "SET QUERY_BAND = 'TEST = 1.0;' UPDATE FOR TRANSACTION;"  - it works

Why not use "SET QUERY_BAND = 'TEST = 1.0;' UPDATE FOR SESSION;" ?

Help me , please.

7 REPLIES
Senior Apprentice

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

As the error indicates, you can't set a Queryband on session level in SPs, it's simply not supported. If the question is "why it's not supported?", i don't know, there might be an obvious reason.

So set the Queryband before you call the SP or do it FOR TRANSACTION. If you need it for multiple statements you might do:

BEGIN REQUEST; --multi-statement request
SET QUERY_BAND ... FOR TRANSACTION;
more SQL;
END REQUEST;

or

BT; -- Teradata mode session
SET QUERY_BAND ... FOR TRANSACTION;
more SQL;
ET;
Enthusiast

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Thank you, Dnoeth!

Teradata Employee

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

I also have a requirement to set query_band dynamically inside a stored procedure and setting it for each TRANSACTION is not a very desirable option.

I have managed to get this to work for SESSION by settnig it inside an External Java Stored Procedure. I can call the JXSP from my stored procedure and it seems to work fine.

Not sure if I am breaking some fundamental principle of query bands but it works.

cheers,

Paul 

BPP
Enthusiast

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Hello Pauldancer, are you willing to share your custom proc with us?  I have a need for this now and I got two options in front me.  One is what you have already implemented and other is wait for the software company to add that feature within the tool, I doubt it is going to happen any time soon.

If you are okay in sharing it here is my email.  Bhanu1405@yahoo.com.  Thanks much in advance. 

Thanks

Bhanu P

Teradata Employee

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Just a small suggestion. Why don't you use TRANSACTION-level Query Band on the CALL for the procedure? It will assign automatically the same query band to all statements within the SP.

Here's a sample code:-

BEGIN TRANSACTION;
SET QUERY_BAND = 'Source=SQLAssistant;report=TestReport;' FOR TRANSACTION;
CALL TestDB.AA_DBQL_StoredProcedure();
END TRANSACTION;

Testing the above in the DBC.DBQLogTbl DBQL querry logging table results into the desired query-band assigned to all statements within the stored procedure.

Hope it works out for you guys.

Teradata Employee

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Since writing my previous post on this I have been made aware that whilst the Teradata manual does xplicitly state that setting a Query Band for a SESSION is not supported from within a stored procedure, it is possible to use dynamic SQL to achieve this.

Here's a small excerpt to illustrate:

SET vQBStmt = 'SET QUERY_BAND='''
||i_QBString||''''
||CASE WHEN i_QBUpdate = 'Y' THEN 'UPDATE' ELSE '' END
||' FOR SESSION'
||';';

/* Set the Query Band */
CALL DBC.SYSEXECSQL(vQBStmt);

Again, I don't know why this SESSION Query band in an SP is a limitation or if circumventing this limitation with dynamic SQL is violating some basic principle behind how query bands work.


Enthusiast

Re: Query band in procedure. SET QUERY_BAND ';' UPDATE FOR SESSION;"

Hi Guys

I have the same requirement but when i use following code,

BEGIN TRANSACTION;

SET QUERY_BAND = 'Source=SQLAssistant;report=TestReport;' FOR TRANSACTION;

CALL TestDB.AA_DBQL_StoredProcedure();

END TRANSACTION;


My procedure involves DDLs and i get following error

CAll Failed: PRocNAME :only ET or Null Statement is allowed after DDL statement

Please help how do we set QueryBand in such scenario?

My proc definition,

Replace procedure My_DB.PRocName ()

Begin

Create Multiset Volatile Table VT_Test as

(sel Tablename, DatabaseName from DBC.tables where Tablekind = 'T'

)with Data

PRimary index (TableName);

Create Multiset Volatile Table VT_Test1 as

(sel objectTableName as Tablename, ObjectDatabaseName as DatabaseName from DBC.DBQLObjTbl where ObjectType = 'T'

)with Data

PRimary index (TableName);

insert into VT_Test1 Values ('Sanket','Sanket_DB');

END;