16.20 - DisapproveStat - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Marks an analyzer missing statistic recommendation as being user disapproved making it ineligible for future automated collections and prevents it from being generated by future Analyzer-related calls.

Definition

REPLACE PROCEDURE TDSTATS.DisapproveStat
 (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 NumDisapproved INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
SCOID TDSTATS database ID for a missing statistics recommendation.
DatabaseName Name of the database. DatabaseName disapproves all statistics on the database you specify.
TableName Name of the table within specified DatabaseName. TableName limits the disapproval 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 disapproved.

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

Usage Notes

This operation applies only to missing statistic recommendations with a TDSTATS.StatsTbl.State column value of M. You can use Teradata Studio or Teradata Studio Express to view details of the TDSTATS.StatsTbl.State column.

You can call the DeAutomateStats external stored procedure to remove an already collected statistic from the TDSTATS database or inactivate it by calling the InActivateStat external stored procedure.

You can call the following external stored procedures to determine which newly recommended statistics are still unapproved from the Analyzer-related operations:

  • AnalyzeStatsReport or AnalyzeStatsUsageReport
  • SelectAutomatedStats and examine the ApprovedStat column

Disapproving 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 disapprove ... You must specify a value for ...
a single statistics recommendation SCOID.
When specifying a value for SCOID, you do not need to specify a value for the DatabaseName or TableName.
all statistics recommendations for a specified database DatabaseName.
all statistics for a specified 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 Parameter

Parameter Description
NumDisApproved Number of statistics marked disapproved.

Example: Using DisapproveStat

The following example shows how to disapprove an individual statistic recommendation on the Personnel.Employee table whose TDSTATS database ID is 101.

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