Identifying outstanding in-place alters

Technote (FAQ)


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:

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

11.70

Operating system(s):

AIX, HP-UX, Linux, Mac OS X, Solaris, Windows

Reference #:

1599602

Modified date:

2012-10-09

Translate my page

Machine Translation

Content navigation