15.10 - RunCollect - Teradata Database

Teradata Database Application Programming Reference

Teradata Database
Programming Reference

Executes a prioritized list of SQL COLLECT STATISTICS statements as prepared by a prior call to PrepCollect.

 (IN CmdListID          BIGINT,
  IN Duration      INTEGER,
  IN Resume           CHAR(1) CHARACTER SET LATIN,
  OUT NumStmtsSubmitted INTEGER,
  OUT NumStatsSubmitted INTEGER,
  OUT NumStatsCollected INTEGER,
  OUT NumErrors     INTEGER,
  OUT DurationExpired   CHAR(1) CHARACTER SET LATIN,
  OUT RunID            BIGINT)




Command list ID assigned from a prior call to PrepCollect.

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


User named commands list as specified in a prior call to PrepCollect.

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


Maximum time limit in minutes for submitting collections within the list.

The valid range is 1 to 4320 (or 72 hours).

Note: If you specify NULL, the duration is unlimited and RunCollect will not complete until all collection commands in the list have finished executing. After the specified Duration expires, no new collections will be submitted, but any already in-progress submissions will be allowed to complete before returning control to the caller.


Possible values:

  • Y. If you specify Y, execution is resumed where a prior RunCollect operation left off from the most recent execution of the same commands list.
  • N or NULL. If you specify N or NULL, execution starts at the beginning of the command list.
  • Note: If you specify Y and the prior RunCollect on the specified commands list finished without expiration, the invoked RunCollect will not submit any collection statements.

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

    Teradata recommends that you call PrepCollect before each call to RunCollect to ensure the latest information regarding missing and stale statistics is incorporated into the command list to be executed. You can choose to re-execute an already prepared list by bypassing subsequent calls to PrepCollect.

    If you have permissions to grant the STATISTICS privilege on the qualifying user objects, you must also grant that privilege to the TDSTATS database which allows RunCollect to submit COLLECT STATISTICS statements (see “Granting Required Privileges” on page 484).

    By default, PrepCollect batches all individual statistics for a specified table into a single SQL COLLECT STATISTICS statement.

    Unless you specify Y for the Resume input parameter, RunCollect submits commands starting at the beginning of the list. If a prior call to RunCollect expired, as determined by the DurationExpired output parameter, you may want to resume the execution on that same list by instructing RunCollect to pick up where it left off in the command list.

    You can also call:

  • AbortCollect to abort one or more pending or in-progress collections under the submission control of RunCollect.
  • RunCollectReport to report on the progress of any RunCollect operating on a specified commands list.
  • SelectStatsExecutionHistory to display the historical results of one or more past completed RunCollect calls.




    Number of COLLECT STATISTICS statements that were submitted. Each statement may batch together multiple statistics on a specified table.


    Number of individual statistics submitted for collection independent of any THRESHOLD settings in effect.


    Number of individual statistics that were actually collected and were not skipped due to any defined THRESHOLD settings.


    Number of statements that encountered a failure.


    Possible values:

  • Y means the Duration value specified expired before the RunCollect external stored procedure finished executing the entire commands list.
  • N means the Duration value specified did not expire.
  • RunID

    System assigned ID for the results associated for this execution or run operation.

    The following example executes the prepared collections for the command list named, MyCmdsList, and specifies a maximum duration of 2 hours.

    CALL TDSTATS.RunCollect (NULL,'MyCmdsList',120,'N',NumStmtsSubmitted,
    *** Procedure has been executed. 
    *** Total elapsed time was 4 seconds.
    NumStmtsSubmitted NumStatsSubmitted NumStatsCollected NumErrors DurationExpired RunID
    ----------------- ----------------- ----------------- --------- --------------- -----
                   10                20                15         0             'N'     1

    For more information on ...

    See ...

    the PrepCollect external stored procedure

    “PrepCollect” on page 546.


    SQL Data Definition Language.

    THRESHOLD settings

  • “UpdateStatThresholdSetting” on page 581.
  • SQL Data Definition Language.
  • Database Administration.