Running Teradata Query Bands using Oracle Data Integrator (ODI)

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Running Teradata Query Bands using Oracle Data Integrator (ODI)

Teradata Query Bands Introduction:

Scientists will often band the legs of birds with devices to track their flight paths. Monitoring and analyzing the data retrieved via the bands provides critical information about the species.The same process holds true for DBAs who need more information about a query than what is typically available. Metadata—such as the name of the requesting user, the work unit, and the application name—is important for, among other purposes, workload management, tracking the use of the data warehouse and query troubleshooting.

The sort of details provided through metadata can be linked to the query using the query banding feature in the Teradata Database. A query band contains any number of name/value pairs that use reserved or custom-defined names to set, for instance, the initiating user’s corporate ID, department name and location, the name and version of the application, and the time the initiating thread of execution started. These query identifiers can be included in workload management rules and in applications and then captured in the Database Query Log (DBQL), where they are used to analyze the work flowing through the system

Oracle Data Integrator users/jobs typically use the same database credentials when they login to the database. There are situations where it is beneficial to add user-specific or job-specific information to the query request to enable different priority or simplify debugging of query performance. One way to do so is to use the database feature called query banding. This article will use the Teradata® Database as the database handling the queries, but the principles are the same for any database that supports query banding functionality.

One method to have ODI generate a query band statement is via the “On Connect/Disconnect” functionality.  Details on this functionality are available in the ODI documentation.  Use of this functionality with Teradata Query Bands is described below.

 

Setting Query Bands using Oracle Data Integrator (ODI):

Query banding must be enabled for the user.  Documentation can be found in the Teradata Workload Management Users Guide.  This is one of the ways to enable query banding for a user:

BEGIN QUERY LOGGING WITH SQL, STEPINFO ON "user01";

 

Start ODI, login and navigate to the designer.

If you don’t have a Teradata Data server defined, a new one can be created via:

1. Select Topology -> Physical Architecture -> Teradata -> New Data Server. In this tab provide the server name and username/password.

2. Select the JDBC tab and in JDBC driver option -> com.ncr.teradata.TeraDriver and JDBC url -> jdbc:teradata://10.246.64.217

 ODI_Teradata_JDBC_Connection_DetailsODI_Teradata_JDBC_Connection_Details 

3. Select the “On Connect/Disconnect” tab. In the “On connect” box fill in the query band statement.  For example:

set query_band = 'ApplicationName=ODI_TESTING_LINUX;ClientUser=ODI;' for session;

Select “commit” and "Ignore Error" and also check the “Transaction 0” check box.

 Teradata Query Band On Connect/Disconnect TabTeradata Query Band On Connect/Disconnect Tab 

4. Save all and select designer tab. Select the mapping and run the mapping. Verify whether the mapping completed or not by checking in the operator tab. Filter by date and view the session that ran the mapping.

5. To check whether the query band is set, run the below query in Teradata SQL Assistant:

SELECT DISTINCT(QUERYBAND) FROM  dbc.dbqlogtbl

SQL_Assistant_QueryBand_OutputSQL_Assistant_QueryBand_Output

In this case ODI_TESTING_LINUX is set, once the workflow ran completed successful. Please find the below snapshot for reference.

 

Additional Information:

http://www.teradatamagazine.com/v09n02/Tech2Tech/Applied-Solutions-2-Keeping-track/

http://docs.oracle.com/middleware/12211/odi/administer-develop/setup_topology.htm#BGBBCAEH