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

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

Definition

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

Input Parameters

Parameter Description
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 “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 “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 “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.

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.

Output Parameters

Parameter Description
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.

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 Topics

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.