15.10 - PreserveAfterRecreate - Teradata Database

Teradata Database Application Programming Reference

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

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.

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

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.

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.

Note: 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” on page 603.

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” on page 488 and “ResyncStats” on page 509. An example workflow is shown in “Statistics from Dropped and Recreated Objects” on page 468.

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” on page 468 for more information.

 

Parameter

Description

NumPreserved

Number of statistics marked for preservation.

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