15.00 - Before Starting Reconfiguration - Teradata Database

Teradata Database Support Utilities

Teradata Database

Before Starting Reconfiguration

Before starting Reconfiguration:

  • 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 on Vproc Manager, see Utilities: Volume 2 (L-Z).
  • Ensure adequate freespace is available for reconfiguration workspace. Depending on the number of fallback tables in existence, changes in DBS clustering, and changes in hash bucket size, reconfig can require a significant amount of freespace to distribute data rows. This involves temporarily rewriting the rows to a new location.
  • NoPI tables will be skewed after a reconfiguration that changes the number of AMPs. To help smooth out the data skew, these tables should be copied to a PI table, then back to a NoPI table after the reconfiguration. To prevent skewing of NoPI table data during reconfiguration, copy NoPI table rows into PI tables and delete the rows from the NoPI tables prior to reconfiguration. Reinsert the rows into NoPI tables after the reconfiguration.
  • Note: Column partitioned tables and column partitioned join indexes are NoPI tables.

  • Specify the order in which Reconfiguration should process tables during the redistribution and deletion phases of reconfiguration.
  • 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.
  • The ProcessOffline field indicates whether the table should be redistributed offline only, when logons are disabled and the system is quiescent. It can be Y or N. The default is N.
  • 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 no ordering information is specified, tables are processed in table ID order.

    Use the following strategies to choose the order of processing:

  • Redistribution ordering
  • The goal of ordering tables for the redistribution phase of reconfiguration is to minimize the processing overhead by minimizing the use and handling of the reconfiguration change log. Consequently, insert table names into the DBC.ReconfigRedistOrderV in the following order:

  • Large, historical tables that are rarely modified should be redistributed first.
  • Large tables that have a small number of rows which undergo more frequent modification should be redistributed next.
  • Lastly, redistribute small tables that are frequently modified.
  • Note: Tables undergoing large-scale changes should not be redistributed during online reconfiguration. Flag these tables for offline redistribution by setting the ProcessOffline field in the row to Y when the table name is inserted into the redistribution list.

  • Deletion ordering
  • During the deletion phase, Reconfiguration deletes duplicate copies of rows that have been redistributed. 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. This helps to prevent them from prolonging the reconfiguration.

    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 Chapter 4: “Reconfiguration Estimator (reconfig_estimator).”

  • If the entire reconfiguration is to be done offline, with users logged off and Teradata Database in a quiescent state (no activity):
  • Run the CHECK command of the CheckTable utility at the PENDINGOP level to verify that no tables are in pending status:

    Note: 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 on the CheckTable Utility, see Utilities Volume 1.

  • Ensure that online archive logging is not enabled for any database object.
  • Offline 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 offline reconfiguration is complete logons must be explicitly enabled again.
  • If the first portion of the reconfiguration is to be performed online (while users remain logged on to Teradata Database):
  • Run the CHECK command of the CheckTable utility at level two to validate the integrity of table data structures: