Resolve outstanding in-place alter operations

Resolving outstanding in-place alter operations is not a requirement before converting to a higher database server version. However, if it becomes necessary to revert to a previous version you must resolve new outstanding in-place alter operations first.

An in-place alter operation is outstanding when data pages still exist with the prior definition, a state that can be detected with the oncheck -pT command. An in-place alter operation is new if the ALTER statement is executed in the higher database version. Carryovers—outstanding in-place alter operations that existed prior to a conversion—need not be resolved before a subsequent reversion to the earlier server version.

If the reversion process detects new outstanding in-place alter operations, reversion fails and the message log will contain a list of all tables whose in-place alter operations must be resolved before the reversion will succeed.

If you are reverting from version 12.10.xC4 or later, you can remove in-place alter operations by running the admin( ) or task( ) SQL administration command with the table update_ipa or fragment update_ipa argument. You can include the parallel option to run the operation in parallel. For example, the following statement removes in-place alter operations in parallel from a table that is named auto:

EXECUTE FUNCTION task('table update_ipa parallel','auto');

If you are reverting from an earlier version of 12.10, you can resolve outstanding in-place alter operations by running sample UPDATE statements. Sample UPDATE statements force any outstanding in-place alter operations to complete by updating the rows in the affected tables. To generate a sample UPDATE statement, create an UPDATE statement in which a column in the table is set to its own value. This forces the row to be updated to the latest schema without changing column values. Because the database server always alters rows to the latest schema, a single pass through the table that updates all rows completes all outstanding in-place alter operations.

The sample UPDATE statement differs from a standard UPDATE statement because it does not change the data. A standard UPDATE statement usually changes the value of the affected row.

For example, to create a sample update, specify:

UPDATE tab1 SET col1=col1 WHERE 1=1 ;

You must ensure that the column selected is a numeric data type (for example, INTEGER of SMALLINT) and not a character data type.

If a table is large, a single update of the whole table can cause a long transaction. To avoid a long transaction, update the table in pieces, by ranges of some column, with this statement:

 ... WHERE {id_column} BETWEEN {low_value} AND {step_value}

For example, specify:

UPDATE tab1 SET col1=col1 WHERE col1 BETWEEN 1 AND 100; 
UPDATE tab1 SET col1=col1 WHERE col1 BETWEEN 101 AND 200;

Ensure that the UPDATE statements include the entire data set.

If the table is replicated with Enterprise Replication, the database server replicates all updated rows unnecessarily. To avoid replication, update the table as follows:

BEGIN WORK WITHOUT REPLICATION;
     ...
     COMMIT WORK;

When all the pending in-place alter operations are resolved, run the oncheck -pT command again for each table. In the output of the command, check information in the Versionsection. The number of data pages should match with current version. Also, all other table versions should have count=0 for the number of data pages that the version is accessing.

For example, if you run the oncheck -pT testdb:tab1 command after outstanding in-place alter operations are resolved, you might see information similar to the information in this segment of sample output:

TBLspace Report for testdb:root.tab1

  Physical Address               1:860
  Creation date                  06/23/2011 14:23:08
  TBLspace Flags                 800801     Page Locking
                                            TBLspace use 4 bit bit-maps
  Maximum row size               29        
  Number of special columns      0         
  Number of keys                 0         
  Number of extents              1         
  Current serial value           1         
  Current SERIAL8 value          1         
  Current BIGSERIAL value        1         
  Current REFID value            1         
  Pagesize (k)                   2         
  First extent size              8         
  Next extent size               8         
  Number of pages allocated      8         
  Number of pages used           4        
  Number of data pages           3
   << Number of data pages used is 3 >>        
  Number of rows                 6         
  Partition partnum              1048981   
  Partition lockid               1048981   

  Extents                       
       Logical Page     Physical Page     Size Physical Pages
                  0            1:1895        8          8
TBLspace Usage Report for testdb:root.tab1

  Type                  Pages      Empty  Semi-Full       Full  Very-Full
  ---------------- ---------- ---------- ---------- ---------- ----------
  Free                      4
  Bit-Map                   1
  Index                     0
  Data (Home)               3
                   ----------
  Total Pages               8

  Unused Space Summary

     Unused data slots                               177

  Home Data Page Version Summary

    Version                                 Count

      3 (oldest)                           0  
          << Other version should show data page count=0>>
      4                                    0
          << Other version should show data page count=0>>
      5 (current)                          3
          << Current should always match the number of data pages>>