Revalidating Table/Join Index Partitioning | ALTER TABLE | Vantage - Revalidating the Partitioning for Tables and Join Indexes With a HASH Function Partitioning Expression - Analytics Database - Teradata Vantage

During a cross platform migration process, the database regenerates the table headers for partitioned tables and join indexes after they are restored or copied to the new system.

If the new system either uses a different hashing algorithm or has 20-bit hash buckets rather than 16-bit hash buckets, a change in the value returned by a hash function can cause the partitioning expression to evaluate differently, and the regenerated table headers can cause incorrect query results to be returned from partitioned tables and join indexes when you specify a partitioning expression using the HASHBUCKET function. For information about the HASHBUCKET function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

You must revalidate the partitioning of any affected tables or join indexes using the following procedure.

1. Identify the partitioned tables and join indexes affected by this problem by running the pre_upgrade_prep.pl script prior to the migration. You should then run the hashbucket_ppi.rpt script afterward.

   The pre_upgrade_prep.pl script performs a SELECT request that finds and reports any tables or join indexes that must be revalidated after the migration occurs.

   If you want to submit a standalone SELECT request to return the rows that must be revalidated, you can submit the following request, which duplicates the request the pre_upgrade_prep.pl script submits.

        SELECT TRIM(DataBasenamei)||'.'||TRIM(TVMNamei), TableKind
        FROM   DBC.TableConstraints AS tc JOIN DBC.TVM
                                          ON tc.TVMID=TVM.TVMID
                                          JOIN DBC.Dbase
                                          ON tc.DBaseId=Dbase.DatabaseId
        WHERE  ConstraintType = 'Q'
        AND    UPPER(TableCheck) LIKE '%HASHBUCKET%'
        ORDER BY 1;

   TO revalidate this database object …	GO to this step …
   partitioned table	2.
   partitioned join index	5.

2. Revalidate the rows in each of the data tables identified by pre_upgrade_prep.pl (but not the join index rows).

   To revalidate data tables, this revalidation requires the null partition handler specified by save_table to ensure that rows are stored in the correct partitions.

   You cannot specify a WITH DELETE or WITH INSERT [INTO] save_table null partition handler clause for a partitioned join index.
   The script uses the following procedure for partitioned tables:

   1. Create a save table using the DDL for the affected partitioned table, but do not specify any partitioning or secondary indexes for save_table.

      This step only applies to revalidating a partitioned table. You cannot use a null partition handler to revalidate a partitioned join index.

   2. Submit the following ALTER TABLE request to revalidate the partitioning for table_name.

      ALTER TABLE database_name.table_name
      REVALIDATE
      WITH DELETE;

      or

      ALTER TABLE database_name.table_name
      REVALIDATE
      WITH INSERT INTO save_table;

      database_name

      Specifies the name of the database or user that contains table_name.

      table_name

      Specifies the name of the affected partitioned table.

      save_table

      Specifies the name of the table created to save the null partitions that are created when you revalidate the partitioned table.

3. The action taken for this step depends on whether save_table is populated with rows or not.

   IF save_table …	THEN …
   is not populated with rows	the revalidation process for the data table is complete.
   is populated with rows	you have a choice of how to handle each row in save_table. Redefine the partitioning of the original table so you can reinsert the row. or Save the row in a separate table.

4. After successfully handling each of the rows in save_table, drop the null partition handler save table.
5. Revalidate partitioned join index rows by dropping and then recreating each affected partitioned join index.