Identifying outstanding in-place alters

Technote (FAQ)

Question

How to determine which tables require dummy updates in order to eliminate outstanding in-place alters before reversion to an earlier version?

Cause

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."

Answer

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

http://www-01.ibm.com/support/docview.wss?uid=swg21599602