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
-
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. -
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' through 't20' represent user-defined actions added to existing group 2. The fractional digits assigned to their ActionOrder values (1.1 through 1.11) result in them being ordered between existing ActionOrder values 1 and 2.
Action Origin TableName GroupOrder ActionOrder Alter A t1 1 1 Alter A t2 1 2 Alter A t3 1 3 Alter U t6 1.5 1 Alter U t7 1.5 2 Alter A t4 2 1 Alter U t10 2 1.1 Alter U t11 2 1.2 Alter U t12 2 1.3 Alter U t13 2 1.4 Alter U t14 2 1.5 Alter U t15 2 1.6 Alter U t16 2 1.7 Alter U t17 2 1.8 Alter U t18 2 1.9 Alter U t19 2 1.10 Alter U t20 2 1.11 Alter A t5 2 2