Finding the Origin of a Query Using Query Bands - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - Database Administration

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update
Product Category
Teradata Vantage

A query band is a set of name-value pairs assigned to a session or transaction that identifies the originating source of a query. You define these identifiers and they get stored along with other session data in DBC.SessionTbl.

This is particularly useful for identifying specific users submitting queries from a middle-tier tool or application, since these often use pooling mechanisms that hide the identity of the users when each connection in the pool logs in to the database using the same user account.

For more information on query bands and how to assign them to a session or transaction, see SET QUERY_BAND in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

After you create a query band set, retrieve the query band and the name-value pairs by using:
  • HELP SESSION or query the QueryBand field of the DBC.SessionInfo view.
  • System UDFs and external stored procedures. For example, to find the query bands of active sessions, use the MonitorQueryBand function:
    BTEQ -- Enter your DBC/SQL request or BTEQ command:
    SELECT t1.sessionno, MonitorQueryBand(Hostid, sessionNo, runvprocno)
    FROM TABLE (MonitorSession(1,'*',0)) AS t1;
    *** Query completed. 2 rows found. 2 columns returned.
    *** Total elapsed time was 1 second.
    SessionNo MonitorQueryBand(HostId,SessionNo,RunVprocNo)
    --------- ------------------------------------------------------------

    For more information on the MonitorQueryBand function and a full list of available functions and procedures, see Teradata Vantage™ Application Programming Reference, B035-1090.

  • Query logging (DBQL). When you enable query logging, query band information is stored in the DBC.DBQLogTbl table. Use the DBC.QryLogV view to access this table. You can use the information to analyze queries after they execute.
    BTEQ -- Enter your DBC/SQL request or BTEQ command:
    sel queryband(FORMAT 'X(50)'), querytext(FORMAT 'X(30)') from qrylogv order by
    StartTime, queryid;
    *** Query completed. 10 rows found. 2 columns returned.
    *** Total elapsed time was 1 second.
    QueryBand                                          QueryText
    -------------------------------------------------- ------------------------
    ?                                                  Begin query logging on a
    =S> REPJOBID=495;REPORT=Sales;CLIENTMACHINE=XYZ12; sel count(*) from dbc.da
    =S> REPJOBID=495;REPORT=Sales;CLIENTMACHINE=XYZ12; sel count(*) from dbc.ta
    =S> REPORT=Costs;CLIENTMACHINE=XYZ12;              SET QUERYChapter 11, Managing C_BAND='REPORT=C
    =S> REPORT=Costs;CLIENTMACHINE=XYZ12;              sel * from tdwm.rules;
    =S> REPORT=Costs;CLIENTMACHINE=XYZ12;              sel * from tdwm.ruledefs
    =S> REPORT=Costs;CLIENTMACHINE=XYZ12;              sel * from configuration
    =S> REPJOBID=99;REPORT=Acct;CLIENTMACHINE=BBB1;    sel database from dbc.da
    ?                                                  End query logging on all

    For more information on the database query log, see Tracking Query Behavior with Database Query Logging: Operational DBAs.

  • Teradata Viewpoint to track requests that meet a certain CPU or duration threshold.

Teradata also supplies library functions to enable UDFs, methods, and external procedures to retrieve query band information. For example:

TO return this value from the current query band… USE this library function…
name FNC_GetQueryBand
value FNC_GetQueryBandValue
all name:value pairs FNC_GetQueryBandPairs

For more information, see Teradata Vantage™ SQL External Routine Programming , B035-1147 .