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.
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.
... WHERE {id_column} BETWEEN {low_value} AND {step_value}
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.
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 Version
section.
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>>