17.00 - 17.05 - Finding the Origin of a Query Using Query Bands - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Administration
Publication ID
B035-1093-170K
Language
English (United States)

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)
    --------- ------------------------------------------------------------
         1299
         1298 =S> REPJOBID=495;REPORT=Sales;CLIENTMACHINE=XYZ12;REPTYPE=10;

    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). Query logging stores query band information 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;    SET QUERY_BAND='REPJOBID
    =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 .