RunCollect Stored Procedure | Application Programming Reference | Teradata Vantage - RunCollect - Teradata Vantage - Analytics Database

Executes a prioritized list of SQL COLLECT STATISTICS statements as prepared by a prior call to PrepCollect.

Syntax

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

Syntax Elements

CmdListID

Command list ID assigned from a prior call to PrepCollect.

For more information about this input parameter, see the usage notes.

CmdListName

User-named commands list as specified in a prior call to PrepCollect.

For more information about this input parameter, see the 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:

• Y. If you specify Y, execution is resumed where a prior RunCollect operation left off from the most recent execution of the same commands list.
• N or NULL. If you specify N or NULL, execution starts at the beginning of the command list.

For more information about this input parameter, see the usage notes.

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:

• Y means the Duration value specified expired before the RunCollect external stored procedure finished executing the entire commands list.
• N means the Duration value specified did not expire.

RunID

System-assigned ID for the results associated for this execution or run operation.

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.

Related Information