Using Queryband

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)

9 thoughts on “Using Queryband”

  1. Hi Agilan,
    I do not plan to write post on reading the explain plan since it is quite eye-destroying, and we developed a product called PRISE Tuning Assistant which prevent you from reading and analyzing dense explain texts. I suggest you to try it, and you will see how easy and quick to understand an explain plan. Manual reading is complicated, for example spool tracking, alias replacement and need of related dictionary informations. You can reach the trial here:
    http://prisetools.com/products/tuning-assistant

Leave a Reply

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