IBM Support

Identifying outstanding in-place alters

Question & Answer


Question

How to determine which tables require dummy updates in order to eliminate outstanding in-place alters before reversion to an earlier version?

Cause

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."

Answer

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';

Sample result:
    dbsname       tdb
    tabname       tab1
    partnum       2097154
    flag          0x00800802

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

                     Version                                 Count

                           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:

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.70","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
03 June 2021

UID

swg21599602