15.10 - ReCollectTable - Teradata Database

Teradata Database Application Programming Reference

Teradata Database
Programming Reference

Prepares and executes the automated collections for a specified table.

 (IN DatabaseName        VARCHAR(128) CHARACTER SET UNICODE,
  IN TableName           VARCHAR(128) CHARACTER SET UNICODE,
  IN Duration       INTEGER,
  OUT NumStmtsSubmitted INTEGER,
  OUT NumStatsSubmitted  INTEGER,
  OUT NumStatsCollected  INTEGER,
  OUT NumErrors      INTEGER,
  OUT DurationExpired    CHAR(1) CHARACTER SET LATIN,
  OUT RunID        BIGINT)




Name of the database to recollect on.

Note: This input parameter cannot be NULL.


Name of the table within the specified DatabaseName to recollect on.

Note: This input parameter cannot be NULL.


Maximum time limit in minutes for submitting collections on the table.

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

After the specified Duration expires, no new collections can be submitted. However, any already in-progress submissions are allowed to complete before returning control to the caller

Note: NULL indicates an unlimited duration.

You can call RecollectTable after the completion of events that significantly alter data of a specified table, such as bulk loads.

The functionality of ReCollectTable is equivalent to PrepCollect followed immediately by a RunCollect on an individual table.

ReCollectTable batches and issues some or all of the statistics for the specified table within a single SQL COLLECT STATISTICS statement.

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 ReCollectTable to submit COLLECT STATISTICS statements (see “Granting Required Privileges” on page 484).

Only statistics marked approved by the user are collected.

Any statistics marked inactive by a prior call to CleanupStats or InActivateStat are not collected. For more information on these external stored procedures, see “CleanupStats” on page 523 or “InActivateStat” on page 540.





Number of COLLECT STATISTICS statements submitted.

Each statement may batch together multiple statistics on the specified table.


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


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


Number of statements that encountered a failure.


Possible values:

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

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

    Note: You can use RunID to call SelectStatsExecutionHistory to display results from a completed ReCollectTable call.

    CALL TDSTATS.ReCollectTable ('PERSONNEL', 'EMPLOYEE', NULL, NumStmtsSubmitted, NumStatsSubmitted,NumStatsCollected,NumErrors,DurationExpired, RunID);
    *** Procedure has been executed. 
     *** Total elapsed time was 1 second.
    NumStmtsSubmitted NumStatsSubmitted NumStatsCollected  NumErrors  DurationExpired               RunID
    ----------------- ----------------- ----------------- ----------- ---------------    ----------------
                    1                 4                 4           0               N                   1