Automated Statistics Management API | Teradata Vantage - 17.10 - Automated Statistics Management API - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
Automated Statistics Management API consist of interfaces to open APIs. You can use these open APIs to:
  • 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.
Teradata recommends that you interface with these APIs directly through Teradata Viewpoint Stats Manager portlet. If you require a specific, customized API that the Teradata Viewpoint Stats Manager portlet does not provide, you can call the Automated Statistics Management APIs directly from your own application.

API Categories

Automate Statistic Management open APIs are divided into the following categories:

Category Description
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:
  • Batch and prioritize a list of SQL COLLECT STATISTICS statements.
  • Submit the queued list of SQL COLLECT STATISTICS statements prepared by the PrepCollect open API.

For more information, see Preparing and Collecting Statistics.

DBAControl These open APIs:
  • Control the application of recommended actions by Analyzer open APIs.
  • Override any of the default settings stored in the TDSTATS database.

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.

Related Information

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.