The Friday Night Project #10 – Turning on Query Banding

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.
Teradata Employee

The Friday Night Project #10 – Turning on Query Banding

Last week we concluded by challenging you to consider how a Connection Pool contributes to the performance of Web and Web service applications. Further we suggested that this week we would expand on this thought by exploring what you can do, during the development of an Active Integration application, to achieve improved performance through collaborating with the Database on Workload Management.

Until now nothing that we have discussed has been dramatically different from any other Spring Framework / DAO / Web services / JEE / POJO tutorial, however, there is method to this madness in that it was necessary to introduce the Teradata Masters out there to some new Java concepts while leading the Java Masters into the Teradata fold. While this has taken a reasonable number of weeks to accomplish it should now be safe for the entire readership to enter the world of Teradata Workload Management and Query Banding together.

Workload Management

If we think back to Week 2 of The Friday Night Project where we discussed the need to create a Solid Architecture, one of the requirements presented was the need to provide for Workload Management as a means to balance the softer parameters that go with this new Active Integration environment, namely Query Complexity versus Query Performance.

Workload Management

To recap the Expert wants a responsive application while they are in Online Mode but understands that when they hit the big GO button that Query Complexity is going to go up as will response times. They may not even wait for a response but expect an email when the job is done.

As we walk along this graph Tactical user applications might periodically issue complex monitoring queries with longer response times, which may even run unattended in the background. However, when something goes wrong these Tactical Users will expect to receive higher prioritization as they drill down to identify and solve the Business Problem at hand.

In the world of the Opportunists and Devices there is no time like the present, and sub second response times to simple queries are required.

For An Active Data Warehouse (ADW) to manage all of these different workloads it needs to know something about what is going with the Applications that are issuing all these queries. Remember not all Queries are created equal in this new ADW world.

From The Surfers to Out on the Wire

In order to try and illustrate the mechanics of this new Active Integration or Active Data Warehouse world we start by depicting “The Surfer” to represent all of the Human Driven Web or Portlet Applications that run at the speed of the human driving them. While this means that the human driving a given session with the application is typically much slower than the underlying application / ADW (i.e. individual users provide initial Input, before Reading and Interpreting the response before possibly repeating the process), it does require a fast response upon each input request. So individual response times are expected to be short (Tactical) although each of these, from a given user, may be separated by seconds or even minutes, it is only where there are many current users (Surfers) that the environment (Wave) may get busy or crowded. The other aspect to consider in this is the underlying Wave itself, which may represent an underlying force that can drive a new load upon the Active Application and the underlying Active Data Warehouse. We will see this in future episodes of The Friday Night Project where we start to think about Web Portlet applications with Asynchronous behavior that is not directly related to normal human input.

Attribution: http://www.flickr.com/photos/travoc/2100480937/sizes/m/in/photostream/

The second of our Illustrations takes us “Out on the Wire” in order to represent the concept of Web services where we have a Wake Boarder, Inverted, being pulled through the air by an un-remitting engine over which they have little control. Simply being pulled along (by single AMP lookups) is hard work, while executing and landing a complex jump (involving joins and calculations) within an acceptable period of time, while still handling the normal operation of the Enterprise Data Warehouse is clearly going to present a major challenge.

Courtesy : Geoff Howell Wakesports.com, San Diego

All this Sporting Activity looks like fun, however, how does it relate to the challenge of Active Integration? The real point of the pictures above is to impart some sense of the context and urgency behind these scenarios and equate this to our problem domain.

The core consideration for an Active Integration application is to somehow communicate an appropriate sense of the context that surrounds each query through to the Active Data Warehouse (ADW). Once the Query is within the ADW, mechanisms such as Workload Management (TASM) can, if provided with appropriate Context information, determine the Urgency or Priority of a given query and prioritize or schedule how this is processed in order to provide for a required Service Level Agreement (SLA).

Active Integration Requirements

This discussion generates two requirements for the Active Integration Architecture. The first is how do we determine the Context around a given Application and in particular individual user sessions with that application? The second is given that we can determine what the application context is how do we communicate this to the ADW?

We will address these questions in reverse order starting with the idea of Query Banding as a means to communicate application context to the ADW before looking at how to establish this context information as part of the application infrastructure.

Query Banding.

TD12+ Image

