15.10 - AddPreparedCollect - Teradata Database

Teradata Database Application Programming Reference

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

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

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

Parameter

Description

CmdListID

Commands list ID as assigned from prior call to PrepCollect.

For more information about this input parameter, see “Usage Notes.”

CmdListName

User named commands list as specified in a prior call to PrepCollect.

For more information about this input parameter, see “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” on page 559.

StatsType

Note: This input parameter is reserved for future use.

DatabaseName

Name of the database containing the statistic to be collected.

Note: This input parameter does not permit wildcard characters.

TableName

Name of the table on whose columns statistics are collected.

Note: This input parameter does not permit wildcard characters.

CollectText

User specified COLLECT STATISTICS statement text.

Note: The SQL syntax of the user supplied text in CollectText is not checked by the AddPreparedCollect external stored procedure. Any errors will not be detected until the subsequent submission by the RunCollect external stored procedure.

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

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

Note: DatabaseName and TableName do not permit wildcard characters.

 

Parameter

Description

NumAdded

Possible values:

  • 1 means the operation was successful.
  • 0 means the operation failed.
  • 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
     

    For more information ...

    See ...

    automating the collection permanently

  • “AutomateSingleStat” on page 492
  • “AutomateStats” on page 488.
  • on removing one or more collections

    “RemovePreparedCollect” on page 588.