Set QueryBand as JDBC Connection Parameter

Database
Enthusiast

Set QueryBand as JDBC Connection Parameter

Hi all,

I would like to perform query banding at the connection level.  We are trying to track down a blocking issue, across a couple hundred, that are all hitting a single metadata table.  A few are not playing nice and we would like to use query banding to narrow it down.

I am currently trying the following but it doesn't seem to be working:

jdbc:teradata://tdprd/TMODE=TERA,CHARSET=ASCII,DATABASE=DB,QueryBand="ID=JobName;"

Tags (2)
4 REPLIES
Teradata Employee

Re: Set QueryBand as JDBC Connection Parameter

The Teradata JDBC Driver does not offer a "QueryBand" connection parameter.

All the Teradata JDBC Driver's connection parameters are documented here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BABJIHBJ

Information about using Query Banding with the Teradata JDBC Driver is documented here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGEGBBAA

You can specify a SET QUERY_BAND statement in the user's startup string, and then specify the Teradata JDBC Driver's connection parameter RUNSTARTUP=ON to execute the user's startup string when the JDBC connection is established. Information about the Teradata JDBC Driver's support for the user startup string is documented here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBHBDAB

Enthusiast

Re: Set QueryBand as JDBC Connection Parameter

Thanks Tom!  I didn't know about the RUNSTARTUP option and that is interesting but I don't think it will help me in this particular scenario.

The issue I am trying to solve is that we have a couple hundred jobs running under about five different users.  We are experiencing heavy blocking on one of our ETL metadata tables.  We have been able to narrow down to the trouble users but without assigning each program it's own user, it is difficult to narrow further.  This is why we are looking into query banding.

These programs all have their JDBC connection information set externally through environmental variables.  So I can change the JDBC URL but don't really have access to the queries to add transactional query banding.  I was hoping that I could set session level query banding in the JDBC URL but that does not appear to be possible.

Looks like I need to start cracking open these programs and adding transactional query banding internally.

Teradata Employee

Re: Set QueryBand as JDBC Connection Parameter

If you are using Teradata Database 14.0 or later, have you considered examining the Client Attributes information?

It is documented here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBGEIBI

At lot of information is saved about the client system and the client application, including the class name, method name, and source line (if available) of the current thread's last stack element at the time the connection is established.

Client Attributes are saved in the DBC.EventLog system table and can be queried from the DBC.LogOnOffV view. You can join DBQL with DBC.LogOnOffV on the session number to correlate your problematic queries with the Client Attributes of the application(s) that executed the queries.

Enthusiast

Re: Set QueryBand as JDBC Connection Parameter

This is exactly what I need.  I can tease enough information out of here to trace our blocking issues down to the process id's in our job launcher logs.  Thank you very much. 

You just allowed me to solve this issue so much more quickly.  I really appreciate it.