16.20 - RunCollect - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

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

You can also call:
  • 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:
  • 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.

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
  • UpdateStatThresholdSetting.
  • Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - Database Administration, B035-1093.