16.10 - Reassigning the Map for a Table - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
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.
  1. 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 Teradata Database at a time.

    For more information on ManageMoveTablesSP, including the syntax, see SQL Functions, Operators, Expressions, and Predicates.

  2. 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 SQL Functions, Operators, Expressions, and Predicates.

  3. 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           1

    For more information on MonitorMoveTablesSP, including the syntax, see SQL Functions, Operators, Expressions, and Predicates.