Identifying outstanding in-place alters

In IBM Informix 11.70 there is a requirement for migration reversion: "In-place ALTER TABLE statements performed in the new version of the server must not be outstanding against any table."

1) Run 'oncheck -pt' command to identify which tables were altered in the current version. Qualifying tables must have tblspace flag 0x800000 set. For example:

# oncheck -pt tdb:tab1
TBLspace Report for tdb:dmitriyr.tab1

 Table fragment partition par1 in DBspace dbs1

    Physical Address               2:5
    Creation date                  06/25/2012 11:58:16
    TBLspace Flags                 800802     Row Locking
                                              TBLspace use 4 bit bit-maps
<...>

As an alternative to 'oncheck -pt' you may run the following query against the Informix sysmaster database to find all qualifying tables:

SELECT ta.dbsname, ta.tabname, pt.partnum, HEX(pt.flags) flag
FROM sysmaster:systabnames ta, sysmaster:sysptnhdr pt
WHERE ta.partnum = pt.partnum
AND BIT_AND(HEX(pt.flags),'00800000'::BINARYVAR) = '00800000';

Sample result:

dbsname       tdb
tabname       tab1
partnum       2097154
flag          0x00800802

In above examples tblspace flag value is 0x800802. It is clear that value 0x800000 is set here.

2) Run 'oncheck -pT <database>:<table>' against those tables identified in p.1 to find which tables have outstanding in-place alters.

You must look at "Home Data Page Version Summary" section. For example:

<...>
    Home Data Page Version Summary

                 Version                                 Count

                       0 (oldest)                           25
                       1 (current)                          15

<...>

In the above example there are 25 pages to be altered to a new version. That means that a dummy update is required to be executed against that table. The steps to perform a dummy update is explained in detail within the migration guide:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.mig.doc/ids_mig_274.htm