MonitorSQLText Function | Application Programming Reference | Vantage - 17.10 - MonitorSQLText - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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

Syntax

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

Syntax Elements

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

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.

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;