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:
|
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