PreserveAfterRecreate Stored Procedure | Teradata Vantage - 17.10 - PreserveAfterRecreate - 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)

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.

Syntax

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

Syntax Elements

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.
NumPreserved
Number of statistics 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.

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