ApproveStat - Teradata Database - Teradata Vantage NewSQL Engine

Application Programming Reference

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
vwf1492987142269.ditamap
dita:ditavalPath
changebar_rev_16_20_exclude_audience_ie.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Marks an Analyzer missing statistic recommendation as being user approved making it eligible for future automated collections.

Definition

REPLACE PROCEDURE TDSTATS.ApproveStat
 (IN SCOID BIGINT,
 IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
 IN TableName     VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
 OUT NumApproved    INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
SCOID TDSTATS ID for a missing statistics recommendation from an Analyze-related operation or an existing statistic copied from an Automate-related operation.
DatabaseName Name of the database. DatabaseName approves all statistics defined on the database you specify.
TableName Name of the table within the specified DatabaseName. TableName limits the approval to the statistics defined on the table you specify within DatabaseName.
ObjectListName Name of the object list. ObjectListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose statistics should be approved.

For more information about this input parameter, see “Usage Notes” or CreateObjectList.

Usage Notes

Depending on how Analyze-related external stored procedures are called, any:

  • Resulting recommendations for new statistics may remain unapproved until ApproveStat is performed on them.
  • Existing statistics copied to the TDSTATS database may also remain unapproved until ApproveStats is performed on them.

To determine which recommended missing statistics are still marked unapproved after an Analyzer-related operation, you can call the following external stored procedures:

  • AnalyzeStatsReport
  • AnalyzeStatsUsageReport
  • SelectAutomatedStats and examine the ApprovedStat result column.

To determine which imported statistics are still marked unapproved after an Automate-related operation, you can call the following external stored procedures:

  • AutomateReport
  • SelectAutomatedStats

Approving Statistics Recommendations

If you do not specify a value for the following input parameters, the operation is performed on all user created databases in the system.
To approve ... You must specify a value for ...
a single statistic recommendation SCOID.
When specifying a value for SCOID, you do not need to specify a value for DatabaseName or TableName.
all statistics recommendations for a particular database DatabaseName.
all statistics recommendations for a particular table both DatabaseName and TableName.
a list of objects that cannot be specified by DatabaseName or TableName

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

ObjectListName usage is mutually exclusive with DatabaseName and TableName . When you specify a value other than NULL for ObjectListName , DatabaseName and TableName must be NULL.

Output Parameters

Parameter Description
NumApproved Number of approved statistics that were previously unapproved.

Example: Using ApproveStat

The following example shows how to approve all unapproved statistic recommendations on the Employee table.

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