ReCollectTable Stored Procedure | Application Programming Reference | Vantage - ReCollectTable - Advanced SQL Engine - Teradata Database

Prepares and executes the automated collections for a specified table.

Syntax

REPLACE PROCEDURE TDSTATS.ReCollectTable (
  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
)
  ...
;

Syntax Elements

DatabaseName

Name of the database to recollect on.

This input parameter cannot be NULL.

TableName

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

This input parameter cannot be NULL.

Duration

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.

NULL indicates an unlimited duration.

NumStmtsSubmitted

Number of COLLECT STATISTICS statements submitted.

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

NumStatsSubmitted

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

NumStatsCollected

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

NumErrors

Number of statements that encountered a failure.

DurationExpired

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.

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

Usage Notes

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

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 or InActivateStat.

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