AutomateReport Stored Procedure | Application Programming Reference | Vantage - 17.10 - AutomateReport - 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)

Returns a summary of events performed by a prior invocation of an Automate or Deautomate-related operation.

Syntax

REPLACE PROCEDURE TDSTATS.AutomateReport (
  IN AutomateId BIGINT
)
  ...
;

Syntax Elements

AutomateId
Results ID from a prior AutomateStats or DeAutomateStats call. For more information, see AutomateStats or DeAutomateStats.
This input parameter cannot be NULL.

Output

Column Description
DatabaseName Database from which statistics were automated.
ObjectName Table from which statistics were automated.
IndexNumber Dictionary ID of the index whose statistics were automated.
This value is NULL for non-indexes.
IndexName User assigned name for the index, if any.
ExpressionList List of comma separated fields on which statistic definitions are defined.
EventType Event type:
  • A means the event was automated successfully.
  • D means the event was deautomated.
  • W means a warning message was returned indicating that the operation on the statistics or object did not succeed.
EventDescription Text description of the event. For example, if the statistic cannot be automated, the EventDescription parameter returns a warning message.
EventDate Time at which the Automation event occurred.
Approved Flag indicates if the caller approved the copied statistics.
StatsType Type of statistics identified by the dictionary:
  • B (Base table)
  • V (View)
  • Q (Query)
V and Q statistic types are reserved for future use.
StatsId Dictionary assigned ID for the statistic as recorded in the DBC.StatsTbl.StatsId column.
StatsName User assigned name for the statistic, if any.
SCOID Automated Statistics Management assigned ID for the automated statistic as recorded in the TDSTATS.StatsTbl.SCOID column.
AutomateId Automated Statistics Management assigned ID for the history results from this operation.

Usage Notes

Report data is taken from the TDSTATS.AutomateHistoryTbl table along with corresponding data from the TDSTATS.StatsTbl and dictionary tables.

Excluded Statistics

AutomateReport returns the following information for those statistics, such as Geospatial statistics, that are excluded from automation:
  • A value of N in the Successful output column.
  • A warning message in the EventDescription output column.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see Teradata Vantageā„¢ - SQL External Routine Programming, B035-1147.

Example: Using AutomateReport

The following example shows a summary of the actions performed by a prior call to the AutomateStats external stored procedure.

CALL TDSTATS.AutomateReport(1);
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 1 second.
*** ResultSet# 1 : 5 rows returned by "TDSTATS.AUTOMATEREPORT".
DatabaseName     ObjectName  IndexNumber ExpressionList EventDescription  .....
---------------- ----------- ----------- -------------  ----------------  .....
PERSONNEL        EMPLOYEE        1       EmpNo          AUTOMATED STAT
PERSONNEL        EMPLOYEE        4       Name           AUTOMATED STAT
PERSONNEL        DEPARTMENT      1       DeptNo         AUTOMATED STAT
PERSONNEL        DEPARTMENT      ?       DeptName       AUTOMATED STAT
PERSONNEL        PROJECT         1       Proj_Id        AUTOMATED STAT