- Identify situation where statistics management might be improved (for example, missing, out of date (also referred to as stale), and unused statistics).
- Ability to control the application of recommended actions by the Analyzer operation and override any of the default settings stored in TDSTATS database.
- View and modify the prepared collection list prepared by the Automated Statistics Management PrepCollect open API prior to its actual submission.
Automate Statistic Management open APIs are divided into the following categories:
|Automate||These open APIs copy the qualifying statistics definitions from the DBC dictionary to the TDSTATS database and synchronize any subsequent changes from SQL Data Definition Language operations.
For more information, see Automating Statistics Collection and Monitoring.
|Analyzer||These open APIs analyze user objects and logged query plans to identify missing, stale, and unused statistics.
For more information, see Analyzing User Objects and Logged Query Plans.
|Collect||These open APIs:
For more information, see Preparing and Collecting Statistics.
|DBAControl||These open APIs:
For more information, see Controlling Recommended Actions and Overriding Default Settings.
Each category of open API records its results within the TDSTATS database which can be queried using SQL.
Specifying the Scope of API Operations
All of the above listed categories of APIs accept input parameters that optionally limit the scope of the operation to a specified database, table, or statistic. You can also define a named list consisting of any set of databases or tables, and then call the APIs with the list name, see Creating Object Lists or Open APIs for Object Lists.
Requirements for Using the Open API
The SQL interfaces to statistics management already exist if the TDSTATS database has been created by the DIPSTATS script as part of the database installation.
You can use Teradata Studio or Teradata Studio Express to list the TDSTATS tables and views and view details about each view and table column. For information about Teradata tools and to download the tools, go to Teradata Downloads.
Required Privileges for Using the Open APIs
|You must have the following privilege as part of your default role or it must be granted directly to you...||To ...|
|GRANT EXECUTE PROCEDURE ON TDSTATS TO User||call the Automated Statistic Management open APIs.|
|GRANT SELECT ON TDSTATS TO User||query the TDSTATS database directly.
If the Automated Statistics Management report open APIs (such as, AutomateReport, AnalyzeStatsReport, AnalyzeStatsUsageReport, and so on) are sufficient, you do not need to grant the SELECT privileges on the TDSTATS database.
|GRANT EXECUTE ON DBC.dbqlaccessmacro TO User||enable statistics usage logging.|
|GRANT STATISTICS ON User_Objects TO TDSTATS||execute the AnalyzeStats or AnalyzeStatsUsage and RunCollect open APIs.|
|For information about ...||See ...|
|the functionality of these open APIs||Automated Statistics Management API Features and Examples.|
|the required privileges to run the Automated Statistics Management open APIs||GrantPrivileges.|
|each of the Automated Statistics Management open APIs||Automated Statistics Management APIs.|
|Teradata Viewpoint||Teradata® Viewpoint User Guide, B035-2206.|