Query Banding is a feature, introduced as part of Teradata 12.0, which allows any application (Active Integration or otherwise) to associate context information with each query it issues to the ADW. Simply put Query Band information is provided through the use of extra SQL statements that allow an Application to pass a series of key/value pairs into the ADW that can be used for purposes such as Debugging, Logging and Workload Management.

FOR SESSION

Query Bands can be set for an entire Session using the following syntax:

SET QUERY_BAND = 'ApplicationName=TZA-InsuranceService; Version=01.00.00.00;' FOR Session;

This context information would be available to any queries issued over this Session until the Session was closed or the Query Band information was cleared using the following syntax:

SET QUERY_BAND = NONE FOR Session;

FOR TRANSACTION

Query Bands can be set for an individual Transaction using the following syntax:

SET QUERY_BAND = ‘ClientUser=TZA10; Group=Experts; Source=MainScreen; Action=AdjustZip;' FOR TRANSACTION;

This context information would be available throughout a given transaction and would be automatically cleared when the transaction completed (whether through being committed or rolled back).

DBQL Demo

All this What / What theory is fine but I’m sure, like Toto, you want to see what this looks like in practice. The easiest way to demonstrate Query Bands is to use the Database Query Log (DBQL) capability. When enabled DBQL provides a means to record every query that is processed by the database and as of Teradata 12.0 this includes information about any Query Band information that was associated with each query.

Return to the TZA-Database project by starting Eclipse and opening up the FridayNightProject workspace.

Workspace Launcher

Create a TERA Mode Connection Profile

As some of the queries necessary to manage DBQL require a TERA Mode Connection (as opposed to an ANSI Mode connection) we need to establish this within the Data Source Explorer view so that we can connect in TERA Mode and issue the queries through this connection (Note this only applies to the management of DBQL, the queries associated with Query Banding are SET Statements, which will operate over an ANSI Mode connection, although they only have relevance to a Teradata Database).

Within the Data Source Explorer view Right Click on the "Databases" Folder and select New.. in order to create a new Connection profile.

Data Source Explorer

In the "Connection Profile" dialog create a New Connection Profile by selecting Teradata Database. Give the Connection Profile a meaningful name such as Local-12-TERA for a local TERA Mode TD 12 Express Installation or Remote-12-TERA for a remote machine (optionally you can provide a description of the profile) then select Next >.

New TERA Connection Profile

In the Specify a Driver and Connection Details dialog select the Teradata JDBC from the drop down list.

Provide the name of the Server, as represented in you local hosts file (in C:\WINDOWS\System32\drivers\etc\hosts we map LOCALHOST to Local-12 Local-12cop1 so the Server name is Local-12). User name and password, at this time are the Teradata defaults of dbc/dbc (as we discussed previously you have since changed these to something else).

By default the connection mode is set to TMODE=ANSI (Teradata Advocated Approach), however to manage DBQL we need a TERA Mode connection. To achieve this set the Optional Properties to TMODE=TERA and select ‘Add’.

New TERA Connection Profile - properties

Remove the TMODE=ANSI property by selecting that line in the Optional properties filed and selecting Remove. Select Finish to create the Local-12-TERA Mode Connection Profile.

Within the Data Source Explorer view Right Click on the Local-12-TERA connection profile and select Connect. Once the connection is made, open up the tree to reveal the contents of the Local-12-TERA Instance.

ManageDBQL.sql

Within the Project Explorer view browse to TZA-Database/src/sql and create a new SQL file called ManageDBQL.sql as follows.

  • Right Click on src/sql and select New -> File -> Sql
  • Set the file Name to ManageDBQL.sql.

NEW SQL File

  • Click Finish and a template SQL file will open within the Eclipse editor.

The ManageDBQL.sql file will be built up iteratively (using copy and paste) as we explorer different aspects of Query Banding over the following sections.

We start by looking at how a FOR SESSION Query Band appears within DBQL. Initialize the ManageDBQL.sql file with the following SQL:

