15.10 - RunCollectReport - Teradata Database

Teradata Database Application Programming Reference

Teradata Database
Programming Reference

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.

  (IN CmdListID    BIGINT,
   IN RunID       BIGINT)




System assigned commands list ID.

For more information about this input parameter, see “Usage Notes.”


User assigned commands list name.

For more information about this input parameter, see “Usage Notes.”


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.

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

To display results from one or more completed run operations, you must call SelectStatsExecutionHistory. For more information, see “SelectStatsExecutionHistory” on page 562.

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 SQL External Routine Programming.

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.

Note: 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.

Note: When you specify a value other than NULL for RunID, the values for CmdListID and CmdListName are not relevant and can remain NULL.





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.


    Database on which the collection is performed.


    Table on which the collection is performed.


    Start time for an in-progress or completed collections.


    End time for completed collections.


    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.


    SQL text of the COLLECT STATISTICS statement.


    Error message text if the Status output parameter returns an E. For details, see Status.


    System assigned ID of the RunCollect results reported.

    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";

    For more information on ...

    See ...

    displaying results from one or more past executions with the option of specifying a variety of criteria

    “SelectStatsExecutionHistory” on page 562.

    stored procedure dynamic result sets

    SQL External Routine Programming.