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

Prepares a prioritized list of SQL COLLECT STATISTICS statements based on the automated statistics definitions stored in the TDSTATS database.

Syntax

REPLACE PROCEDURE TDSTATS.PrepCollect (
  IN  DatabaseName VARCHAR(128) CHARACTER SET UNICODE,
  IN  TableName VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
  [ IN  CmdListName VARCHAR(128) CHARACTER SET UNICODE, ]
  OUT NumStmtsPrepped INTEGER,
  OUT NumStatsPrepped INTEGER,
  OUT CmdListID BIGINT
)
  ...
;

Syntax Elements

DatabaseName
Name of the database. DatabaseName allows you to specify the database whose statistics should be prepared for collection.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
TableName
Name of a table. TableName allows you to specify the table whose statistics should be prepared for collection.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
ObjectListName
Name of the object list. ObjectListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose statistics should be prepared for execution.
For more information about this input parameter, see the usage notes or CreateObjectList.
CmdListName
[Optional] User assigned name for the resulting prepared list of commands.
CmdListName can be referred to by its name rather than its system assigned numeric CmdListID. If PrepCollect is called again with a specified CmdListName that already exists, the list will be overwritten and the original CmdListId retained.
You can execute a command list as many times as needed, although Teradata recommends that you call PrepCollect before each call to RunCollect. For more information, see RunCollect.
NumStmtsPrepped
Number of COLLECT STATISTICS statements prepared in the list. Each statement includes one or more individual statistics on a table.
NumStatsPrepped
Number of individual statistics prepared for collection in the list.
CmdListID
System-assigned ID for the prepared list of statements.

Usage Notes

By default, PrepCollect batches and issues together all individual statistics defined on a specified table within a single SQL COLLECT STATISTICS statement.

Any objects excluded by a prior call to AddExcludedObject will not have their statistics prepared for collection.

Only statistics marked approved by the user are included in the prepared collection list.

Any statistics marked inactive by a prior call to CleanupStats or InActivateStat will not be prepared for collection.

Wildcard Characters

The following table describes the wildcard characters that the names of some databases, tables, and objects contain.
Character Description
% (PERCENT SIGN) Represents any string of zero or more arbitrary characters. Any string of characters is acceptable as a replacement for the percent.
_ (LOW LINE) Represents exactly one arbitrary character. Any single character is acceptable in the position in which the underscore character appears.

For more information on how to use these wildcard characters, see the SQL LIKE clause in Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates, B035-1145.

Preparing Statistics for Collection

If you do not specify a value for the following input parameters, the operation is performed on all user created databases in the system.
To prepare ... You must specify a value for ...
all statistics for a particular database for collection DatabaseName.
all statistics for a particular table for collection both DatabaseName and TableName.
a list of objects that cannot be specified by DatabaseName or TableName for collection

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

ObjectListName usage is mutually exclusive with DatabaseName and TableName . When you specify a value other than NULL for ObjectListName , DatabaseName and TableName must be NULL.

Example: Using PrepCollect to Prepare a Named List of Collections for a Database

The following example shows how to prepare a list of collections for the Personnel database and assign the list a name (for example, MyCmdsList).

CALL TDSTATS.PrepCollect ('PERSONNEL', NULL, NULL, 'MyCmdsList', NumStmtsPrepped, NumStatsPrepped, CmdListID);
*** Procedure has been executed.
*** Total elapsed time was 3 seconds.
NumStmtsPrepped                           NumStatsPrepped           CmdListID
------------------                       ----------------           ----------
             20                                        45                    1

Example: Using PrepCollect to Prepare a List of Collections on an Object List

The following example shows how to prepare a list of collections on the object list named FinCashList.

CALL TDSTATS.PrepCollect(NULL , NULL, 'FinCashList', NULL, NumStmtsPrepped, NumStatsPrepped, CmdListID);

Related Information

For more information on ... See ...
displaying and customizing the prepared collection RunCollect.

You can also refer to the following external stored procedures:

statistics marked approved ApproveStat.
statistics marked inactive
excluded objects AddExcludedObject.