--/**
-- * The contents of this file are Teradata Public Content and have been released to the Public Domain.
-- * Please see the license.txt file in the top level directory for more information.
-- *
-- * @author Mike.Coutts@Teradata.com
-- */
--
-- Clear Existing DBQL and BEGIN QUERY LOGGING;
-- Select these Queries, Right Click and Ececute as Individual Statements
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING WITH SQL, OBJECTS ON ALL;
--
-- Set Query Band FOR SESSION, Run a Query and Check for the Query Bands
--
SET QUERY_BAND = 'ApplicationName=TZA-InsuranceService;' FOR SESSION;
SELECT * FROM TZA_DB.ZipCodeRiskFactors WHERE '90000' BETWEEN StartZipRange AND EndZipRange;
SET QUERY_BAND = NONE FOR SESSION;
--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Query Band For Session

In the SQL Results view you should see that each individual statement executed successfully:

Query Band - Logging Execution

If we drill into the final operation “SELECT QueryBand..” and open up the Result1 View we can see each statement that was executed and can see the Session Level Query Band (marked with =S>) associated with each Query. On line 3 it is clear that a Session Level Query Band was in place while the “SELECT * FROM TZA_DB.ZipCodeRiskFactors..” query was being executed.

Query Band For Session - Results

Now we look at how a FOR TRANSACTION Query Band appears within DBQL. Add the following SQL to ManageDBQL.sql, Select the Queries, Right Click on the selection and Execute them as Individual Statements, as before.

--
-- Clear Existing DBQL and BEGIN QUERY LOGGING;
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING ON ALL;
--
-- Set Query Band FOR TRANSACTION, Run a Query and Check for the Query Bands
--
BT;
SET QUERY_BAND = 'Version=01.00.00.00;' FOR TRANSACTION;
SELECT * FROM TZA_DB.ZipCodeRiskFactors WHERE '90000' BETWEEN StartZipRange AND EndZipRange;
ET;
--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View we can see each statement that was executed and can see the Transaction Level Query Band (marked with =T>) associated with each Query. On line 5 it is clear that a Transaction Level Query Band was in place while the “SELECT * FROM TZA_DB.ZipCodeRiskFactors..” query was being executed.

Query Band for Transaction - Results

Finally we look at how FOR SESSION and FOR TRANSACTION Query Band will appear within DBQL. Add the following SQL to ManageDBQL.sql, Select the Queries, Right Click on the selection and Execute them as Individual Statements, as before.

--
-- Clear Existing DBQL and BEGIN QUERY LOGGING;
-- Select these Queries, Right Click and Execute as Individual Statements
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING WITH SQL, OBJECTS ON ALL;
--
-- Set Query Band FOR SESSION, And FOR TRANSACTION Run a Query and Check for the Query Bands
--
SET QUERY_BAND = 'ApplicationName=TZA-InsuranceService;' FOR SESSION;
BT;
SET QUERY_BAND = 'Version=01.00.00.00;' FOR TRANSACTION;
SELECT * FROM TZA_DB.ZipCodeRiskFactors WHERE '90000' BETWEEN StartZipRange AND EndZipRange;
ET;
SELECT * FROM TZA_DB.ZipCodeRiskFactors WHERE '90000' BETWEEN StartZipRange AND EndZipRange;
SET QUERY_BAND = NONE FOR SESSION;
--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View we can see how each statement was executed.

Query Band for Session and Transaction - Results

The initial statements (lines 2 and 3) have a Session Level Query Band (marked with =S>) associated with each Query. During the period of the Transaction (lines 4 through 6) we see that a Transaction Level Query Band (marked with =T>) is in place when we execute the “SELECT * FROM TZA_DB.ZipCodeRiskFactors..” (at line 6). Once the Transaction is completed we notice that we return to just the Session Level Query Band when executing the second “SELECT * FROM TZA_DB.ZipCodeRiskFactors..” query at line 7, outside the Transaction but still within the Session.

JDBC Comments

Query Banding is a Teradata 12.0 on upwards feature and so does not work with older versions of Teradata (V2R6.2 etc). It is possible to provide some level of Application Context within DBQL through the use of JDBC Comments. To illustrate this Add the following SQL to ManageDBQL.sql:

--
--Clear Existing DBQL and BEGIN QUERY LOGGING;
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING ON ALL;
--
-- For PRE TD12 Instances a JDBC Comment can be added to the SQL
--
SELECT * FROM TZA_DB.ZipCodeRiskFactors WHERE '90000' BETWEEN StartZipRange AND EndZipRange -- JDBC Comment
--
;
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

