MonitorSQLCurrentStep Function | Application Programming Reference | Vantage - 17.05 - MonitorSQLCurrentStep - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Purpose

Returns data about the step being executed of the currently running request for the specified host, session, and vproc.

Definition

REPLACE FUNCTION SYSLIB.MonitorSQLCurrentStep
  (HostIdIn      SMALLINT,
   SessionNoIn   INTEGER,
   RunVProcNo    SMALLINT)
  RETURNS TABLE
   (HostId        SMALLINT,
    SessionNo     INTEGER,
    DynamicPlan   SMALLINT
    PartialSteps  SMALLINT,
    NumOfSteps    SMALLINT,
    CurLvl1StepNo SMALLINT,
    CurLvl2StepNo SMALLINT
    ZoneID  INTEGER,
    SPName VARCHAR(128) CHARACTER SET UNICODE,
    SPDBName VARCHAR(128) CHARACTER SET UNICODE
    DefaultDBName VARCHAR(128) 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 MonitorSQLCurrentStep function provides similar functionality to the PMPC MONITOR SQL request. For information about this interface, see MONITOR SQL.

Result Rows

Column Name Description
HostId Logical host ID associated with a PE or session. For a PE, HostId identifies one of the hosts or LANs associated with the described PE. For a session, the combination of a host ID and a session number uniquely identifies a user session on the system.
This value is NULL for AMPs. A value of zero represents the Supervisor window.
SessionNo Number of the current session. Together with a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time.
DynamicPlan Plan type:
  • 0 = Static plan
  • 1 = Dynamic plan

For more information on static and dynamic explanations of a request, see the EXPLAIN request modifier in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 or Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

PartialSteps Possible values:
  • 0 = All steps are returned
  • 1 = Partial plan or no plan is returned

    If a partial plan is returned, this indicates the steps for the final plan fragment of the dynamic explanation of the request has not yet been generated.

    If no plan is returned, this indicates the request has been throttled and is in the delay queue.

A value 1 cannot occur for a static plan.

For more information on static and dynamic explanations of a request, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 or Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

NumOfSteps Number of steps contained in the description text in the third statement of the response.

If this is a static plan (that is, when the DynamicPlan field value is zero), NumOfSteps is the total number of steps for the static plan.

If this is a complete dynamic plan (that is, when the DynamicPlan field value is 1 and the PartialSteps field value is zero), NumOfSteps is the total number of steps for the dynamic plan.

If this is a partial dynamic plan (that is, when both of the DynamicPlan and PartialSteps field values are 1), NumOfSteps is the total number of steps generated. This value is less than the total number of steps generated for the entire dynamic plan.

If this is a request with a dynamic plan that has been throttled and is in the delay queue (that is, when the DynamicPlan and PartialSteps field values are 1 and no rows are returned in response to the third statement), NumOfSteps is zero.

For more information, see the MONITOR SQL DynamicPlan and PartialSteps fields.

CurLvl1StepNo Number of the currently executing level 1 step. If parallel steps are executing, it is the number of the lowest executing step.

If this is a request with a dynamic plan that has been throttled and is in the delay queue (for example, when the NumOfSteps field value is zero and both the DynamicPlan are PartialSteps field values are 1), the CurLvl1StepNo field value is zero.

CurLvl2StepNo Number of the currently executing step. If parallel steps are executing, it is the number of the highest executing step. If only one step is executing, CurLvl1StepNo and CurLvl2StepNo are identical.

If this is a request with a dynamic plan that has been throttled and is in the delay queue (for example, when the NumOfSteps field value is zero and both the DynamicPlan are PartialSteps field values are 1), the CurLvl2StepNo field value is 1.

ZoneId The unique identifier of the zone.
SPName The outer stored procedure name, if a stored procedure is being executed.

NULL is returned in indicator mode if no stored procedure is being executed.

SPDName This is the owner database name of the outer stored procedure if a stored procedure is being executed.

NULL is returned in indicator mode if no stored procedure is being executed.

DefaultDBName This field returns the default database name of the session at the start of the non-stored procedure request. For stored procedures, it returns the default database name of the session when the stored procedure was compiled.
If only one step is executing, CurLvl1StepNo and CurLvl2StepNo are identical.

Example: Using MonitorSQLCurrentStep

select * from table (monitorsqlcurrentstep(1,1472,30718)) as t1;

*** Query completed. One row found. 11 columns returned.
*** Total elapsed time was 1 second.

       HostId      1
    SessionNo        1472
  DynamicPlan      0
PartialSteps      0
   NumOfSteps     13
CurLvl1StepNo     10
CurLvl2StepNo     10
       ZoneId           0
       SPName
     SPDBName
DefaultDBName TESTUSER