PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Exploring QueryBand !!

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 can define 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:>
 

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;
 
.SET QUERY_BAND='PROJECT=TeradataWorld;TASK=QB_example;' for session
(For the logged in session)SELECT queryband FROM dbc.sessioninfoX WHERE sessionNo=session;

 

 

***********************PROJECT=TeradataWorld TTASK=QB_example; ***************************

SELECT queryband FROM dbc.dbqlogtbl WHERE Queryid=…;

—–=S> PROJECT=TeradataWorld;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 ;
—————————————————-
TeradataWorld

(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)
 
 
 
 

Add a Comment

Your email address will not be published. Required fields are marked *