15.10 - MONITOR QUERYBAND - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

 

Element

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.

Note: 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” on page 162.

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” on page 120). If this field is specified with NULL or zero, all PEs will be searched for the session, causing significant overhead.

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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • 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.

    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 and Key Parcel Body Fields

    Success

    8

    18 to 273

    Statement No = 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 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

    Note: 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.

    The Record parcel returns the following field:

     

    Field/Column Name

    Data Type

    Description

    QueryBand

    VARCHAR (0 to 12316), 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.

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

    Note: In this example, the size of the response buffer in the example 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

    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.

    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;

    For information on setting the query band for a session or transaction, see SET QUERY_BAND in SQL Data Definition Language.

    For information on setting a profile query band, see CREATE PROFILE in SQL Data Definition Language.