Executes a prioritized list of SQL COLLECT STATISTICS statements as prepared by a prior call to PrepCollect.
Definition
REPLACE PROCEDURE TDSTATS.RunCollect (IN CmdListID BIGINT, IN CmdListName VARCHAR(128) CHARACTER SET UNICODE, IN Duration INTEGER, IN Resume CHAR(1) CHARACTER SET LATIN, 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 |
---|---|
CmdListID | Command list ID assigned from a prior call to PrepCollect. For more information about this input parameter, see “Usage Notes.” |
CmdListName | User named commands list as specified in a prior call to PrepCollect. For more information about this input parameter, see “Usage Notes.” |
Duration | Maximum time limit in minutes for submitting collections within the list. The valid range is 1 to 4320 (or 72 hours). If you specify NULL, the duration is unlimited and RunCollect will not complete until all collection commands in the list have finished executing. After the specified Duration expires, no new collections will be submitted, but any already in-progress submissions will be allowed to complete before returning control to the caller.
|
Resume | Possible values:
If you specify Y and the prior RunCollect on the specified commands list finished without expiration, the invoked RunCollect will not submit any collection statements.
For more information about this input parameter, see “Usage Notes.” |
Usage Notes
Teradata recommends that you call PrepCollect before each call to RunCollect to ensure the latest information regarding missing and stale statistics is incorporated into the command list to be executed. You can choose to re-execute an already prepared list by bypassing subsequent calls to PrepCollect.
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 RunCollect to submit COLLECT STATISTICS statements (see Granting Required Privileges).
By default, PrepCollect batches all individual statistics for a specified table into a single SQL COLLECT STATISTICS statement.
Unless you specify Y for the Resume input parameter, RunCollect submits commands starting at the beginning of the list. If a prior call to RunCollect expired, as determined by the DurationExpired output parameter, you may want to resume the execution on that same list by instructing RunCollect to pick up where it left off in the command list.
- AbortCollect to abort one or more pending or in-progress collections under the submission control of RunCollect.
- RunCollectReport to report on the progress of any RunCollect operating on a specified commands list.
- SelectStatsExecutionHistory to display the historical results of one or more past completed RunCollect calls.
Output Parameters
Parameter | Description |
---|---|
NumStmtsSubmitted | Number of COLLECT STATISTICS statements that were submitted. Each statement may batch together multiple statistics on a specified table. |
NumStatsSubmitted | Number of individual statistics submitted for collection independent of any THRESHOLD settings in effect. |
NumStatsCollected | Number of individual statistics that were actually collected and were 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 for this execution or run operation. |
Example: Using RunCollect
The following example executes the prepared collections for the command list named, MyCmdsList, and specifies a maximum duration of 2 hours.
CALL TDSTATS.RunCollect (NULL,'MyCmdsList',120,'N',NumStmtsSubmitted, NumStatsSubmitted,NumStatsCollected,NumErrors,DurationExpired,RunID); *** Procedure has been executed. *** Total elapsed time was 4 seconds. NumStmtsSubmitted NumStatsSubmitted NumStatsCollected NumErrors DurationExpired RunID ----------------- ----------------- ----------------- --------- --------------- ----- 10 20 15 0 'N' 1
Related Topics
For more information on ... | See ... |
---|---|
the PrepCollect external stored procedure | PrepCollect. |
SQL COLLECT STATISTICS statements | Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. |
THRESHOLD settings |
|