AddPreparedCollect | Application Programming Reference | Teradata Vantage - AddPreparedCollect - Advanced SQL Engine - Teradata Database

Application Programming Reference
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
hvk1593628831140.ditamap
dita:ditavalPath
hvk1593628831140.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

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

Syntax

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

Syntax Elements

CmdListID
Commands list ID as assigned from 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.
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.
NumAdded
Possible values:
  • 1 means the operation was successful.
  • 0 means the operation failed.

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.

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 Information

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