MONITOR QUERYBAND

Teradata® Database Application Programming Reference

brand
Software
prodname
Teradata Database
vrm_release
16.20
category
Programming Reference
featnum
B035-1090-162K

Purpose

Returns the concatenated transaction, session, and profile query band for the specified session.

Input Data

Field Name Data Type Description
IndByte BYTE Indicator bits that specify which fields to treat as NULL if you are using indicator mode.

Each bit in the byte corresponds to one field in the input data.

If data is supplied for that field, set the bit to zero.

If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1.

The IndByte field is only required if the CLIv2 request is submitted in indicator mode.
mon_ver_id SMALLINT

NOT NULL

MONITOR software version ID. This can be version 6 or later.

For a general explanation of monitor version choices, see MONITOR VERSION.

host_id SMALLINT Logical ID of a host (or client) with sessions logged on. For example, a hostid of zero identifies internal sessions or system console sessions.

host_id cannot exceed 1023.

SessionNo INTEGER Number of the session. session_no combined with host_id represents a unique session ID.
RunPEVprocNo SMALLINT PE vproc number where the session runs. This is typically obtained from the RunVprocNo data field of the MONITOR SESSION response (see Group I Data Fields and JDBC ResultSet Columns). If this field is specified with NULL or zero, all PEs will be searched for the session, causing significant overhead.

Monitor Privileges

To use this request, you must have the MONSESSION privilege as part of your default role or this privilege must be granted directly to you.

For more information on roles and privileges, see:

Usage Notes

If you encounter a problematic query that is using a large amount of system resources, use the associated query band to identify the source application issuing the request.

The MONITOR QUERYBAND request cannot be used on internal sessions or sessions that are logged onto the MONITOR partition. The query band is not stored for these types of sessions and is only stored for SQL partitions.

CLIv2 Response Parcels

The MONITOR QUERYBAND request is treated internally as a one statement request that generates one response. The statement response returned from Teradata Database contains the following sequence of parcel types:

Parcel Sequence Parcel Flavor Length (Bytes) Comments/Key Parcel Body Fields
Success 8 18 to 273 StatementNo = 1

ActivityCount = 1

ActivityType = 168 (PCLMONQUERYBANDSTMT)

DataInfo 71 6 to 64100 Optional: This parcel is present if request was IndicData parcel.
Record 10
  • 5 to 64100(record mode)
  • 6 to 64100 (indicator mode)
Depending on the request (Data or IndicData), data is in record or indicator mode. This record contains the query band text.
EndStatement 11 6 StatementNo = 2-byte integer
EndRequest 12 4 None

Response

The statement described below corresponds to a ResultSet returned by the Teradata JDBC Driver, and each of the fields correspond to a ResultSet column returned by the Teradata JDBC Driver. For more information on ResultSets, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

The Record parcel returns the following field:

Field/Column Name Data Type Description
QueryBand VARCHAR (0 to 12304), NOT NULL Concatenated query band string for the transaction, session, and profile.

The QueryBand column returns the following concatenated transaction, session, and profile query band text:

=T> transaction query band =S> session query band =P> profile query band

If there... The text will contain...
is a transaction query band =T> transaction query band
is a session query band =S> session query band
is a profile query band =P> profile query band
are no query bands NULL. The return string is zero bytes or NULL in indicator mode.

Sample Input - CLIv2 Request

The following example shows how the parcels for a MONITOR QUERYBAND request, built by CLIv2, appear when sent to the Teradata Database server.

In this example the size of the response buffer is set at the maximum (64,000 bytes), although you can set it to any size.
Flavor Length Body
Num Name Bytes Field Value
0001 Req 16 Request MONITOR QUERYBAND
0003 Data 12 HostId

SessionNo

RunPEVprocNo

1

1002

16383

0004 Resp 6 BufferSize 64000

Sample Input - Teradata JDBC Driver Request

For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

Sample Output

The following example shows a concatenated query band string that is returned for the current transaction and session.

=T> job=x1; =S> org=Finance;report=Fin123;

Related Topics

For information on setting the query band for a session or transaction, see SET QUERY_BAND in Teradata® Database SQL Data Definition Language - Syntax and Examples, B035-1144.

For information on setting a profile query band, see CREATE PROFILE in Teradata® Database SQL Data Definition Language - Syntax and Examples, B035-1144.