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