Adds a user specified COLLECT STATISTICS statement to an already prepared list of collections generated by PrepCollect.
Definition
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) . . . ;
Input Parameters
| 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. |
| StatsType |
This input parameter is reserved for future use.
|
| DatabaseName | Name of the database containing the statistic to be collected. This input parameter does not permit wildcard characters.
|
| TableName | Name of the table on whose columns statistics are collected. This input parameter does not permit wildcard characters.
|
| CollectText | User specified COLLECT STATISTICS statement text. 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.
|
Usage Notes
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
| 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.
Output Parameters
| Parameter | Description |
|---|---|
| NumAdded | Possible values:
|
Example: Using AddPreparedCollect
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
Related Topics
| For more information ... | See ... |
|---|---|
| automating the collection permanently | |
| on removing one or more collections | RemovePreparedCollect. |