Managing Sessions and Transactions with Query Banding - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - Database Administration
Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
tgx1512080410608.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
ujp1472240543947
Product Category
Software
Teradata Vantage

How can you identify a unit of work such as a report that consists of multiple SQL requests that span multiple tiers in the enterprise, such as a web service? Use query banding.

A query band is a set of name-value pairs assigned to a session, transaction, or profile that you can use to identify the originating source of a query. This is particularly useful for identifying specific users submitting queries from a middle-tier tool or application. Query banding information helps you answer the following types of questions:
  • Which external users are using the most resources?

    Access the information stored in the QueryBand field in the DBC.DBQLogTbl table when you enable query logging. See Tracking Query Behavior with Database Query Logging: Operational DBAs.

    For example:

    Sel Username (Format 'x(10)'), queryband(Format 'x(40)'), AMPCPUTime from qrylogV
where ampcputime > .154;

*** Query completed. 9 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

UserName    QueryBand                                     AMPCPUTime
----------  ----------------------------------------  --------------
TWMUSER1    =S> CLUser=KShort;Dept=Sales;Job=z995;             0.188
TWMUSER18   =S> CLUser=TJuli;Dept=DMgr;Job=x1235;              0.170
TWMUSER     =S> CLUser=TJuli;Dept=DMgr;Job=x1234;              0.171
TWMUSER13   =S> CLUser=BPut;Dept=Mgr;Job=q2120;                0.173
TWMUSER1    =S> CLUser=KShort;Dept=Sales;Job=z995;             0.157
TWMUSER27   =S> CLUser=KShort;Dept=Sales;Job=z996;             0.186
TWMUSER2    =S> CLUser=DThom;Dept=Manuf;Job=a100;              0.156
TWMUSER28   ?                                                  0.158
TWMUSER     =S> CLUser=DGale;Dept=Mktg;Job=m125;               0.158

BTEQ -- Enter your DBC/SQL request or BTEQ command:

    You can even use query bands to determine what functional area of an application is using the most resources.

  • How can I control how a workload or type of query from a specific session uses system resources?

    Set up rules using Teradata Viewpoint Workload Designer portlet and associate query bands with filter rules or define them as workload definition attributes.

  • Is there a correlation of CPU time by region?

    Using Teradata Workload Analyzer, you can get a report of how CPU time is affected by queries coming from different regions. For example you can track CPU time in the Atlantic, Central, Northeast, Northwest, Southeast, and so on.

  • Are there some months when response time is slower than others? That is, are there correlations of response times by current month?

    Using Teradata Workload Analyzer, generate reports to measure resource usage by periods of time such as by month or quarter. You could generate a report like the following: