Abstract
There is a standard recommendation to scan tables and indexes using the Informix “oncheck” tool for any integrity issues before an in-place database server upgrade that might otherwise make it fail.
What is less well known is that it should be run afterwards in case problems have been caused by the upgrade. This article covers a specific scenario where this is necessary and how it can be more easily resolved.
Content
The IBM documentation page “Upgrading Informix (in-place migration)” describes the process as follows:
“In-place migration upgrades Informix directly to the current version by installing the product in a new directory on the same computer, copying a few configuration files, and starting the new server to automatically convert your database data.”
For $ONCONFIG and ALARMPROGRAM, the actual process should of course be that you reapply custom settings to copies of the new standard “onconfig.std” and “alarmprogram.sh” files so that they contains any additional content that comes with the new version.
“Preparing for migration” contains further links on the usual main steps:
- Reviewing changes in Informix product functionality.
- Checking and configuring available space.
- Configuring for recovery of restore point data in case an upgrade fails.
- Saving copies of the current configuration files.
- Closing all transactions and shutting down the source database server.
- Initiating fast recovery to verify that no open transactions exist.
- Verifying the integrity of the data.
- Verifying that the database server is in quiescent mode.
- Making a final backup of the source database server.
- Verifying that the source database server is offline.
In reality, to minimise down-time during the upgrade, you will want to perform step 7 above in advance as it might take quite some time. The following is a convenient shell script “oncheck-all.sh” to apply the recommendations:
If any problems with tables are found, they are repaired with the “-y” option as that is fairly light-weight, whereas that option on indexes requires either an exclusive lock on the table or potentially a very large number of row locks, either of which would adversely affect a running system.
You can first run the script alone to see what commands will be run, then actually execute them as follows, using “nohup” so that the background process survives and continues if the terminal connection is lost, redirecting all output to a file:
Much of the output is ignorable, so you might want to filter it with another script like this (modify as preferred):
“Completing required post-migration tasks” contains further links on the usual main steps:
- Optionally update statistics on your tables after migrating.
- Review client applications and registry keys.
- Verify the integrity of migrated data.
- Back up Informix after migrating to the new version.
- Tune the new version for performance and adjust queries.
When performing step 3 above after an upgrade, you may well get messages such as the following the first time you run “oncheck-all.sh”:
This is due to defect APAR IC98053 when upgrading from versions earlier than 11.70.xC7W2 or 12.10.xC2 with MAX_FILL_DATA_PAGES 1:
https://www.ibm.com/support/pages/oncheck-reporting-data-pages-more-or-less-full-indicated-bitmap
It advises running “oncheck -cD -y” (as in our script) after the migration:
“If not fixed it would be very hard to tell real problems from these in the future. This not only would make those warnings go away, but potentially also would make a lot of existing pages’ unused space available for new rows.”
Conclusion
This article shows the importance of running “oncheck” both before and after an in-place upgrade to fix known and new migration defects. No additional down-time is needed, and the provided script makes it easy to do.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
