15.10 - PrepCollect - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

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

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” on page 603.

CmdListName

Optional user assigned name for the resulting prepared list of commands.

Note: 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” on page 550.

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 SQL Functions, Operators, Expressions, and Predicates.

Preparing Statistics for Collection

Notice:

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.

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

 

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  

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  

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

For more information on ...

See ...

displaying and customizing the prepared collection

“RunCollect” on page 550.

You can also refer to the following external stored procedures:

  • “SelectPreparedCollects” on page 559.
  • “AddPreparedCollect” on page 585.
  • “RemovePreparedCollect” on page 588.
  • “PrioritizePreparedCollect” on page 593.
  • statistics marked approved

    “ApproveStat” on page 535.

    statistics marked inactive

  • “InActivateStat” on page 540.
  • “CleanupStats” on page 523.
  • excluded objects

    “AddExcludedObject” on page 570.