16.20 - MonitorSQLText - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

Returns the SQL text of the request currently being executed for the specified host, session, and vproc.

Definition

REPLACE FUNCTION SYSLIB.MonitorSQLText
  (HostIdIn     SMALLINT,
   SessionNoIn  INTEGER,
   RunVProcNo   SMALLINT
  )
  RETURNS TABLE
   (HostId      SMALLINT,
    SessionNo   INTEGER, 
    SeqNum      SMALLINT,
    SQLTxt      VARCHAR(31000) CHARACTER SET UNICODE
   )
            .
            .
            .
;

Input Parameters

Parameter Description
HostIdIn Logical ID of a host (or client) with sessions logged on.
SessionNoIn Session number of the SQL to monitor.
RunVprocNo PE vproc number where the session runs.

Usage Notes

This table function is only supported in Constant Mode.

If MONITOR SQL processing is not completed within the timeout interval, then an error is returned to the client application. When a MONITOR SQL request is timed out, the processing continues internally to its completion. If the client application submits a new MONITOR SQL request for the same timed out target session while the previous timed out one is still being processed, then an error is returned. The timeout interval can be set in the DBS Control field, PMPC_TimeoutSecs. The default timeout interval is 60 seconds. If the PMPC_TimeoutSecs field is set to zero, the MONITOR SQL timeout request will be disabled and no timeout will occur. For more information on the PMPC_TimeoutSecs field, see Teradata Vantage™ - Database Utilities , B035-1102 .

The MonitorSQLText function provides similar functionality to the PMPC MONITOR SQL request. For information about this interface, see MONITOR SQL.

Result Rows

Column Name Description
HostId Host ID of the SQLtext.
SessionNo Session number of the SQLtext.
SeqNum Sequence number of the row. For example, if the SQL text exceeds 31,000 bytes, the system will return multiple rows.
SQLTxt SQL text of the running request.

Example: Using MonitorSQLText

SELECT SQLTxt FROM TABLE (MonitorSQLText(1, 1001, 16383)) AS t2;
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 58 seconds.
SQLTxt
------------------------------------------------------------------------
select a11.c1, a12.c1, a13.c1, a21.c1, a22.c1, a23.c1  from dbaaa.skewAmp1
 a11, dbaaa.skewAmp1 a12, dbaaa.skewAmp1 a13, dbaaa.skewAmp1 a21, dbaaa.sk
ewAmp1 a22, dbaaa.skewAmp1 a23;