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