View Only
Expand all | Collapse all

In-place alters and rolling back to a previous version of Informix

  • 1.  In-place alters and rolling back to a previous version of Informix

    Posted Tue November 22, 2022 04:30 PM

    I understand that if we want to have the ability to rollback to a previous version of Informix after upgrading to Informix 14, then we need to perform in-place alters for certain tables that need them.


    Here is a link to an article about how to do in-place alters:


    In my situation I have 150 tables that need the in-place alters. Some are small tables that are easy to address. Other tables have large numbers of rows and have special triggers that would have to be disabled in order to perform the work.


    I'm wondering if I could do most of the in-place alters before the upgrade to Informix 14. Then, if I discovered an issue that resulted in management telling me to roll back to the previous version of Informix, would I be able to do the remaining in-place alters while Informix 14 was installed and then do the rollback?



    John Dargan, Database Administrator II - Information Technology

    LAURA E. ROTH, Clerk of the Circuit Court

    Seventh Circuit, Volusia County, Florida







  • 2.  RE: In-place alters and rolling back to a previous version of Informix

    IBM Champion
    Posted Tue November 22, 2022 05:16 PM

    That article is old and specific to v11. In version 12.10 and later there is an SQL API function to complete outstanding in-place alters:

    execute function task( 'table update_ipa', 'tablename', 'databasename', 'owner' );


    execute function task( 'fragment update_ipa', partnum );

    Optionally you can add the 'parallel' keyword to the command. 

    If you have a lot of tables to do that for, you can try using my fix_ipa utility (reach out to me directly and I'll send you the source). It tends to be a bit faster than the API function and it. Also fix_ipa will identify what tables need to be updated and by default process them all. Fix_ipa also tends to be less intrusive as it updates small batches of pages and commits that batch. It also has a "list mode" (-l) that finds the tables that have outstanding IPAs and lists them for you so you don't have to go through executing complex queries yourself. Here is the fix_ipa usage:

    $ fix_ipa -?

    fix_ipa - Complete outstanding in-place alters for all databases and tables.

    Usage: fix_ipa
                   <-h host>
                   <-d database> [-t table | -P partnum]
                   <-w waittime>
                   <-c commit_count>
                   <-p PDQRPIORITY>
           fix_ipa -l
           fix_ipa -?
           fix_ipa -V

           -l - List mode. Just list the tables that need fixing.
           -d - Specify a database to process. Default: all.
           -t - Specify a table to process. Default: all in selected database.
           -P - Specify a partnm to process. Default: all in selected database.
           -h - Host to update Default: $INFORMIXSERVER.
           -S - Silent mode.  Supress messages.
           -V - Print version information and exit.
           -c - Commit after commit_count rows updated. Default: 10,000 rows.
           -w - # of seconds to wait for locked rows to be released (if < 0 wait
                forever).  Default 10.
           -p - PDQPRIORITY to set for the task.


    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.