Manage Sessions and Transactions | Teradata Vantage - Managing Sessions and Transactions with Query Banding - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
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, unless query logging is disabled. 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;

    Result:

    *** 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.