PreserveAfterRecreate Stored Procedure | Teradata Vantage - 17.05 - PreserveAfterRecreate - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Marks one or more automated statistics as eligible for having their TDStats stored metadata preserved after their owning table is temporarily dropped and then recreated.

Definition

REPLACE PROCEDURE TDSTATS.PreserveAfterRecreate
(IN DatabaseName VARCHAR(128) CHARACTER SET UNICODE,
IN ObjectName VARCHAR(128) CHARACTER SET UNICODE,
IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
OUT NumPreserved INTEGER)
   .
   .
   .
;

Input Parameters

Parameter Description
DatabaseName Database whose automated statistics should be marked for preservation.
ObjectName Table within DatabaseName whose statistics should be marked for preservation.
ObjectListName Object list whose defined databases and tables should be marked for preservation.

Usage Notes

The use of this API should be limited to those tables that after being temporarily dropped are subsequently recreated with the exact same name, column definitions, and statistics definitions.

Users are cautioned against using this API in table recreation scenarios that do not meet this criteria as the subsequent automated statistics management operations may fail.

To mark all tables within a given database for preservation, callers should specify a value for input parameter DatabaseName. To further limit the preservation to a single table, callers should also specify a value for input parameter ObjectName.

To mark the set of databases or tables defined within a previously created object list, callers should specify a value for input parameter ObjectListName. For information on how to create an ObjectListName, see CreateObjectList.

The values of input parameters DatabaseName and ObjectListName cannot both be NULL. Callers must specify a non-NULL for one of them.

After a table, whose automated statistics are marked for preservation, is dropped and recreated, users must call AutomateStats or ResyncStats to complete the preservation process. For more information see AutomateStats and ResyncStats. An example workflow is shown in Statistics from Dropped and Recreated Objects.

If not marked for preservation, the TDStats entries for dropped and recreated tables become orphaned from the Data Dictionary and the associated statistics can no longer be managed through Automated Statistics Management APIs. See Orphaned Statistics for more information.

Output Parameter

Parameter Description
NumPreserved Number of statistics marked for preservation.

Example: Using PreserveAfterRecreate

The following example shows how to mark the automated statistics Personnel.Employee table for preservation.

CALL TDSTATS.PreserveAfterRecreate('Personnel','Employee', NULL, NumPreserved);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
NumPreserved
------------
           4