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