Sometimes more permspace is required on the destination system than was required for the same database/user on the source system. As a rule of thumb, 20% free permspace on the destination system database is recommended, but still may not be sufficient in some cases.
If the migration involves a hash function change, there must be enough free space in each database to hold an extra temporary copy of the largest table in that database. A hash function change may result in many rows being located on different AMPs on the destination system. When a row is redistributed to another AMP, the row gets copied into a buffer to be sent to the new AMP. The space for the original copy of the row(s) is not freed until the entire table has been restored and redistributed.
The most common reasons that pre-existing database space is insufficient for a newer system include the following:
- New features and functionality add fields to existing system tables or may require new system tables.
- Table headers increase in size.
- The new system may be configured with more AMPs. Each AMP will has a copy of every table header.
- Hash function changes may result in data skewing related to NOPI tables or PPI tables.
- The source and destination systems have different DBS Control settings. Some DBS Control settings, especially those related to block-level compression, affect space usage.
The following query can be used to show both the used and available space for each database
SELECT DatabaseName,SUM(CurrentPerm), SUM(MaxPerm) FROM DiskSpace GROUP BY 1 WITH SUM(CurrentPerm),SUM(maxperm) ORDER BY 1;
If SUM(CurrentPerm)/SUM(MaxPerm)>80%, the available free space on the system is already below the recommended threshold for migration. If the migration involves a hash function change, the available free space required depends on the size of the tables being restored and could require more than 20% available free space. It is not just large databases that can run out of space.
Fallback Considerations
If the source system contains non-fallback tables and the destination system requires all migrated tables become fallback tables, the max permspace on the destination system should add a minimum 100% of permspace consumed by non-fallback table on the source system.
SELECT SUM(DataBaseSpace.CurrentPermSpace) (NAMED CurrentPerm, FORMAT '-,---,---,---,---,---,---,--9') FROM DBC.Dbase, DBC.DataBaseSpace, DBC.TVM WHERE DataBaseSpace.TableID <> '000000000000'XB AND DataBaseSpace.TableID = TVM.tvmid AND TVM.DatabaseId = Dbase.DatabaseId AND TVM.TableKind NOT IN ('G','M','V') AND TVM.ProtectionType = 'N';
Block-Level Compression Considerations
If the source and destination systems have different DBS Control settings relating to block-level compression (BLC), and the migration job is not set up to overwrite BLC DBS Control settings when data is restored to the destination system, the permspace consumed by tables will be different on the two systems.
An extreme case would be if the source system uses BLC to compress all tables, but the destination system does not compress any. In this case, the same set of tables may consume 250% of the permspace on the destination system that they consumed on the source system. (This is based on Teradata Engineering estimates that the typical reduction in space from using BLC is about 60% per table (tables become, on the average, about 2.5 times smaller). The reduction rate varies, depending on user data.