How to determine which tables require dummy updates in order to eliminate outstanding in-place alters before reversion to an earlier version?
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';
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
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: