Using Queryband

Sponsored by PRISE Ltd.
www.prisetools.com

How to use querybanding in Teradata?

What is queryband?

Teradata is a diligent RDBMS that runs sometimes millions of SQLs a day. You will see them in the DBQL (DataBase Query Logging area) - if it is switched on - but it's a hard job to know around in that mess of queries. How can I find a specific query? What did that query run by? If I want to analyze or modify something I need to find the source of the execution as exactly as can be.
Queryband is a labelling possibility to flag the queries to let their source job/report/etc. be easily found.

Who defines the queryband?

Setting the queryband is usually the responsibility of the query runner:

• ETL software or solution that executes it
• OLAP tool that issues it
• Person, who runs it ad-hoc

How to set the queryband?

Technically it is a quite simple stuff: Teradata provides a command to set it:

SET QUERY_BAND = {'<variable1>=<value1>;<variable2>=<value2>;...' / NONE} [UPDATE] for SESSION/TRANSACTION;

, where:

<variable1>=<value1>;

Queryband can consist of arbitrary number of "variable"-"value" pairs. Both are string values. Do not forget to put the semicolon after each variable-value pair!

NONE: clears the queryband 

UPDATE: is specified, then those variables that has been previously defined are updated by the new value, others are added with the given value. Empty value string is a valid content and will not remove the variable. Please note that deleting a value is only possible by redefining the queryband without that specific variable.

SESSION/TRANSACTION: what it says...

Where can I check queryband?

The values are reflected in the dbc.SessionfoX.QueryBand and the dbc.DBQLogtbl.QueryBand. The following example shows its content:

SET QUERY_BAND='PROJECT=TeraTuningBlog;TASK=QB_example;' for session;

(For the logged in session)
SELECT queryband FROM dbc.sessioninfoX WHERE sessionNo=session;
----------------------------------------------------
PROJECT=TeraTuningBlog;TASK=QB_example;

(For the formerly ran queries)

SELECT queryband FROM dbc.dbqlogtbl WHERE Queryid=...;
----------------------------------------------------
=S> PROJECT=TeraTuningBlog;TASK=QB_example;

(For a specific variable, eg. "PROJECT")
SELECT QB_PROJECT FROM
(
   SELECT CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.sessioninfoX WHERE sessionNo=session
) x ;

----------------------------------------------------

TeraTuningBlog

(Which queries has been run by the "LoadCustomers" project?)

   SELECT a.*, CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.dbqlogtbl a WHERE QB_PROJECT="LoadCustomers"

;

Designing querybanding

We know how to set the queryband, it's quite easy to build in / configure in the ETL tool, OLAP software and other query running applications. But what variables should we define, and how should we populate them? I give a best practice, but it is just a recommendation, can be modified due according to your taste.

First of all, some things to mind:

• Use short variable names and values, since they will be logged in each DBQL records
• Define consistent structure in each source systems to easily analyze data
• Record as detailed information as you need, not more, not less. Define unique values for those items you later want to differentiate. Using a lookup/hierarchy table you can easily merge what you need, but never can drill down what is aggregated.

I recommend these variables to be defined:

• SYS: Maximum of 3 characters ID of the system that ran the Query, like INF (Informatica), MST (Microstrategy), SLJ (SLJM), BO (Business Objects), AH (ad-hoc query tool)
• ENV: P (Production) / Tx (Test x) / Dx (Development x), the identifier of environment. x may be neglected, if it does not matter
• JOB: Which job or report contains that specific query (the name of it)
• STP: (Step) Which SQL script, or other sub-structure does the query belong to (name of it)
• VER: Version of the JOB. This will determine the version of the script (if available)

Sponsored by PRISE Ltd.
www.prisetools.com