How to setting QueryBand

Database
Teradata Employee

How to setting QueryBand

Please advice me that how to set up the query band when Connection Pool is done and transaction cannot explicitly BeginTransaction, EndTransaction .

Wish to set it for each SQL,how can we do the process?

 

Thanks & Best regards

David

13 REPLIES
Senior Apprentice

Re: How to setting QueryBand

Hi David,

 

I think you have two options:

 

Use the 'for transaction' and run each SQL as a multi-statement-request (MSR):

set query_band = 'qbname=qbvalue;'; for transaction;your-sql-here;

Use the 'for session' option:

set query_band = 'qbname=qbvalue;' for session;

your-sql-here;

This second option will be fine providing no other session uses that connection between the 'set query_band' and 'your sql'. - but I don't think it can.

- NOTE: if you're on TD14.10 or higher then you can use the 'VOLATILE' option which should help make this run a little bit more efficiently.

 

Cheers,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: How to setting QueryBand

There is another option if you want to truly automate it.

This can be accomplished by using startup parameters.

And yes, this will be either a call to a stored procedure or a macro.

 

Replace procedure UpdQryBand ()
BEGIN
DECLARE UpdQryBandStr VARCHAR(200);
SET UpdQryBandStr = 'SET QUERY_BAND = ' || '''' || 'WebSessionPool=' || session || '-' || user || ';' || '''' || ' FOR SESSION;';
CALL DBC.SysExecSQL(UpdQryBandStr );
END;

 

Then modify the user in question's startup parameter as 

 

MODIFY USER <someWebUser> AS STARTUP = 'call UpdQryBand ();';

 

Bear in mind, there will be additional call which means there will be additional CPU and response time penalties. 

You will have to account for any TASM throttles that may impact this. 

The user in question should have execute procedure on this newly created macro. 

You can possibly pass some parameters to this procedure to make it very personal.

 

Personally, while this feature is good but it comes at cost as it is a SQL that DBS has to process.

 

I haven't tested this code but I had used something like this in past when Dinosaurs weren't extict.  

HTH!

 

Best,

Vinay Bagare

Teradata Employee

Re: How to setting QueryBand

Hello dear Dave,

Hello dear Vinay,

thanks for your response for my last question.

Now i have the following wish to confirm with both of you once again,

***************************

Is there anything need to pay attention to when setting the query band?

The performance of Customer's environment is tight, please tell me the Impact on performance such as CPU / memory  etc.

****************************

thanks in advance

best regards

David

Teradata Employee

Re: How to setting QueryBand

Hello dear Viany,

Hello dear Dave,

thanks for your response for my last question.

Now i have the following wish to confirm with both of you once again,

*****************************

Is there anything need to pay attention to when setting the query band?

The performance of Customer's environment is tight, please tell me the Impact on performance such as CPU / memory  etc.

*****************************

thanks in advance

best regards

David

Senior Apprentice

Re: How to setting QueryBand

Hi David,

 

If you avoid the 'reserved' QueryBand names then there shouldn't be any impact at all. Non reserved Queryband values are basically just a piece of text that gets recorded in the query log tables.

 

Avoid issuing the 'set query_band ... for session;' command before every request. That is usually a waste of time and will cause an update of a system table. If you are on TD14.10 and must use this logic then use the 'volatile' option (e.g. 'set query_band ... for session volatile;').

 

Even with the above, check that any queryband names being used are not the subject of any TASM rules (filters, throttles etc.).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: How to setting QueryBand

From performance perspective, while SET QUERY BAND are feather weight but they do something (probably use an AWT).

If you have several thousands of these and you have TASM setup (throttles), you will probably see some impact. 

 

Other than that, nothing much bad to say about these featherly light objects.

 

 Dave put this more eloquently than I did though :-).

 

 

Teradata Employee

Re: How to setting QueryBand

Hello Mr.Dave,

Adding QueryBand to customer developed application and executing SQL,

On dbc.dbqlogtbl, an event occurred in which the QueryBand column was NULL.

would you please teach me about the confirmation points and  whether have necessary for JDBC settings etc.

<Environment>

  • JDBC version of application: 13.00
  • Middleware: InterStage
  • Destination Teradata version: 13.10

 

<Execution SQL>

Set query_band = 'test = 16;' for transaction; select col1 from TABLE1 where col1 =?;

Execute the above 2 SQL as one transaction.

Parameter query for the second SQL.

 

<Status of dbc.dbqlogtbl>

  • One record is created as a log record, and execution SQL is stored in querytext column.
  • Errorcode column is 0 (normal termination)
  • QueryBand column is NULL

 

<Others>

  • When executed with bteq, it terminated normally, and the set value (= T> test = 16;) was stored in the QueryBand column of dbc.dbqlogtbl.

* Parameter query part is executed by replacing " ? " With  " '' " .

  • QueryBand column was NULL even when executing QueryBand SQL only from application.                                                                              
Enthusiast

Re: How to setting QueryBand

I am inclined to say that you need to upgrade JDBC drivers.

If you used the solution that I mentioned (startup parameter), you need to use RUNSTARTUP=ON in your JDBC URL.

Teradata Employee

Re: How to setting QueryBand

Hello dear Viany

You meaning this solution as following :

 

Replace procedure UpdQryBand ()
BEGIN
DECLARE UpdQryBandStr VARCHAR(200);
SET UpdQryBandStr = 'SET QUERY_BAND = ' || '''' || 'WebSessionPool=' || session || '-' || user || ';' || '''' || ' FOR SESSION;';
CALL DBC.SysExecSQL(UpdQryBandStr );
END;

 

Then modify the user in question's startup parameter as 

 

MODIFY USER <someWebUser> AS STARTUP = 'call UpdQryBand ();';

 

thanks so much

best regards

david