Dave's Adventures in Business Intelligence

Years ago we implemented query banding as a way to track queries being submitted from Business Objects. As I’ve mentioned we’re moving into Tableau as well now, and Tableau also supports query banding. When we started with Business Objects there were two choices: Transaction and Session. For no reason that I can remember, we opted for Transaction mode.

When we started to implement query banding for Tableau we discovered that it only worked for Session mode. That presented a problem.

Ultimately our code went from this:

some_table.user_name = GetQueryBandValue(1,'ClientUser')

to this

some_table.user_name = GetQueryBandValue(1,'ClientUser') OR some_table.user_name = GetQueryBandValue(2,'ClientUser')

The challenge was that the OR clause impacted query performance. We tried this:

some_table.user_name IN (GetQueryBandValue(1,'ClientUser'),GetQueryBandValue(2,'ClientUser'))

…but that also did not perform well. Interestingly enough we ended up with this:

some_table.user_name = GetQueryBandValue(1,'ClientUser') || GetQueryBandValue(2,'ClientUser')

Since only one of the two entries would ever have a value, using the concatenation operation || to put them both together and avoid the OR and the IN clause seemed to work well.

Now there is a better option. Based on this article, we are now implementing query banding using a zero option, as in:

some_table.user_name = GetQueryBandValue(0,'ClientUser')

There are different parameter options now that I feel certain were not available when we started or we would have been using them. According to the Teradata document linked above, the codes are:

• 0 = Return the first value found for the name=value pair. If the same name is found in both Transaction and Session then Transaction will be used
• 1 = Transaction mode
• 2 = Session mode
• 3 = Search the name=value pairs and find the item specified in the QBName input parameter

We now use mode 0 (zero) to check for both. I suspect 3 is used only when you need to override the default behavior of zero to check Transaction Mode first.

There was a post opened recently on BOB asking about Teradata Query Banding. DBAs are always interested in knowing as much as they can about who is running a query, and query banding (think of banding a bird to track its flight patterns) does just that. In later versions of XI we have the BEGIN_SQL parameter that can be used for this. In XI R2 and earlier versions the BEGIN_SQL parameter does not exist, and the END_SQL parameter really isn’t appropriate. Can we still use this feature?

It turns out that earlier versions are still able to use this feature by plugging the proper syntax into the ConnectInit parameter, which is part of the connection definition process. Continue reading “Using ConnectInit For Teradata Query Banding”