AddPreparedCollect | Application Programming Reference | Teradata Vantage - 17.05 - AddPreparedCollect - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Adds a user specified COLLECT STATISTICS statement to an already prepared list of collections generated by PrepCollect.

Definition

REPLACE PROCEDURE TDSTATS.AddPreparedCollect
   (IN  CmdListID    BIGINT,
    IN  CmdListName  VARCHAR(128) CHARACTER SET UNICODE,
    IN  Priority     INTEGER,
    IN  StatsType    CHAR(1) CHARACTER SET LATIN,
    IN  DatabaseName VARCHAR(128) CHARACTER SET UNICODE,
    IN  TableName    VARCHAR(128) CHARACTER SET UNICODE,
    IN  CollectText  VARCHAR(10000) CHARACTER SET UNICODE,
    OUT NumAdded     INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
CmdListID Commands list ID as assigned from 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.”

Priority Priority of the added collection. The value is expressed as a relative rank among other collections in the same commands list.

To determine a Priority value, you can call the SelectPreparedCollects external stored procedure. For more information, see SelectPreparedCollects.

StatsType
This input parameter is reserved for future use.
DatabaseName Name of the database containing the statistic to be collected.
This input parameter does not permit wildcard characters.
TableName Name of the table on whose columns statistics are collected.
This input parameter does not permit wildcard characters.
CollectText User specified COLLECT STATISTICS statement text.
The SQL syntax of the user supplied text in CollectText is not checked by the AddPreparedCollect external stored procedure. Any errors will not be detected until the subsequent submission by the RunCollect external stored procedure.

Usage Notes

You can use the AddPreparedCollect external stored procedure to customize a system generated commands list with additional collections.

The added collection is only stored in the specified commands list. Future invocations of PrepCollect will not incorporate the added collection in its generated commands lists.

To permanently automate the collection, you must call AutomateSingleStat or AutomateStats.

Identifying a Command List

You can specify both CmdListID and CmdListName as long as they identify the same commands list.
To identify ... You must ...
the commands list being executed specify a value that is not null for either CmdListID or CmdListName.
a commands list by CmdListName assign it during a prior call to PrepCollect.

Collecting Statistics

To collect statistics for a particular object, you must specify a value for both DatabaseName and TableName in case the RemovedPreparedCollect external stored procedure is subsequently called.

DatabaseName and TableName do not permit wildcard characters.

Output Parameters

Parameter Description
NumAdded Possible values:
  • 1 means the operation was successful.
  • 0 means the operation failed.

Example: Using AddPreparedCollect

The following example shows how to add a collection on the Personnel.JobTitle table to the assigned number command list ID generated by PrepCollect (for example, 3).

CALL TDSTATS.AddPreparedCollect(3, NULL, 1, NULL, 'Personnel', 'Employee, 'Collect Statistics Column(JobTitle) On Personnel.Employee', NumAdded);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
NumAdded
---------
        1

Related Topics

For more information ... See ...
automating the collection permanently
on removing one or more collections RemovePreparedCollect.