ReCollectTable Stored Procedure | Application Programming Reference | Vantage - 17.05 - ReCollectTable - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Prepares and executes the automated collections for a specified table.

Definition

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

Input Parameters

Parameter Description
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.

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.

Output Parameters

Parameter Description
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.

Example: Using ReCollectTable

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