defragment argument: Dynamically defragment partition extents (SQL administration API)

Use the defragment argument with the admin() or task() function to defragment tables or indexes to merge non-contiguous extents.

Defragmenting a table brings data rows closer together to avoid partition header page overflow problems, and can improve performance.

Before you defragment a partition you should review the Partition defragmentation.

Syntax

You can specify either the defragment argument or defragment partnum argument using the following syntax:


>>-EXECUTE FUNCTION--+-admin-+---------------------------------->
                     '-task--'   

>--(--"--defragment--"--,----"--database--:--owner--.--table--"----)--;-><


>>-EXECUTE FUNCTION--+-admin-+---------------------------------->
                     '-task--'   

                                   .-,-------------------.         
                                   V                     |         
>--(--"--defragment partnum--"--,----partition_number--"-+--)--;-><

Element	Description	Key considerations
`database`	Name of the database that includes the table or index that you want to defragment.
`owner`	User ID of the owner of the table.
`table`	Name of the table to defragment.
`partition_number`	One or more partition numbers to defragment.	Use a comma-separated list of partition numbers to specify more than one partition.

Usage

Use the defragment argument to defragment specific tables. Use the defragment partnum argument to defragment one or more specific disk partitions.

Information about defragmentation is stored in shared memory. Use the oncheck -pt and -pT: Display tblspaces for a Table or Fragment command to display information about the number of extents for a specific table or fragment. Use the onstat -g defragment command: Print defragment partition extents.

If the defragment request reduces the number of extents by at least 1 extent, the request returns 0 (success), even if there are many extents in the partition.

If a partition has a single extent, the defragment request returns 0 to indicate that the request was a success, even though no extents were merged.

Examples

To defragment the customer table in the stores_demo database, use either of the following functions:

EXECUTE FUNCTION task(“defragment”,“stores_demo:informix.customer");

EXECUTE FUNCTION admin(“defragment”,“stores_demo:informix.customer");

To defragment an index, you must specify the partition number for the index, as in these two function examples:

EXECUTE FUNCTION task(“defragment partnum”,“2097154”);

EXECUTE FUNCTION admin(“defragment partnum”,“2097154”);

To defragment a list of partitions, use either of the following functions:

EXECUTE FUNCTION task(“defragment partnum”, “16777217,28477346");

EXECUTE FUNCTION admin(“defragment partnum”, “16777217,28477346");