RunCollectReport Stored Procedure | Application Programming Reference | Vantage - RunCollectReport - Advanced SQL Engine - Teradata Database

Application Programming Reference
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Displays all pending, in-progress, and completed collections for the most recent execution run operation of a given commands list or the results for a specified completed execution run operation as identified by its RunID output parameter.

Definition

REPLACE PROCEDURE TDSTATS.RunCollectReport
  (IN CmdListID    BIGINT,
   IN CmdListName  VARCHAR(128) CHARACTER SET UNICODE,
   IN RunID       BIGINT)
           .
           .
           .
;

Input Parameters

Parameter Description
CmdListID System assigned commands list ID.

For more information about this input parameter, see “Usage Notes.”
CmdListName User assigned commands list name.

For more information about this input parameter, see “Usage Notes.”
RunID System assigned ID for the results from a given execution of RunCollect.

To show the results from a specific execution run, you must specify a value for RunID.

If RunID is NULL, the results from all run operations in TDSTATS.CommandsHistoryTbl will be shown.

Usage Notes

To display results from one or more completed run operations, you must call SelectStatsExecutionHistory. For more information, see SelectStatsExecutionHistory.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see Teradata Vantage™ - SQL External Routine Programming , B035-1147 .

Displaying Execution Results

To display ... You must specify ...
in-progress execution results or the most recent completed results for a specified commands list a value other than NULL for either CmdListID or CmdListName.

To identify a list by name, you must have assigned it during a prior call to PrepCollect.

You can specify CmdListID and CmdListName as long as they identify the same commands list.
a particular execution run operation that may not be the most recent a value for RunID.
When you specify a value other than NULL for RunID, the values for CmdListID and CmdListName are not relevant and can remain NULL.

Output Parameters

Parameter Description
Status Possible values:
  • P (Pending)
  • I (In progress)
  • C (Completed successfully)
  • E (Error)
  • A (Aborted by the user)
PriorityRank System assigned submission priority relative to other collection commands in the same run operation.
DatabaseName Database on which the collection is performed.
TableName Table on which the collection is performed.
StartTime Start time for an in-progress or completed collections.
EndTime End time for completed collections.
Skipped Possible values:
  • Y means the statistics are submitted but not collected according to the defined THRESHOLD setting.
  • N means the statistics were collected and updated in the dictionary.
RequestID System assigned ID for the COLLECT STATISTICS statement.
CollectText SQL text of the COLLECT STATISTICS statement.
ErrorText Error message text if the Status output parameter returns an E. For details, see Status.
RunID System assigned ID of the RunCollect results reported.

Example: Using RunCollectReport

The following example shows how to display all pending statistics that are submitted but not collected according to the THRESHOLD setting.

CALL TDSTATS.RunCollectReport(null,'mycmds',1);
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 1 second.
 *** ResultSet# 1 : 6 rows returned by "TDSTATS.RUNCOLLECTREPORT".
Status PriorityRank DataBaseName TableName StartTime                          EndTime                         Skip  Req. ID  Collect Text
C   1               personnel    charges    2013-02-14 18:25:08.790000+00:00  2013-02-14 18:25:08.850000+00:00 N    00007    COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."charges";
C   2               personnel    charges    2013-02-14 18:25:09.180000+00:00  2013-02-14 18:25:09.250000+00:00 N    100008   COLLECT STATISTICS INDEX("EmpNo","Proj_Id") ON "personnel"."charges";
C   3               personnel    department 2013-02-14 18:25:09.600000+00:00  2013-02-14 18:25:09.660000+00:00 N    100009   COLLECT STATISTICS INDEX("DeptNo") ON "personnel"."department";
C   4               personnel    employee   2013-02-14 18:25:09.910000+00:00  2013-02-14 18:25:09.970000+00:00 N    100010   COLLECT STATISTICS INDEX("Name") ON "personnel"."employee";
C   5               personnel    employee   2013-02-14 18:25:10.310000+00:00  2013-02-14 18:25:10.380000+00:00 N    100011   COLLECT STATISTICS INDEX("EmpNo") ON "personnel"."employee";
C   6               personnel    project    2013-02-14 18:25:10.720000+00:00  2013-02-14 18:25:10.810000+00:00 N    100012   COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."project";

Related Topics

For more information on ... See ...
displaying results from one or more past executions with the option of specifying a variety of criteria SelectStatsExecutionHistory.
stored procedure dynamic result sets Teradata Vantage™ - SQL External Routine Programming , B035-1147 .