Managing Sessions and Transactions with Query Banding - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

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 Chapter 15: “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: