Example of configuring for the automatic addition of more space

This example shows how you can fully configure for the automatic addition of more space by changing some configuration parameter settings, changing the frequency of a task that monitors low storage, and specifying information for extendable spaces and chunks.

To configure for the automatic addition of more storage space:

  1. Add entries to the 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");
  2. Mark some chunks in unmirrored dbspaces and temporary dbspaces as extendable so that the server can extend the chunks if necessary in the future.

    For example, specify that chunk 12 can be extended:

    EXECUTE FUNCTION task("modify chunk extendable", "12");

    You can also change the mark to of an extendable chunk to not extendable. For example, specify that chunk number 10 cannot be extended:

    EXECUTE FUNCTION task("modify chunk extendable off", "10");
  3. 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. Specify either:
    • A value from 1 to 50 for a percentage,
    • A value from 1000 to the maximum size of the chunk in KB

    If an individual storage space fills beyond this threshold that you define and remains that full until the space-monitoring task (mon_low_storage) next runs, the server attempts to expand the space by extending an extendable chunk or by using the storage pool to add a chunk.

    For example, suppose the SP_THRESHOLD value is 5.5, which the server treats as 5.5 percent. If a space runs low on free pages, and the free space percentage falls below 5.5 percent and remains below that level until the mon_low_storage task runs next, that task attempts to expand the space. If SP_THRESHOLD is set to 50000 and a space has fewer than free 50000 KB, that space is expanded the next time mon_low_storage runs.

  4. Optional: Change how often the mon_low_storage task runs. This task periodically scans the list of dbspaces to find spaces that fall below the threshold indicated by SP_THRESHOLD configuration parameter.
    For example, to configure the task to run every 10 minutes, run the following SQL statements:
    DATABASE sysadmin;
    UPDATE ph_task set tk_frequency = INTERVAL (10) MINUTE TO MINUTE
     WHERE tk_name = “mon_low_storage”;
  5. Optional: Change the value of the SP_WAITTIME configuration parameter, which specifies the maximum number of seconds that a thread waits for a space to expand before returning an out-of-space error.
  6. Optional: Change two sizes that are associated with expanding a storage space:
    • The extend size, which is the minimum size that is used when extending a chunk in a dbspace, temporary dbspace, or the plogspace
    • The create size, which is the minimum size that is used when creating a new chunk in a dbspace, temporary dbspace, sbspace, temporary sbspace, or blobspace that is not a mirror space

      For example, the following command sets the create size and extend size to 60 MB and 10 MB, respectively, for space number 3:

      EXECUTE FUNCTION task("modify dbspace sp_sizes",
       "3", "60000", "10000");

After you configure for the automatic expansion of a storage space, you can also manually expand the space or extend a chunk in the space, as necessary.