Dave's Adventures in Business Intelligence

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.

Building A Connection

There are several standard steps required for building a connection. The most obvious are the user name and password so those are set up on the very first screen. There is another screen that allows me to set up various connection settings like a time out value, the array fetch size, and how long the connection should remain active. Many of these screens are database specific, meaning a SQL Server connection screen may have different parameters than an Oracle connection screen.

With Teradata the final page of the connection definition process looks like this:

There are two parameters available here. The second one named “ConnectInit” is what I am interested in for this blog post. From the name the expectation is that the parameter set here will have something to do with the connection initialization process, and that is exactly where I want to set up my query banding information.

Query Band Options

There are a number of predefined values that can be passed through this process, and I have listed a few of the interesting ones below. (There is a link at the end of the blog post that shows a Teradata article with the full list.) Before I list them all here’s what the parameter string looks like in my case:

SET QUERY_BAND = 'ApplicationName=YourAppHere; ClientUser=@variable('BOUSER');' FOR TRANSACTION;

ApplicationName
The first variable+value pair is ApplicationName and I have entered YourAppHere as the value. Obviously I would enter something more descriptive for a real connection. What am I planning to use this for? We have several major applications using Teradata, and in many cases the users are unique to an application. In some cases (especially for developers) the same person may connect to more than one application. By using this parameter for the query banding process I can tag a query as coming from a specific application.

Our applications get different priority on the server at various times during the day, so tagging the query application source would help in that effort.

ClientUser
This one is probably the most obvious. By passing the user name through as part of the query banding process the DBA team knows exactly who to call for a runaway query. By filling the username using the @variable() function each query will be banded (tagged) with the Business Objects username.

Quotes

When I first put this into our development environment I felt weird about the quotes. If you look carefully at the string posted above the quotes are not really nested appropriately. There are single quotes inside of single qoutes. Normally I would expect that to cause problems. But remember that the special “@” functions in Business Objects are evaluated before anything else happens, so by the time the string gets sent to the database the internal quotes are gone.

Transaction Versus Session

There are two different ending tags that appear in the syntax: For Transaction or For Session. Here’s a quote from the article that discusses the difference between the two.

The session query band is stored in the session table and applied to each request. It remains set for the duration of the session or until the query band is replaced by another. In case of a system reset, the query band is recovered.

A query band can also be applied to all of the requests within a transaction. When the application requests a connection from the pool to perform a service for a client user—for instance, saving data to the database—it sets the transaction query band with attributes specific to that service request, as follows:

SET QUERY_BAND = ‘ClientUser=dg120444; Group=Sales; JobID=998;’ FOR TRANSACTION;

When the transaction completes, the transaction query band is automatically discarded. Therefore, no cleanup is required before reusing the connection for another service request.

I reviewed the article with our primary DBA and he selected Transaction for our initial testing.

Setting It Up

To set this up, all I have to do is copy the text shown above into the ConnectInit field and click the “Set” button. Later on if I decide to change something I can redo the same steps; the new information will replace the old configuration. If I need to remove it, as you would expect the Remove button takes care of that. It doesn’t remove the ConnectInit parameter altogether (you might want to set it up again later). It sets the value to a null string so nothing is passed.

Since I am passing the application name, I have to set up application-specific connections.

Conclusion

So far it’s doing what we expected. We really have not done a lot to make use of the extra information yet as we were just trying to make sure that if we wanted to pass the information, we could. Since we’re currently on XI R2 and don’t have the BEGIN_SQL option, the ConnectInit parameter for the connection was the only choice we had.

And so far it’s working exactly as we need it to.

Related Links

• Original article on Teradata Forum: Keeping TrackQuery banding provides detailed workflow information
• Original Topic on BOB: Banding for Queries