RemovePreparedCollect Stored Procedure | Teradata Vantage - 17.10 - RemovePreparedCollect - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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

Syntax

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

Syntax Elements

RequestID
TDSTATS ID for a prepared collection command.
CmdListID
Commands list ID as assigned from prior call to PrepCollect. For more information, see PrepCollect.
CmdListName
User-named commands list as specified in prior call to PrepCollect. For more information, see PrepCollect.
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.
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.
This input parameter cannot be NULL.
NumRemoved
Number of collections removed.

Usage Notes

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.

Removing Collections

To remove ... You must specify a value for ...
a single collection RequestID.
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.

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.

Example: Using RemovePreparedCollect

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