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