16.10 - Before Starting Reconfiguration - Teradata Database

Teradata Database Support Utilities

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Configuration
featnum
B035-1180-161K

Before starting Reconfiguration, you should be aware of the following actions and considerations.

  • Verify that hardware is in place, properly configured, and online. Hardware configuration is detailed in the hardware service manuals for your Teradata Database system.
  • Use the Vproc Manager utility to ensure all AMP-to-PE vprocs are online and ready. For more information, see the “Vproc Manager (vprocmanager)” chapter of Utilities .
  • Ensure adequate free space is available for reconfiguration workspace. Depending on the number of fallback tables in existence and changes in DBS clustering, reconfig can require a significant amount of free space to distribute data rows. This involves temporarily rewriting the rows to a new location.
  • Ensure there are no tables using sparse maps that are children of contiguous maps that will be reconfigured. These tables must be either altered to use other maps for data distribution, or dropped.

Preventing and Correcting Skew

To prevent skewing of a NoPI table or join index (JI) during a reconfiguration to fewer AMPs:

  • If the data in a NoPI table does not need to be retained, delete the rows from the table prior to reconfiguration.
  • If the data in a NoPI table needs to be retained, copy the NoPI table into a PI table and delete the rows from the NoPI table prior to reconfiguration. Reinsert the rows into the NoPI table after the reconfiguration.
  • For a NoPI JI that is not empty (due to base table not being empty), if possible drop the JI prior to reconfiguration and recreate it after reconfiguration.

To smooth out the data skew that occurs for a nonempty NoPI table or JI after a reconfiguration to more AMPs (or fewer AMPs for a NoPI table for which rows were not deleted prior to the reconfig):

  • Copy a NoPI table to a new NoPI table using the HASH BY RANDOM option, then drop the old NoPI table after dropping any JIs and create new JIs as needed on the new NoPI table. Grant privileges on the new NoPI table as needed.
  • A nonempty NoPI JI should be dropped and recreated after the reconfiguration in order to remove skew.

To prevent skewing of a table or JI having a primary AMP index (a PA table or join index) during a reconfiguration to fewer AMPs:

  • If the data in a PA table does not need to be retained, delete the rows from the table prior to reconfiguration.
  • If the data in a PA table needs to be retained, copy the PA table into a PI table and delete the rows from the PA table prior to reconfiguration. Reinsert the rows into the PA table after the reconfiguration.
  • For a PA JI that is not empty (due to base table not being empty), if possible drop the JI prior to reconfiguration and recreate it after reconfiguration.

After reconfiguration, rows for a nonempty PA table are not necessarily assigned to the correct AMPs per the new configuration, and the data is skewed. Because of the incorrect distribution of the rows the PA table is marked for limited access. Only SELECTs are allowed on these tables.

  • To smooth out the data skew that occurs for a nonempty PA table or JI after a reconfiguration to more AMPs (or fewer AMPs for a PA table for which rows were not deleted prior to the reconfig) and to have rows distributed to the AMPs based on the PA, copy the PA table to a new PA table, then drop the old PA table after dropping any JIs and recreate JIs as needed on the new PA table. Grant privileges on the new PA table as needed.
  • Alternatively, you can alter an nonempty PA table to a NoPI table in order to quickly regain full access to the table. At some convenient later time, copy the NoPI table (that was formerly a PA table) to a new PA table, then drop the old NoPI table after dropping any JIs and recreate JIs as needed on the new PA table. Grant privileges on the new PA table as needed. This regains the PA and removes the skew.

A nonempty PA JI should be dropped and recreated after the reconfiguration in order to remove skew and correctly distribute the data.

Specifying the Order of Table Processing

Reconfiguration allows user specification of the order in which tables are processed during the redistribution and deletion phases of reconfiguration. During the deletion phase, Reconfiguration deletes duplicate copies of rows that have been redistributed. Teradata recommends explicit specification of this order. Although the rationale for specifying a processing order during these two phases is slightly different, the strategies are the same:
  • The goal of ordering tables for the redistribution phase of reconfiguration is to ensure that the largest tables are started first, while avoiding starting too many large redistribution jobs at the same time.
  • The goal of ordering tables for the deletion phase of reconfiguration is to begin the deletion of larger tables early in this phase, because it takes longer to delete larger tables.
In both cases, the preferred strategy is to process the largest table first, followed by a small or empty table, then continue interleaving large and small tables for processing. The ordering lists for both phases should match.

To specify the order of table processing during the redistribution phase, insert a row for each table to be redistributed into DBC.ReconfigRedistOrderV:

  • The DatabaseName and TableName fields specify a table.
  • The OrderNumber field should contain an integer, the value of which indicates when the table will be processed relative to other tables in the list.

During a reconfiguration, you can optionally specify that the Reconfiguration utility process only the tables listed in DBC.ReconfigRedistOrderV.

To specify the order of table processing during the deletion phase, create a separate list by inserting rows into DBC.ReconfigDeleteOrderV.

For more information on the structure of these tables, see Data Dictionary.

If the order for processing is not explicitly specified, tables are processed in table ID order.

Run the CheckTable Utility

Run the CHECK command of the CheckTable utility at the PENDINGOP level to verify that no tables are in pending status:
CHECK ALL TABLES AT LEVEL PENDINGOP SKIPLOCKS PRIORITY = H;
  • Do not use the CHECK command with the PARALLEL option when using the PENDINGOP option and with users logged on. A hang will occur. For more information, see the “CheckTable (checktable)” chapter in Utilities.
  • Ensure that online archive logging is not enabled for any database object.

    Reconfiguration will not run if online archive logging is enabled. Use the LOGGING ONLINE ARCHIVE OFF statement to turn off logging. You must specify the name of the database or the names of the tables for which logging is enabled. For more information, see SQL Data Definition Language.

  • Disable logons, and verify that the Teradata Database system is in a quiescent state. After the reconfiguration is complete logons must be explicitly enabled again.

Run the Reconfiguration Estimator Utility

Use the Reconfiguration Estimator utility prior to running the Reconfiguration utility. Reconfiguration Estimator validates the existence of the tables in the ordering lists. For more information on Reconfiguration Estimator, see Reconfiguration Estimator (reconfig_estimator).