A script that finds outstanding in-place alters in a database

Question

How do you find outstanding in-place alters in a database

Answer

Download and run the attached script. It will find and display the tables with outstanding in-place alters in the database. Here is the command line syntax:

inplacealter.ksh -d database_name -t [ALL| table_name ] [-o [ver|sum]]

-d database      the name of the database (required) 
-t [ALL|table]   the name of the table (default is all tables) 
-o ver|sum       print either verbose or summary (default) report

The verbose report is similar to the oncheck -pT output. The summary output prints out whether the table has any outstanding in-place alters. The utility generates two output files: 

• inplacealter.out -- in-place alter information
• inplacealter.update -- SQL dummy update statements

The advantages of this script are: 

• It places no locks on any tables, so it can be run anytime by the DBA
• It doesn't run the expensive oncheck -pT to gather the statistics, so it is extremely fast even in the busiest of times on the server.
• It creates a seperate script for the DBA with update statements for the table to remove the outstanding in-place alters. The update statement chooses a non-index column to be updated so that the engine does a sequential scan to select all pages used by the table.
• It is written is Korn Shell and awk which are widely available on UNIX systems.

Warning: This script is not supported by Informix Technical Support. If it does not work on your system ask your system administrator for help debugging it. It is written in Korn Shell and may not work in other shells. The generated script containing the update statements can be run as-is; however, this script/updates have a direct impact on database logging and performance (time for the update(s) to complete). Serious consideration needs to be given to both these issues if any of tables referenced in the update statetments are logged and/or are very large.

http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923