The Execute as Individual Statements command does not work exactly as we would expect when trying to apply JDBC Comments, therefore for this exercise Select the first pair of queries, Right Click on the selection and Execute them as Individual Statements (to prepare for Query Logging). Then select the “SELECT QueryBand..” query including the JDBC Comment text, Right Click and select Execute Selected Text. In the Status tab of the SQL Results view you can see that the Query and the Comment were applied.

Set JDBC Comment

Lock down the Query Logging by selecting the final pair of queries, Right Click on the selection and use Execute as Individual Statements.

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View we can see how each statement was executed.

Query Band Comments in DBQL

The JDBC Comment appears within the Query Text and could be used as part of a manual debugging process. However the QueryBand value is NULL (or in a Pre 12.0 Database simply will not exist) and so you get now of the Query Band capabilities such as Workload Management, Logging, Debugging, Auditing etc.

Thread Local Context and Teradata Access

While Query Banding provides us with a mechanism for communicating Application Context through to the database the problem, that Application Developers face, is that most of that Application Context is actually created or first available up at the Interface Layer or Presentation Veneer as illustrated below:

Maintaining Context

So the challenge is how do we capture this information and pass it through to the Session Manager without putting the responsibility for its management, through the application layers, on individual developers (who could easily be different people for the different layers).

As this is considered to be “Infrastructure” it is best to have a single approach to this activity that can be Leveraged and Reused across many projects. To that end Teradata has provided two Public Domain packages (td-commons-context and td-commons-access) that have been used in the previous weeks. A full exploration of these is beyond our scope here, but we will take a brief look at how we use these packages in order to provide for Context Capture and Query Banding.

Maintain the Context from UI to DAO

