SelectStatsExecutionHistory Stored Procedure | Teradata Vantage - 17.10 - SelectStatsExecutionHistory - 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 the collection execution history for a specified completed run, commands list, user object names, or time period.

Syntax

REPLACE PROCEDURE TDSTATS.SelectStatsExecutionHistory (
  IN RunID          BIGINT,
  IN CmdListID      BIGINT,
  IN CmdListName    VARCHAR(128) CHARACTER SET UNICODE,
  IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
  IN TableName      VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
  IN StartTime      TIMESTAMP(6) WITH TIME ZONE,
  IN EndTime        TIMESTAMP(6) WITH TIME ZONE
)
  ...
;

Syntax Elements

RunID
System assigned ID for the results from a given execution of RunCollect or RecollectTable.
To show the results from a specific execution run, you must specify a value for this input parameter.
RunID is an output parameter for RunCollect and ReCollectTable. If you specify NULL, the results from all run operations in the TDSTATS.CommandsHistoryTbl table will be displayed.
CmdListID
System-assigned commands list ID from a prior call to PrepCollect.
CmdListName
User-assigned commands list name.
DatabaseName
Name of the database. DatabaseName limits the results to collections for the database you specify.
TableName
Name of the table. TableName limits the results to collections on the table you specify.
ObjectListName
Name of object list. ObjectListName limits the results to completed collections on the object list (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) you specify.
For more information about this input parameter, see the usage notes or CreateObjectList.
StartTime
Start time. StartTime limits the results to those collections submitted on or after the time you specify.
EndTime
End time. EndTime limits the results to those collections completed on or before the time you specify.

Output

Column Description
RunID System assigned ID of the RunCollect results being reported.
CmdListID Commands list ID from the collection executed.
CmdListName Commands list name, if assigned, from which the collection was executed from.
StartTime Time when the collection was submitted for execution.
EndTime Time when the collection completed execution.
DatabaseName Database on which the collection was performed.
TableName Table on which the collection was performed.
Status Possible values:
  • C means the operation completed successfully.
  • E means an error in the operation occurred.
  • A means the operation was aborted by the user.
Skipped Possible values:
  • Y means the statistics were submitted but not collected according to the defined THRESHOLD setting.
  • N means the statistics were collected and updated in the dictionary.
CollectText SQL text of the COLLECT STATISTICS statement.
ErrorText Error message if the Status output parameter returns an E. For details, see the Status output parameter.

Usage Notes

The SelectStatsExecutionHistory external stored procedure displays the historical results from already completed calls to RunCollect or RecollectTable.

You can call RunCollectReport to monitor the results for an in-progress RunCollect.

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 a value for ...
results for a particular execution RunID.
past execution results for a particular command list CmdListID or CmdListName.
past execution results from collections on a particular user object both DatabaseName or TableName
past execution results from collections on a particular named object list

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

ObjectListName usage is mutually exclusive with DatabaseName and TableName . When you specify a value other than NULL for ObjectListName , DatabaseName and TableName must be NULL.

execution results for a particular date range both StartTime and EndTime.

Example: Using SelectStatsExecutionHistory

The following example shows how to display the past execution results for the commands list named MyCmdslist.

CALL TDSTATS.SelectStatsExecutionHistory(NULL,NULL,'mycmds',NULL,NULL,NULL,NULL,NULL);
 *** 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.SELECTSTATSEXECUTIONHISTORY".
RunID CmdListID CmdListName   StartTime                         EndTime                            DataBaseName TableName  Status  Skipped    CollectText
----- --------- ------------  --------------------------------  ---------------------------------  ----------   ---------  ------  -------    ------------
1      1         mycmds       2013-02-14 18:25:08.790000+00:00  2013-02-14 18:25:08.850000+00:00   personnel    charges     C       N         COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."charges";
1      1         mycmds       2013-02-14 18:25:09.180000+00:00  2013-02-14 18:25:09.250000+00:00   personnel    charges     C       N         COLLECT STATISTICS INDEX ("EmpNo","Proj_Id")ON "personnel"."charges";
1      1         mycmds       2013-02-14 18:25:09.600000+00:00  2013-02-14 18:25:09.660000+00:00   personnel    department  C       N         COLLECT STATISTICS INDEX("DeptNo") ON "personnel"."department";
1      1         mycmds       2013 02-14 18:25:09.910000+00:00  2013-02-14 18:25:09.970000+00:00   personnel    employee    C       N         COLLECT STATISTICS INDEX("Name") ON "personnel"."employee";
1      1         mycmds       2013-02-14 18:25:10.310000+00:00  2013-02-14 18:25:10.380000+00:00   personnel    employee    C       N         COLLECT STATISTICS INDEX("EmpNo") ON "personnel"."employee";
1      1         mycmds       2013-02-14 18:25:10.720000+00:00  2013-02-14 18:25:10.810000+00:00   personnel    project     C       N         COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."project";

Related Information

For more information on ... See ...
monitoring results for an in-progress RunCollect RunCollectReport.
THRESHOLD settings
  • UpdateStatThresholdSetting.
  • Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - Database Administration, B035-1093.
SQL COLLECT STATISTICS statements Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.