RunCollectReport Stored Procedure | Application Programming Reference | Vantage - 17.10 - RunCollectReport - 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)

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.

Syntax

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

Syntax Elements

CmdListID
System-assigned commands list ID.
For more information about this input parameter, see the usage notes.
CmdListName
User-assigned commands list name.
For more information about this input parameter, see the 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.

Output

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

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.

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 Information

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.