So the first part of our challenge is how to maintain Application Context from the User Interface / Presentation Veneer through to the Data Access Layer. In the Java world we use the ThreadLocal concept [http://java.sun.com/j2se/1.4.2/docs/api/java/lang/ThreadLocal.html], which provides for Thread Local Variables that can be setup at the beginning of a Thread of Execution and retrieved later within that Thread without needing to maintain that object directly. This allows us to associate Application Context information with the Thread of Execution hence we call the class Thread Local Context (TLC). TLC uses a ThreadLocal Map of two Strings and directly supports the Teradata Reserved Query Band names Orange Book [541-0006939] with various helper methods like setApplicationName(), setVersion(), setSource(), setAction() etc for initializing the Application Context.

If you look back at the TZA-InsuranceService project in the getQuote method of the PropertyInsuranceSoapBindingImpl.java class we established some pieces of the TLC based upon the Web service method parameters.

        // At first point of Application Entry (Re)Initialize ThreadLocalContext
ThreadLocalContext.setClientUser(quoteRequest.getClient().getUsername());
ThreadLocalContext.setGroup(quoteRequest.getClient().getOrgCode());
ThreadLocalContext.setAction((new Exception().getStackTrace()[0].getMethodName()));

Applying the Context to the Query Band

The second part of the challenge is how we apply the Context information, maintained within TLC to the Database as we issue Queries to it. TLC provides two further helper methods getQueryBandForTransaction() and getQueryBandForSession() that allows us to retrieve, pre-formatted Query Band information ready for application to the database so all the Data Access Layer needs to do is determine an appropriate place to apply this information.

As the advocated solution for Java based applications is for them to explicitly manage their transactions it is relatively simple exercise to place Query Banding support into the Teradata Access Object Session so:

    /****************************************************************
* beginTransaction - Used by business service layer
* The begin transaction method marks the beginning of a transaction.
* With JDBC this is a logical begin because the beginning of a JDBC transaction
* is marked by the beginning of the first database request following a commit or a rollback.
* In this mode a commit or roll-back must be issued or transactions will be left open.
***************************************************************/
public void beginTransaction()
{
PreparedStatement stmt = null;

// Begin transaction
if (activeSession)
{
if (connection != null)
{
activeTransaction = true;
log.debug(BeginTransaction);

// This is a new Transaction so Set the QueryBand For Transaction if enabled
if (queryBandingEnabled)
{
try {
stmt = connection.prepareStatement(ThreadLocalContext.getQueryBandForTransaction());
stmt.execute();
stmt.close();
queryBandSetForTransaction = true;
}
catch (SQLException ex)
{
DBException dbEx = new DBException(BeginTransactionError + " Query Band Execute", ex);
throw dbEx;
}
}
}
else
{
DBException dbEx = new DBException(BeginTransactionError + " - No open connection");
throw dbEx;
}
}
else
{
DBException dbEx = new DBException(BeginTransactionError + " - No active session");
throw dbEx;
}
}

So now when we call upon the JDBC Implementation of the TAO Session Interface to begin a transaction the Query Band for this transaction is established using the helper method in Thread Local Context to do all the work for us.

Note how we check if (queryBandingEnabled) before attempting to apply the QueryBand information to the database? This Boolean field is initialized through the QueryBandConfig class and the associated entries in the applicationContext.xml file:

    <!-- JDBC Teradata Access Object Session manager - Used by Quotation Engine -->
<bean id="jdbcTaoSessionManager"
class="com.teradata.commons.access.JdbcTaoSessionManager">
<property name="dataSource" ref="dataSource"></property>
<property name="queryBandConfig" ref="queryBandConfig"></property>
</bean>

<!-- =================== Query Band Configuration Bean ============================== -->
<bean id="queryBandConfig" class="com.teradata.commons.access.QueryBandConfig">
<property name="queryBandingEnabled" value="${query-banding-enabled}"/>
</bean>

Which ultimately relies on the ‘query-banding-enabled’ property held in the jdbc.properties file within the src/config directories of the TZA-InsuranceService and TZA-InsuranceConsole projects.

# query-banding-enabled
# Enables Query Banding. Use only for TD version 12 or higher.
query-banding-enabled=false

Exploring Query Banding with the Web service

So now the stage is set to see Query Banding working with a real, Web service, application, starting with Query Banding turned off.

Start the Application Server

If you had closed down your application server you will need to start it up.

  • Start / Restart the Server using the Start Icon on the Server View

Restart Server

Start the Web service Test Client

You will probably need to start up the Web service Test Client again also. You can simply browse to it by pointing your favorite Web browser at http://localhost:8080/TZA-InsuranceServiceClient/samplePropertyInsuranceProxy/TestClient.jsp. Alternatively you can stay within Eclipse.

  • Open up the TZA-InsuranceServiceClient project WebContent/samplePropertyInsuranceProxy/ directory.
  • Right Click TestClient.jsp
  • Select Run As.. -> Run on Server -> Finish
  • Within the Web Services Test Client browser select the getQuote method in the Methods frame.
  • Scroll down through the Inputs frame and Select the Invoke button.
  • Examine the Web service response in the Result frame.

Web service Test Client

Clear any existing DBQL entries and Begin Query Logging by selecting the appropriate SQL within the ManageDBQL.sql file we worked on earlier.

--
--Clear Existing DBQL and BEGIN QUERY LOGGING;
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING ON ALL;

Right Click on the selected text and select Execute as Individual Statements.

Run the getQuote Web service Method again be selecting Invoke within the Web service Test Client.

Lock down the Query Logging by selecting the appropriate SQL within the ManageDBQL.sql file:

--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View we can see how each statement was executed.

DBQL Query Band with QueryBandEnabled = False

The Query Band should be NULL, as expected as query-banding-enabled is set to false. Note if you see the QueryText as <LONG> it means it is too long to fit on the screen properly. Select the “Display result in text mode” option to see all the QueryText captured.

Enable Query Band

Assuming you are on a TD 12 you can go and change the query-banding-enabled value to true in order to turn on Query Banding support.

# query-banding-enabled
# Enables Query Banding. Use only for TD version 12 or higher.
query-banding-enabled=true
  • Restart the Server to establish the new configuration.
  • Within the SQL Editor Turn on DBQL.
  • Within the Web service Test Client run the getQuote Web service Method.
  • Within the SQL Editor Turn off DBQL (to lock the results) and examine the DBQL.

The Query Band field should reflect the values we added into the getQuote Method (ClientUser, Group, Action). Note that the Thread Local Context class automatically initializes Query Band values for StartTime and QueryIssueTime.

QB-QueryBand-when-QB-Enabled-true

In the examples shown here we clearly see the time delay caused through the establishment of the connection pool on that first Web service request.

QueryText-when-QB-Enabled-true.JPG

Adding Context Capture to the Web Infrastructure

So between TLC and TAO Session we are able to provide for Query Banding with the minimum amount of developer involvement. However, it would be good if we could put static information such as ApplicationName and Version into TLC (and therefore the Query Band) without requiring the Web service developer to do it within every Web service method.

Servlet Filter

All Servlet based Web applications (Dynamic Web, Web services and Web Portlets) use the same basic Web infrastructure, based upon the web.xml file. A Servlet Filter provides a mechanism to filter each Web (HTTP) request as it comes into the Web Infrastructure. Servlet Filters are chained together in a sequence that can be managed (typically we would place Security Filters at the front of the chain).

A Servlet Filter can be used to capture Application Context, within TLC, as part of Infrastructure. With TLC maintaining this throughout the Thread of Execution.

Add com.teradata.tza.insurance.service.web Package

We start by adding a Java Package to the src/java folder to hold the ApplicationContextFilter class.

  • Create a new package within src/java of TZA-InsuranceService
  • Right Click src/java and Select -> New -> Package ->
  • Set the Name: = com.teradata.tza.insurance.service.web

Add ApplicationContextFilter

The ApplicationContextFilter class Implements the javax.servlet.Filter Interface, which requires that we implement the init(), doFilter() and destroy() methods.

Within the init() method (called only once upon establishment of this application within a given Web server) we can establish a local copy of the servletContext, which can be used within the init method to access the Spring Framework WebApplicationContext for logging or asset reporting purposes.

Within the doFilter() method (called for each Web request assigned to this filter), we can initialize the TLC object with static information (such as the ApplicationName and Version) from the servletContext and WebApplicationContext. We can also get more dynamic information from the HTTPServletRequest such as the RequestURI and Method. Application specific information (such as the ClientUser or Group) can be set within this method (although these may overridden later by the application logic as appropriate). Finally this filter will chain on to the next one in the sequence.

Start by creating a new class (ApplicationContextFilter.java) in package com.teradata.tza.insurance.service.web.

  • Right Click on src/java and select New -> Class
  • Set the Package Name to "com.teradata.tza.insurance.service.web".
  • Set the class Name to ApplicationContextFilter.java.
  • Add the javax.servlet.Filter interface

New Java Class - ApplicationContextFilter

  • Click Finish and a template source file will open within the Eclipse editor.
package com.teradata.tza.insurance.service.web;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class ApplicationContextFilter implements Filter
{
public void destroy()
{
// TODO Auto-generated method stub
}

public void doFilter(ServletRequest arg0, ServletResponse arg1,
FilterChain arg2) throws IOException, ServletException
{
// TODO Auto-generated method stub
}

public void init(FilterConfig arg0) throws ServletException
{
// TODO Auto-generated method stub
}
}

The ApplicationContextFilter class can then be built up by replacing the template code with the following code segment within the ApplicationContextFilter.java file using copy and paste.

/** 
* The contents of this file are Teradata Public Content and have been released to the Public Domain.
* Please see the license.txt file in the top level directory for more information.
*
* @author Mike.Coutts@Teradata.com
*/
package com.teradata.tza.insurance.service.web;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.teradata.commons.context.ApplicationDescription;
import com.teradata.commons.context.ThreadLocalContext;

public class ApplicationContextFilter implements Filter
{
/** The ServletContext. */
private ServletContext servletContext;

/** the logger */
private static Log log = LogFactory.getLog(ApplicationContextFilter.class);

/**
* Init.
*
* @param config
* the config
*
* @throws ServletException
* the servlet exception
*/
public void init(FilterConfig config) throws ServletException
{
this.servletContext = config.getServletContext();

WebApplicationContext webAppContext = WebApplicationContextUtils.getRequiredWebApplicationContext(this.servletContext);
ApplicationDescription applicationDescription = (ApplicationDescription) webAppContext.getBean("applicationDescription");
log.info("Init Servlet Filter for " + applicationDescription.getApplicationName() + ":" + applicationDescription.getVersion());
}

/**
* Do filter.
*
* @param response
* the httpResponse
* @param chain
* the chain
* @param request
* the httepRequest
*
* @throws IOException
* the IO exception
* @throws ServletException
* the Servlet exception
*/
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException
{
log.debug("Entering doFilter");

// ThreadLocalContext: Remove any existing User Vars.
ThreadLocalContext.removeUserVars();

/**
* Get the applicationDescription from the webApplicationContext from the servletContext
* Save this in the ThreadLocalContext as ApplicationName and Version
*
* Note: this Initial Access to TLC will initialise StartTime
*/
WebApplicationContext webAppContext = WebApplicationContextUtils.getRequiredWebApplicationContext(servletContext);
ApplicationDescription applicationDescription = (ApplicationDescription) webAppContext.getBean("applicationDescription");
ThreadLocalContext.setApplicationName(applicationDescription.getApplicationName());
ThreadLocalContext.setVersion(applicationDescription.getVersion());

/**
* Set the default Source and Action from the HttpServletRequest
* The Application may update these to more specific values.
*/
ThreadLocalContext.setSource(((HttpServletRequest)request).getRequestURI());
ThreadLocalContext.setAction(((HttpServletRequest)request).getMethod());

/**
* Fill in defaults for ClientUser and Group
* The Application may update these to more specific values.
*/
ThreadLocalContext.setClientUser("TZA_USER");
ThreadLocalContext.setGroup("TZA_SERVICE");

try
{
// TODO Placeholder for TMSM StartEvent
log.info("Start " + ThreadLocalContext.getApplicationName());

// Chain into the rest of the Application Stack
chain.doFilter(request, response);
}
finally
{
// TODO Placeholder for TMSM EndEvent
log.info("End " + ThreadLocalContext.getApplicationName());
}

log.debug("Exit doFilter");
}

/**
* Destroy.
*/
public void destroy()
{
// Clean up the ServletContext
servletContext = null;
}
}

Configure the ApplicationContextFilter

In order to add the ApplicationContextFilter to the Servlet Filter Chain we need to configure it within the web.xml file of the TZA-InsuranceService project. To do this we first define the Filter class as follows:

 <!-- Define an ApplicationContextFilter -->
<filter>
<filter-name>Application Context Filter</filter-name>
<filter-class>
com.teradata.tza.insurance.service.web.ApplicationContextFilter
</filter-class>
</filter>

Then we Map the resources that will pass through this filter as follows:

 <!-- Map the resources that pass through the ApplicationContextFilter -->
<filter-mapping>
<filter-name>Application Context Filter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

Open up the web.xml (TZA-InsuranceService/web/web.xml). and add these filter and filter-mapping definitions.

The url-pattern “/*” means that all HTTP access to this Web application will pass through this filter, which as this is a Web service application means all Web service requests will be filtered, even if we add new service definitions at a later date i.e. the filtering and establishment of the TLC is now part of the infrastructure of this application.

Testing the ApplicationContextFilter

Finally for this week we will test everything that we have put together:

  • Restart the Server to establish the new configuration.
  • Within the SQL Editor Clear DBQL and Begin Logging.
  • Within the Web service Test Client run the getQuote Web service Method.
  • Within the SQL Editor Turn off Logging (to lock the results in place) and examine the DBQL.

The Query Band field should now reflect the values we established in the ApplicationContextFilter (ApplicationName, Version, Source etc), except where these are overridden by the appropriate values set within the getQuote Method (ClientUser, Group, Action).

Query Band with Application Context Filter

Query Text with Application Context Filter

So there you go a full on What, Why and How description of some pretty Teradata specific information around Query Banding, but woven into the Web Application and Data Access Layers so as to minimize the impact of this on the Business Service layer and it’s developers.

Next week we are going to keep on the core Teradata trail by looking into Macros and Stored Procedures.

2 REPLIES

Re: The Friday Night Project #10 – Turning on Query Banding

Is it possible to use querybands via the .Net Data Provider for Teradata without the QueryIssueTime being included in the queryband?

William Miteff
Teradata Employee

Re: The Friday Night Project #10 – Turning on Query Banding

All of the above releates to a Java/JDBC based implementation for Thread Local Context and Teradata Access. For .Net Data Provider you would need to code in the SET QUERY_BAND FOR SESSION request into your .Net application and you would ave full control over which Key/Value pairs you chose to use although we of course recommend using as much information as possible so you use this information later as part of a DevOps approach.