Returns data about the step being executed of the currently running request for the specified host, session, and vproc.
Syntax
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 ) ... ;
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
- 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.
- 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
- 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.
- NumOfSteps
- Number of steps in the description text in the third statement of the response.
Plan DynamicPlan Field Value PartialSteps Field Value NumOfSteps Static 0 Number of steps for plan. Complete dynamic 1 0 Number of steps for plan. Partial dynamic 1 1 Number of steps generated. - CurLvl1StepNo
- Number of the currently executing level 1 step. If parallel steps are executing, it is the number of the lowest executing step.
- 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.
- ZoneId
- The unique identifier of the zone.
- SPName
- The outer stored procedure name, if a stored procedure is being executed.
- SPDName
- This is the owner database name of the outer stored procedure if a 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.
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.
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