informix-table-reorganizationOnline Defragmentation

Do you have large tables that contain too many extents but haven’t been able to schedule a maintenance window to reorganize? With the release of Informix 11.70, IBM introduced a new defragmentation utility that lets you reorganize a table or index online while the table is in use.

In a blog post that I wrote a couple of years ago, “Informix Extents: Sizing and Performance“, I explained the importance of managing the number of extents allocated to tables and indexes. Prior to Informix 11.70, reorganizing a table required exclusive access and meant a disruption to service. This often results in tables getting out of hand because there is no convenient time to reorganize. There is now a sysadmin function that allows you to defragment a table or index in the same way you might defrag a disk in Windows.

Informix Table and Index

You can use the following query to determine the number of extents for your tables and indexes:

database sysmaster;

select  dbsname,
        tabname,
        count(*) num_of_extents,
        sum( pe_size ) total_size
from systabnames, sysptnext
where partnum = pe_partnum
group by 1, 2
order by 3 desc, 4 desc;

Alternatively, to see the fragments for a given table, you can run oncheck –pt database:table, as shown below.

Prior to defragment:

informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data

TBLspace Report for bjsdb:informix.data

    Physical Address               4:7457
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 0
    Number of extents              8
    Current serial value           48469
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               512
    Number of pages allocated      3584
    Number of pages used           3233
    Number of data pages           3232
    Number of rows                 48468
    Partition partnum              4194461
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            4:9040          32         32
                   32            4:9076          32         32
                   64            4:9116         192        192
                  256            4:9324         128        128
                  384            4:9484         256        256
                  640            4:9804         768        768
                 1408           4:10700        1408       1408
                 2816           4:12236         768        768

                  Index  100_1 fragment partition datadbs1 in DBspace datadbs1

    Physical Address               4:7458
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 1
    Number of extents              8
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              4
    Next extent size               128
    Number of pages allocated      384
    Number of pages used           320
    Number of data pages           0
    Number of rows                 0
    Partition partnum              4194462
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            4:9036           4          4
                    4            4:9072           4          4
                    8            4:9108           8          8
                   16            4:9308          16         16
                   32            4:9452          32         32
                   64            4:9740          64         64
                  128           4:10572         128        128
                  256           4:12108         128        128

A good starting place for reorganization would be any object with 50 or more extents. To defragment the table, you can run “execute function task” in the sysadmin database, specifying “defragment” with the database and table name or the partition number. For example, to defragment the data table in the bjsdb database, you would type the following:

execute function task(“defragment”,”bjsdb:informix.data”);

The results of the defragment can be seen in the next output from oncheck.

After table defragment:

informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data

TBLspace Report for bjsdb:informix.data

    Physical Address               4:7457
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 0
    Number of extents              1
    Current serial value           48469
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               512
    Number of pages allocated      3584
    Number of pages used           3233
    Number of data pages           3232
    Number of rows                 48468
    Partition partnum              4194461
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0           4:13004        3584       3584

         Index  100_1 fragment partition datadbs1 in DBspace datadbs1

    Physical Address               4:7458
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 1
    Number of extents              8
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              4
    Next extent size               128
    Number of pages allocated      384
    Number of pages used           320
    Number of data pages           0
    Number of rows                 0
    Partition partnum              4194462
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            4:9036           4          4
                    4            4:9072           4          4
                    8            4:9108           8          8
                   16            4:9308          16         16
                   32            4:9452          32         32
                   64            4:9740          64         64
                  128           4:10572         128        128
                  256           4:12108         128        128

In order to defragment an index using this function, you need to use the index name or partition number, which can be found in the “oncheck –pt” output above.

execute function task(“defragment partnum”,”4194462″);

After index defragment:

informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data

TBLspace Report for bjsdb:informix.data

    Physical Address               4:7457
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 0
    Number of extents              1
    Current serial value           48469
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               512
    Number of pages allocated      3584
    Number of pages used           3233
    Number of data pages           3232
    Number of rows                 48468
    Partition partnum              4194461
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0           4:13004        3584       3584

         Index  100_1 fragment partition datadbs1 in DBspace datadbs1

    Physical Address               4:7458
    Creation date                  01/13/2016 15:56:21
    TBLspace Flags                 802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               125
    Number of special columns      0
    Number of keys                 1
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              4
    Next extent size               128
    Number of pages allocated      384
    Number of pages used           320
    Number of data pages           0
    Number of rows                 0
    Partition partnum              4194462
    Partition lockid               4194461

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            4:9804         384        384

The “execute function” commands can be put in a sql file and then run from the command line, or typed directly into dbaccess. I recommend the former as it could take some time to execute against a large table.

Example:

vi defrag_data.sql:
execute function task(“defragment”,”bjsdb:informix.data”);

Then to run the command in background:
dbaccess sysadmin defrag_data > defrag.out 2>&1 &

You can watch the progress of the defragment statement by running “onstat –g defragment” from the command line. You will need to run update statistics against the table after defragmentation to update distributions for the optimizer.

I have found this function to be very useful in defragmenting tables that otherwise could not be reorganized without a maintenance window. It can also be scheduled to run automatically using the Informix scheduler, but that’s a topic for another blog post. Stay tuned!

Share This