15.10 - RemovePreparedCollect - Teradata Database

Teradata Database Application Programming Reference

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

Removes one or more collections from an already prepared commands list generated by PrepCollect or customizes a system generated commands list.

REPLACE PROCEDURE TDSTATS.RemovePreparedCollect
  (IN RequestID     BIGINT,
   IN CmdListID     BIGINT,
   IN CmdListName   VARCHAR(128) CHARACTER SET UNICODE,
   IN DatabaseName  VARCHAR(128) CHARACTER SET UNICODE,
   IN TableName     VARCHAR(128) CHARACTER SET UNICODE,
   OUT NumRemoved   INTEGER)
           .
           .
           .
;
 

Parameter

Description

RequestID

TDSTATS ID for a prepared collection command.

CmdListID

Commands list ID as assigned from prior call to PrepCollect. For more information, see “PrepCollect” on page 546.

CmdListName

User named commands list as specified in prior call to PrepCollect. For more information, see “PrepCollect” on page 546.

DatabaseName

Name of the database. If you specify a value other than NULL, DatabaseName removes all collections in the commands list that are defined on the database you specify.

Note: This input parameter cannot be NULL.

TableName

Name of the table. If you specify a value other then NULL, TableName removes all collections in the commands list that are defined on the table you specify within DatabaseName.

Note: This input parameter cannot be NULL.

The qualifying collections are only removed from the specified commands list. Future invocations of PrepCollect may continue to incorporate the removed collection into newly generated commands lists.

To permanently remove automated collections, you must call the DeAutomateStats external stored procedure For more information, see “DeAutomateStats” on page 497.

Removing Collections

 

To remove ...

You must specify a value for ...

a single collection

RequestID.

Note: If you specify a value for RequestID, you do not need to specify values for CmdsListId, DatabaseName, or ObjectName.

If you do not specify a RequestID, you must specify a value other than NULL for CmdsListId or CmdListName to limit the removal to a particular prepared list of collection commands.

To determine the assigned RequestID for a particular collection, you can call SelectPreparedCollects. For more information, see “SelectPreparedCollects” on page 559.

all collections for a particular database within the specified commands list

DatabaseName.

all collections for a particular table within the specified command list

both DatabaseName and TableName.

 

Parameter

Description

NumRemoved

Number of collections removed.

The following example shows how to remove all collections on the Personnel.Department table within the command list named MyCmdsList.

CALL TDSTATS.RemovePreparedCollect(NULL,NULL,'MyCmdsList,'PERSONNEL','Department', NumRemoved);
 
*** Procedure has been executed. 
*** Total elapsed time was 1 second.
 
NumRemoved
-----------
         2