DisapproveStat Stored Procedure | Application Programming Reference | Vantage - 17.10 - DisapproveStat - 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 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.

Syntax

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

Syntax Elements

SCOID
TDSTATS ID for a missing statistics recommendation.
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 the usage notes or CreateObjectList.
NumDisapproved
Number of statistics marked disapproved.

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.

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