Use the following procedure to reassign a table to another map to maximize processing efficiency. The Mover process uses stored procedures to act on the recommendations in ActionsTbl.
-
In a BTEQ or Teradata Studio window, call the manager procedure ManageMoveTablesSP to schedule the execution of map actions previously generated by AnalyzeSP.
This procedure moves a group of tables out of ActionsTbl and into a queue table. In the following example, the DBA schedules execution of the actions in MyNewMapActions and imposes a time limit of 12 hours (720 minutes):
Session #1: CALL TDMaps.ManageMoveTablesSP(NULL, NULL, 'MyNewMapActions', 720, 'Y', :ActionsCompleted, :NumErrors, :TimeExpired);
In the following example, a DBA uses a scheduler to call the manager procedure in BTEQ:
CALL TDMaps.ManageMoveTablesSP(DATE+SESSION, NULL, 'MyNewMapActions', 4*60, 'Y', :ActionsCompleted, :NumErrors, :TimeExpired);
There can be only one instance of ManageMoveTablesSP active in Vantage at a time.For more information on ManageMoveTablesSP, including the syntax, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.
-
Call the worker procedure MoveTablesSP in one or more BTEQ or Teradata Studio windows to start reassigning maps.
Each call to MoveTablesSP requires a separate session. One way to do this is by using the BTEQ commands SET SESSIONS and REPEAT. MoveTablesSP issues an ALTER TABLE request for each row in the queue table. In the following example, the DBA starts two concurrent worker sessions, one in each BTEQ window:
Session #2: CALL TDMaps.MoveTablesSP('P',NULL,NULL,NULL,NULL); Procedure has been executed. *** Total elapsed time was 11 hours 3 minutes and 5 seconds. Session #3: CALL TDMaps.MoveTablesSP('P',NULL,NULL,NULL,NULL); Procedure has been executed. *** Total elapsed time was 11 hours 19 minutes and 45 seconds.This procedure records completed or in progress actions in TDMaps.ActionHistoryTbl.
For more information on MoveTablesSP, including the syntax, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.
-
Optionally, call MonitorMoveTablesSP to monitor the progress of the map reassignments.
This procedure reports on the progress of both the manager and worker sessions. It reports the number of tables altered, the number left to alter, and the percentage complete. For example:
CALL tdmaps.MonitorMoveTablesSP('MyNewMapActions', :NumTables, :NumComplete, :NumInProgress, :NumWaitingToStart, :PercentComplete); *** Procedure has been executed. *** Warning: 3212 The stored procedure returned one or more result sets. *** Total elapsed time was 1 second. NumTables 2712 NumComplete 2526 NumInProgress 1 NumWaitingToStart 185 PercentComplete .93 *** ResultSet# 1 : 1 rows returned by "TDMAPS.MONITORMOVETABLESSP". ZoneName ? JobNumber 2016042801 Action Alter Status InProgress DatabaseName MHM TableName TAB21_PI GroupOrder 1.00 SourceMap TD_Map1 DestinationMap TD_Map2 ActionSQLText ALTER TABLE MHM.TAB21_PI, MAP=td_map2 ActionListName MyNewMapActions TableSize 1118482 FractionOfPermDBFree 9.99999999610461E-001 Issues N PrevGroupsBytesPerSec EstElapsedTimeInSecond ? ElapsedTime ? StartTime 2016-02-02 12:22:45.270000+00:00 EndTime ? ErrorCode 0 RunUID 0000F903 ActionsTblDescription Move table for system expansion. Description ? WorkerId 1For more information on MonitorMoveTablesSP, including the syntax, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.