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:
Rate this page:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.