MonitorSQLStepsSV - Teradata Vantage

Use this single operational view API for SQL steps being run across VantageCloud Lake clusters. Then use this function to get additional details on that session to monitor the current SQL step for the SQL run, provided that session is running a query.

Syntax

REPLACE FUNCTION SYSLIB.MonitorSQLStepsSV (
  HostIdIn SMALLINT,
  SessionNoIn INTEGER,
  RunVprocNo SMALLINT
) RETURNS TABLE (
    HostId SMALLINT,
    SessionNo INTEGER,
    DynamicPlan SMALLINT,
    PartialSteps SMALLINT 
    StepNum INTEGER,
    Confidence SMALLINT,
    EstRowCount FLOAT,
    ActRowCount FLOAT,
    EstRowCountSkew FLOAT,
    ActRowCountSkew FLOAT,
    EstRowCountSkewMatch FLOAT,
    ActRowCountSkewMatch FLOAT,
    EstElapsedTime FLOAT,
    ActElapsedTime FLOAT,
    SQLStep  VARCHAR(2048)CHARACTER SET UNICODE,
    ParentSessionNo INTEGER,
    Type VARCHAR,
    Id VARCHAR,
    Name VARCHAR,
    Group VARCHAR
  )
  ...
;

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

This value is incremented by 10000 for each compute cluster for the single view. For example, the first node of the primary cluster starts at 1, the first node of the first online compute cluster starts at 10001, and the first node of the second online compute cluster starts with 20001, and so on.

SessionNo

Number of the current session. 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.

A value 1 cannot occur for a static plan.

StepNum

Unique number identifying the EXPLAIN step.

Confidence

Confidence level as determined by the optimizer:

• 0 = None
• 1= Foreign Key
• 2 = Low
• 3 = High

EstRowCount

Estimated row count generated from the Optimizer plan for this step.

For a PRPD plan, the EstRowCount field for the split step (that is, a RETRIEVE or JOIN step with “split into” appearing in the EXPLAIN when target spools are generated) is the estimated row counts for ALL split spools.

For more information on PRPD, see Join Strategies and Methods.

ActRowCount

Actual row count returned from the AMP for this step.

For a PRPD plan, this value includes rows from all split spools for a split step.

See Join Strategies and Methods.

EstRowCountSkew

Estimated row count for the skew split spool in PRPD, which contains the rows with skewed values of this spool.

See Join Strategies and Methods.

ActRowCountSkew

Actual number of rows for the skew split spool in PRPD.

See Join Strategies and Methods.

EstRowCountSkewMatch

Estimated row count for the skew match split spool in PRPD, which contains the rows with skewed values of the other relation to be joined with this relation.

See Join Strategies and Methods.

ActRowCountSkewMatch

Actual number of rows for the skew match split spool in PRPD.

See Join Strategies and Methods.

EstElapsedTime

Estimated time for the query as generated from the Optimizer plan.

ActElapsedTime

Actual elapsed time calculated by the dispatcher.

SQLStep

Generated text for the step.

ParentSessionNo

Only available in the detailed view and for compute clusters. This integer links the query running from the primary cluster to the compute cluster. The Parent Session is the session from the primary cluster that correlates to this SessionNo from the compute cluster.

Type

Only available in the detailed view. Identifies the group type (compute cluster or primary cluster).

Id

Only available in the detailed view. Provides the TOS identifier the of the group (useful to identify compute clusters).

Name

Only available in the detailed view. Provides the name of the compute cluster.

Group

Only available in the detailed view. Provides the name of the compute group.

Usage Notes

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.

For information on MONITOR SQL, see MONITOR SQL.