RunCollect Stored Procedure | Application Programming Reference | Vantage - 17.10 - RunCollect - 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)

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.

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.

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 Information

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.