Jun 08 2010

Using ConnectInit For Teradata Query Banding

Categories: Database Techniques Dave Rathbun @ 11:27 am

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:

Screenshot of final parameter screen for Teradata connection in a Business Objects universe

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

11 Responses to “Using ConnectInit For Teradata Query Banding”

  1. Comment by Paulo

    Hey Dave, how can I do this with Oracle?

  2. Comment by Dave Rathbun

    It won’t do any good to set something like this up unless Oracle is going to receive the information. That would be the first step… finding out what sort of information Oracle will accept as part of the connection initialization process.

  3. Comment by Rob Paller

    Dave,

    Take a look at this message on the TeradataForum website: Query Banding and BO Usage

    There are a few more things you can do with BO and Query Banding that will make your analysis even easier and more useful.

  4. Comment by Dave Rathbun

    Hi, Rob, your link did not come through properly. The text of your comment is there but the URL is missing. If you can post just the URL I will update your comment with the proper syntax, thanks.

  5. Comment by Suresh Srinivasan

    Hi Dave,

    I have something related to Oracle for the ConnectInit, even though i haven’t seen much use of the same, i’ll try finding out and send across to you and you can see if that any way is usefull.

    Your blogs are really great.

    Thxs,
    Suresh

  6. Comment by Rishabh

    Hi Dave,

    I am trying to get the report name in to the query band syntax but it seems to not work any suggestions that you can give:

    SET QUERY_BAND=’ClientUser=’@Variable(’BOUSER’)';
    Document=’@Variable(’DOCNAME’)';’ for transaction;

    Thanks,
    Rishabh

  7. Comment by Dave Rathbun

    Based on our experiments (and confirmation from SAP) the document name is “out of scope” during the query initialization phase and therefore is not passed. The DOCNAME value is not set until some time later, which is unfortunate.

  8. Comment by amjad

    We have implemented the query band for teradata universe. Teradata tables storing few information from the settings. Before installing SP5 it was working fine later found out that some information is been stored in tables and some are blank. Did any one face the issues.

  9. Comment by Kumar Ankur

    Hi Dave,

    How does it work in Information Design Tool 4.1?
    Can you make an insight into that?

    Ankur

  10. Comment by Ganesh

    Dave ,
    can you please explain this in IDT as we have that BEGIN_SQL option now. Please.

  11. Comment by xiomara zamora c

    What else is required additional to define the parameter of begin_sql SET QUERY_BAND = ‘ClientUser = @ variable (’ BOUSER ‘);’ FOR TRANSACTION;
    in information design tools?

    I remain attentive to your answer. Thank you

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

 []                 

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.