Example of minimally configuring for and testing the automatic addition of more space

To minimally configure for and test the automatic addition of more space:

1. Create a dbspace.

   For example, create a dbspace named expandable_dbs and allocate an initial chunk using the first 10000 KB of a cooked file named /my_directory/my_chunk, as follows:

   onspaces -c -d expandable_dbs -p /my_directory/my_chunk -o 0 -s 10000

2. Fill the dbspace.

   For example, fill the dbspace without loading a row of data. Instead, create a table and allocate a large set of contiguous free pages to the first extent, as follows:

   CREATE TABLE large_tab (col1 int) IN expandable_dbs EXTENT SIZE 10000000;

   You can monitor the free pages in your chunks by using the onstat -d command . If your dbspace is full, you receive out-of-space errors when attempting to create and load data into another new table.

3. Add an entry to the Informix® storage pool.

   For example, add the $INFORMIXDIR/tmp directory to the storage pool, as follows:

   DATABASE sysadmin;
   EXECUTE FUNCTION task("storagepool add", "$INFORMIXDIR/tmp",
    "0", "0", "10000", "2");

4. In the SP_THRESHOLD configuration parameter, set a threshold for the minimum amount of free KB that can exist in a storage space before Informix® automatically runs a task to expand the space.
5. Create and load new tables into your database.

   Now, if a storage space becomes full, instead of receiving an out-of-space error, Informix® automatically creates a cooked file in the $INFORMIXDIR/tmp file and add a chunk to the expandable_dbs database using the new cooked file. As you continue to fill this chunk, the server automatically extends it. The server will always extend chunks if possible before adding new ones to a dbspace.

6. Reduce the free space in a storage space to test the value in the SP_THRESHOLD configuration parameter.

   Allocate enough pages in a storage space to reduce the free space so it is below the threshold indicated by SP_THRESHOLD. However, do not completely fill the space.

   You must see the space automatically expanded the next time that the mon_low_storage task runs.

7. Create an out-of-space condition.

   Allocate all pages in a storage space. Then try to allocate more pages. The allocation must be successful and you must not receive an out-of-space error.

   Informix® writes messages to the log whenever it extends or adds a chunk and marks new chunks as extendable.

   Run the onstat -d command to display all chunks in the instance. Look for extendable chunks, which are marked with an E flag. The command output shows that the server automatically expanded the space, either through the addition of a new chunk or by extending the size of an existing chunk.