17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

All actions stored within TDMaps.ActionsTbl that are part of the specified SerialTableActionList or ParallelTableActionList and whose type are not 'Exclude' are fetched and queued for execution in a group at a time. Objects in the SerialTableActionList are processed by only one session. Objects in ParallelTableActionList are processed by one or more sessions. One or both parameters must be specified. If both are specified, one session works on the SerialTableActionList and the other session(s) work on the ParallelTableActionList. After all the not ‘Exclude’ actions are processed, then ‘Exclude’ actions are moved to the history table without submitting an ALTER TABLE.

Generate table action lists from prior calls to TDMaps.AnalyzeSP. The value specified for AnalyzeSP parameter OutputActionList must be the same value specified for parameter SerialTableActionList or ParallelTableActionList. Prior to calling ManageMoveTablesSP, you can customize the actions generated in OutputActionList by executing standard UPDATE, DELETE, and INSERT statements on TDMaps.ActionsTbl.

Prior to being queued for execution, all qualifying actions are grouped and ordered according to the designated column values in ActionsTbl.GroupOrder and ActionsTbl.ActionOrder. Sets of tables are assigned to the same group as determined by prior calls to procedure AnalyzeSP, if the Analyzer is run with the log data and determines there is a join relationship.

After calling ManageMoveTablesSP, one or more concurrent calls must be made to related procedure MoveTablesSP to execute the queued actions. Because a call to ManageMoveTablesSP does not complete and return until all actions complete (or TimeLimit expires), separate DBS sessions are required to issue calls to MoveTablesSP.

Only one active execution of ManageMoveTablesSP is allowed in the system. The underlying system queue tables used for scheduling and executing actions do not support more than one SerialTableActionList or ParallelTableActionList at a given time.

Results from executing this procedure and the worker procedures it manages are written to table TDMaps.ActionHistoryTbl whose rows represent completed or in progress actions. Monitor and display results by issuing standard SELECT statements on the table.

If the specified TimeLimit expires before all actions in SerialTableActionList or ParallelTableActionList complete, no additional groups of actions queue for execution.

The default behavior is to manage actions with the same group as a single unit with respect to scheduling, executing, and time limit expiration processing. Override the behavior by specifying input parameter GroupActions as 'N'. The grouping behavior ensures that Alter (move) actions on groups of tables with strong joining relationships complete by a given call to ManageMoveTablesSP. If the Analyzer is run without log data, then tables are grouped by database.

If GroupActions is 'Y' (or NULL) and a non-NULL TimeLimit is specified, ManageMoveTablesSP cannot schedule a group of actions to execute if their combined estimated time of completion exceeds the current time remaining. Similarly, when GroupActions is 'N’ and a non-NULL TimeLimit is specified, ManageMoveTablesSP does not execute the action for a given table if its estimated time of completion exceeds the current time remaining. When this occurs for a given group or table whose assigned GroupOrder or ActionOrder will execute, it is recorded within ActionHistoryTbl with a Status value of Skipped.

If TimeLimit expires before all actions within a given group complete, all actions in that group complete before stopping execution.

When GroupActions is 'N', and the time limit expires, any queued actions in TDMaps.ActionQueueTbl that did not run are transferred back to table TDMaps.ActionsTbl. They are assigned a GroupOrder representing the highest priority within their designated ActionListName.

If the returned value of output parameter TimeExpired is 'Y', complete any remaining actions by calling ManageMoveTablesSP again with the same TableActionList. ManageMoveTablesSP automatically resumes the uncompleted actions in the list.

To stop a running call to ManageMoveTablesSP, call procedure StopMoveTablesSP.