16.10 - Customizing the List of Recommended Map Actions - 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)
You may want to customize the list of recommended actions in ActionsTbl before reassigning tables to different maps. The reason for this is ActionsTbl is input to the process that changes the map that a table uses. You may want to change any of these items in ActionsTbl:
  • Table grouping
  • Table order within the group
  • The list of recommended actions
To change the planned actions in the ActionsTbl, either update the table or delete rows. To add actions, insert rows. When executing DML statements directly against ActionsTbl, users must include a WHERE condition on column ActionListName to limit the changes to a designated action list. The following steps are optional.
  1. Change the recommended actions. Update the ActionsTbl. For example, if user Fisk has a customer table that has only a few rows but a lot more will be added, AnalyzeSP may recommend a sparse table for it:
    sel * from tdmaps.actionsv where ActionListName  = 'Fiskobject' and databasename='Fisk' and tablename='customer';
    
    *** Query completed. One row found. 21 columns returned.
    *** Total elapsed time was 1 second.
    
                 ZoneName ?
                   Action Alter
                   Origin A
             DatabaseName FISK
                TableName customer
                SourceMap TD_Map2
           DestinationMap TD1AMPSPARSEMAP_1NODES
             CoLocateName ?
            ActionSQLText ALTER TABLE FISK.customer, MAP=TD1AMPSPARSEMAP_1NODES
           ActionListName Fiskobject
               GroupOrder   1.00
              ActionOrder  14.00
                TableSize                 1024
    FractionOfPermDBFree  9.99999630767549E-001
                   UDICnt                    ?
                 QueryCnt                    ?
                PKJoinCnt                    ?
       LastAlterTimeStamp 2017-02-06 14:55:02.440000-08:00
             LastAlterUID 10000000
                   Issues N
    ActionsTblDescription Table is very small. One-AMP sparse map is recommended. This Action is from running Analyzer without DBQL analysis.
    
    The DBA knows that the table is growing too fast to use a sparse map. This SQL can change the recommended map for the table:
    UPDATE tdmaps.ActionsTbl 
       SET ActionSQLText  = 'ALTER TABLE FISK.customer, MAP=TD_Map3',
           DestinationMap = 'TD_Map3',
           Origin         = 'U'
    WHERE ActionListName = 'fiskobject'
       AND DatabaseName   = 'fisk'
       AND TableName      = 'customer';
    
    The following example illustrates another situation in which you might want to update the ActionsTbl:
    UPDATE tdmaps.ActionsTbl SET Action = 'Exclude', ActionsTblDescription = 'Slocum marks this exclude because this table will be dropped tomorrow so why move it?'
    WHERE DatabaseName = 'Slocum' AND TableName = 'Customer' AND ActionListName = 'MoveTablesToMap2';
    
    Teradata recommends using UPDATE instead of DELETE because UPDATE logs the reason for the change in the history table.
  2. Change the order in which groups of tables are reassigned or change the order in which tables are reassigned within a group. Tables are grouped by database if you do not use DBQL step logging. If you do use DBQL step logging, tables are grouped by how often they are joined together. To add a new group between existing ones, use a fractional value in the GroupOrder column. To add new actions within a group, use a fractional value in the ActionOrder column.
    The following is an example of ActionsTbl rows that are listed from top to bottom in their priority of execution. Note the following information about the table:
    • Rows whose Origin value is 'A' were generated by AnalyzeSP and those with a 'U' (or NULL) value were added later by manual INSERT statements.
    • Rows for tables 't6' and 't7' represent user-defined actions within a new group whose assigned GroupOrder value of 1.5 falls between existing groups 1 and 2.
    • Rows for tables 't10' thru 't20' represent user-defined actions added to existing group 2. The fractional digits assigned to their ActionOrder values (1.1 thru 1.11) result in them being ordered between existing ActionOrder values 1 and 2.