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

Teradata Vantageā„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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.

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