Finding the Origin of a Query Using Query Bands - Teradata Database

Finding the Origin of a Query Using Query Bands

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 SQL Data Definition Language.

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 Application Programming Reference.

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;    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 Chapter 15: “Tracking Query Behavior with Database Query Logging: Operational DBAs.”

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