It is important to review the actions Teradata recommends for reassigning tables to different maps. Look for issues that may need to be resolved before any recommended changes can occur. Also consider whether the recommended actions are best for your site.
Isolate any issues found during map analysis.
Query the issues in ActionsTbl. For example:
SELECT * FROM tdmaps.ActionsVX WHERE Issues='Y' OR Action <> 'alter';
The Description column describes the issue.
- Check for space issues that might prevent recommended map reassignments. In particular, see FractionOfDBFreePerm, which lists the space that will be left in a database after tables are redistributed. For example, if your database has 100 MB of perm space and the table being redistributed is 1 MB, FractionOfDBFreePerm is .99, indicating that 99% of permanent space in the database remains free after table redistribution. It is particularly important to notice this value after a system expansion because the table header has to be written to each AMP. For example, if you upgrade from a 2-AMP system to a 100-AMP system the table header will use 50 times more space than before, even though the table is the same size. Reassigning a table to another map requires an INSERT-SELECT, which uses 2 times the amount of permanent space the table requires.
- Give the table being reassigned additional permanent space if necessary.
Look for other issues.
These can include the following common issue types, for example:
System-defined join indexes automatically use the new map.
- The table uses a sparse map and will be reassigned to a contiguous map.
- The table has permanent journaling enabled, so the map reassignment will fail. Tables with permanent journals must use the default map.
- The join or hash index will be invalid when the table is assigned to a new map. The DBA must drop and recreate the join or hash index after the map reassignment. This occurs when the join or hash index is based on these types of tables:
- Primary AMP index (PA)
- Nonpartitioned primary index (NOPI)
- Column-partitioned primary index (CPPI)
- A user-created join or hash index does not transfer when a table is assigned to a new map. The DBA must issue a separate ALTER TABLE request for a user-created join or hash index.