One of the biggest benefits of Query Banding is the ability to pass parameters into an SQL script.
Establishing an associative security table lookup between a userid and allowable company codes may allow easy access to the base data tables.
One of my clients recently had the need to allow specific individuals access to specific retailer’s sales info. Rather that creating multiple views with separate where statements for each retailer they wanted to create a security table. Their BI tool would only logon to Teradata through one user id. All the BI users access Teradata through this one id.
For instance; if you want to limit user access by Retailer, a table could be built like this:
Some third-part Business Intelligence (BI) tools login to Teradata with a single id whereas we need a separate id for each user.
We can have the BI tool issue the query banding instruction to set the user id to the suggested field name, ClientUser.
The Query band may be set at the FOR TRANSACTION or FOR SESSION level.
Use GetQueryBandValue(1,'ClientUser'); as tthe UDF for obtaining the Transaction level value or
Use GetQueryBandValue(2,'ClientUser'); for obtaining the Session level value.
To test query banding I wrote the following BTEQ code.
select date, time;
SET QUERY_BAND = 'ClientUser=i303444;' FOR SESSION ;
SET QUERY_BAND = 'ClientUser=i303555;' FOR SESSION ;
We can have a Sales_Daily_View:
Locking row for access
Select flda, fldb, fldc from
Sales_Daily.Retailer_ID = Security_Tbl.Retailer_ID
and Security_Tbl.User_ID = GetQueryBandValue(2,'ClientUser');
The Sales_Daily base table contains the proper two character Retailer_ID in each row.
The view will select the appropriate data rows from the Sales_Daily table based on the ClientUser ID that was passed to the system using Query banding.
As you mentioned the drawback: "getQueryBandValue is evaluated once per row, thus increasing CPU usage", later versions of TD (13 & 14) overome this drawback? if yes, can you please explain how it is achieved?
And, can you please explain more about your second statement:
"It's currently defined as NOT DETERMINISTIC, but can be changed to DETERMINISTIC without risk in TD13.
Dipdem.bteq will be fixed in a coming patch level."
Thanks in advance,