Additional Space on Destination System | Teradata Vantage - 17.00 - 17.05 - Plan Additional Space on Destination System - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Advanced SQL Engine Node Software Migration Guide

Advanced SQL Engine
Teradata Database
Release Number
Release Date
June 2020
Content Type
Publication ID
English (United States)

Teradata recommends you have at least 20% free permspace available on the destination system. Sometimes more permspace is required on the destination system than was required for the same database/user on the source system. You may need additional permspace on the destination system for the following reasons:

  • Use of BLC can cause space requirements to grow nearly 100% in extreme cases.
  • Use of ALLWAYS FALLBACK can cause space requirements to grow nearly 100% in extreme cases.
  • 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 has a copy of every table header.
  • Hash function changes may result in data skewing related to NOPI tables or PPI tables.

    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 is copied into a buffer to be sent to the new AMP. The space for the original copy of the row is not freed until the entire table has been restored and redistributed.

  • The source and destination systems have different DBS Control settings. Some DBS Control settings, especially those related to block-level compression, affect space usage.

It is not only large databases that can run out of space. Use the following query on the source system to show the used and available space for each database:

SELECT DatabaseName,SUM(CurrentPerm), SUM(MaxPerm)
WITH SUM(CurrentPerm),SUM(maxperm) ORDER BY 1;

If SUM(CurrentPerm)/SUM(MaxPerm)>80%, the available free space on the system is below the recommended minimum threshold for migration.

In any restore it is possible, due to the issues listed above, that the physical space (Current Perm) used by a user/database will exceed the allocated Max Perm for that user/database after the restore. If there is adequate physical storage available, DSA ignores space accounting during the restore operation. This allows the restore to complete successfully. A non-fatal 1196 will be reported:
Database USRP_32448 has exceeded it's logical space limit
but the restore will succeed. A system administrator must adjust the Max Perm for those users/databases before the system is turned over to end users or certain queries will abort with out-of-space errors.

Fallback Considerations

After sysinit is run on the destination system, it will default to ALWAYS FALLBACK. If the source system contains non-fallback tables and the destination system requires that all tables have fallback, the destination system will need to have sufficient space to hold a fallback copy of all of these tables. The max permspace on the destination system should at least double the permspace amount consumed by non-fallback tables on the source system.

Use the following query to calculate total permspace consumed by non-fallback tables 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';
The destination system will need to have at least this much more space than the source system for these tables.

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, uncompressed, may consume up to and even more than 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 compress to, on the average, about 2.5 times smaller than when they are not BLC-compressed.) The reduction rate varies, depending on user data.