How to reorganize a table using "oncheck"

Question

Using IDS 7.31.xD1 or higher you can use oncheck -me to reorganize your tables.

Answer

Starting in 7.31.xD1, a new feature was introduced in oncheck. It allows you to reduce the number of extents of a table.

How to enable environment variable RASHELP:
To use this new feature the environment variable RASHELP must be set to 1 before we start the database server. 
Example:
In ksh or bash:
export RASHELP=1
oninit
In csh:
setenv RASHELP 1
oninit

How to get the new usage of oncheck:
Then we can see additional syntax of oncheck: 

oncheck --

ONCHECK
Usage:  oncheck {-cCheckOption | -pPrintOption | -mMergeOption}
                [-y | -n] [-q]
                [ { database[:[owner.]table[,fragdbs|#index]] | TBLspace number
                    | Chunk number }
                  { rowid | page number | target number of extents } ]
...

-m - MergeOption
e - Merge of TBLspace/partition extents
[TBLspace/partition number and target number of extents]
...

partnum
The partition number for the target table.
extents
The number of surviving extents.

How to get partnum:
1) Run command oncheck -pt database:tablename
2) Find the value of Partition partnum
Example:
oncheck -pt stores:customer
...
Partition partnum 2097183
...
Or 
1) Choose the database your table reside in. Then run appropriate sql to get the value from the syscatalog table
Example:
select tabname, partnum from systables where tabname="customer"
tabname partnum

customer 2097183

Partition partnum or partnum is we are looking for.

How to reorganize the table by oncheck:
Example:

Suppose that table t1 has 2 extents and we want to reorganize them in just one extent:

$ oncheck -pt dbname:t1

TBLspace Report for dbname:informix.t1

    Physical Address               103103    
    Creation date                  07/07/2003 15:50:08
    TBLspace Flags                 801  Page Locking
                                        TBLspace use 4 bit bit-maps
    Maximum row size               2000      
    Number of special columns      0         
    Number of keys                 0         
    Number of extents              2         
    Current serial value           1         
    First extent size              4         
    Next extent size               4         
    Number of pages allocated      8         
    Number of pages used           6         
    Number of data pages           5         
    Number of rows                 5         
    Partition partnum              1048744   
    Partition lockid               1048744   

    Extents                       
         Logical Page  Physical Page        Size
                    0         1031bb           4
                   4         1031c3           4


$ oncheck -me 1048744 1

TBLspace merge extents report for TBLspace 0x1000a8

    Number of extents
        Original      2
        Target        1

        Current       1
        Freed         2

$ oncheck -pt dbname:t1

TBLspace Report for dbname:informix.t1

    Physical Address               103103    
    Creation date                  07/07/2003 15:50:08
    TBLspace Flags                 801  Page Locking
                                        TBLspace use 4 bit bit-maps
    Maximum row size               2000      
    Number of special columns      0         
    Number of keys                 0         
    Number of extents              1         
    Current serial value           1         
    First extent size              4         
    Next extent size               4         
    Number of pages allocated      8         
    Number of pages used           6         
    Number of data pages           5         
    Number of rows                 5         
    Partition partnum              1048744   
    Partition lockid               1048744   

    Extents                       
         Logical Page  Physical Page        Size
                    0         1031c7           8 

http://www-01.ibm.com/support/docview.wss?uid=swg21141582