Storage provisioning in Informix

Ease your DBA workload with automatic storage space management

Storage provisioning, a new feature in IBM® Informix® 11.7, makes storage management much easier. With this new functionality, DBAs don't have to monitor Informix in order to anticipate when an application might deplete storage. Now Informix can automatically allocate storage to containers whenever a database requires additional space. In this article, learn how to implement this functionality.

Aniket J. Adnaik (aniketa@us.ibm.com), Advisory Software Engineer, IBM

Aniket Adnaik photoAniket Adnaik has been working in IBM's Informix server development team for the past 10 years in Kernel, SQL, and Client SDK areas. His experience includes designing and implementing key features for Informix Dynamic Server's kernel component, including the storage provisioning feature in the latest Informix release (11.70.xC1).



John C. Lengyel (lengyel@us.ibm.com), Senior Software Engineer, IBM

John Lengyel began supporting Informix products in 1991, and has helped develop them as part of the kernel group since 1997.



03 March 2011

Also available in Chinese

Overview

The Database System Administrator (DBA) faces a number of challenges in managing the disk space allocated to an Informix server. Previous releases of Informix have helped considerably, adding features such as large chunks (Informix 9.40), dynamic log allocation (Informix 9.30), configurable tblspace tblspace (Informix 10.00), and more. However, Informix still generates an error if a dbspace fills up unexpectedly, and the DBA must manually add more space to alleviate the problem. A storage space may become under-allocated or over-allocated because of the difficulty in predicting the space requirements of database objects. Starting with Informix 11.70.xC1, the storage provisioning feature simplifies life for the DBA because Informix automatically allocates more disk space to a storage space when Informix needs it, without explicit (manual) intervention from the DBA. The storage provisioning feature adds a new term—a storage pool—to the concepts of Informix storage spaces and chunks. The storage pool is unused disk space that Informix can use when it needs more space. It is subject to the controls placed on the storage spaces that need the extra space, and subject to the controls placed on the storage pool entries themselves.

Terminology

Storage space refers to an Informix storage space that is a permanent or temporary dbspace, blobspace, or smartblob space.

Storage provisioning concepts

Extendable chunk

Earlier versions of Informix (prior to 11.70xC1) always had fixed-length chunks and there was no way you could add more space to an existing chunk if it ran of out of space. Starting with Informix 11.70xC1, when a chunk runs out of space in its current allocation of disk space, Informix can add more space to the existing chunk (up to the maximum size of a chunk) without needing to allocate a new chunk to the storage space that contains it. A chunk with such a characteristic is called an extendable chunk.

Expandable storage spaces

When a storage space runs out of space Informix can automatically expand the storage space. A storage space can be expanded automatically in two different ways:

  • One of the chunks can be extended (a chunk must be marked extendable), or
  • A new chunk can be added to the storage space

Informix will first try to extend one of the existing chunks that is marked extendable. If none of its current chunks are extendable, Informix can automatically allocate a new chunk to the storage space using the storage pool.

Storage pool

The storage pool is a collection of one or more entries that consist of a raw device, a cooked file, or a directory. It is unused disk space designated by the DBA that Informix can use when it needs space. Informix allows you to manually add space to this pool and automatically return space to this pool when a chunk is dropped. There is only one storage pool per Informix instance, and a storage pool cannot be shared across Informix instances.

Proactive and reactive triggering mechanism

Depending on your configuration, spaces can be automatically expanded before they run out of space. This is called proactive expansion. If a space is expanded because there are literally no free pages left, this is called a reactive expansion.

In the proactive mechanism, a sysadmin task will monitor existing storage spaces and automatically grow a storage space when its free space falls below a specific threshold amount. This threshold can be controlled using the SP_THRESHOLD onconfig parameter. Although the monitoring task will run automatically, proactive allocation is disabled by default.

In the reactive mechanism, when a transaction is about to fail because of insufficient space, Informix grows the depleted storage space either by extending an existing chunk or adding a new chunk. Transactions requiring additional storage to complete will stall while waiting for the additional storage to be allocated. If the storage space is not configured for automatic expansion, an ENOFREE error will be returned to the client just as it is in previous Informix versions.

Although both the proactive and reactive triggering mechanisms can be disabled, manual storage space expansion and chunk extension are still possible.

Control over chunks, storage spaces, and storage pool

The DBA has control over whether a given chunk or storage space can be grown so the DBA can tell Informix to enforce hard limits on the space used by a particular storage space. The DBA can also limit the amount of space in the storage pool, or let Informix use all the space that is available. The DBA will be able to configure the allocation amount when Informix automatically adds storage to a container, either in terms of a percent of the current space or as an absolute value in kilobytes. Also, the DBA can configure an instance-wide threshold that will be used by the proactive trigger.


Configuring storage provisioning

This section explains different levels of configurations for the storage provisioning feature.

Instance-level configuration parameters

Informix 11.70.xC1 introduces the following three new onconfig configuration parameters:

  • SP_AUTOEXPAND <0 | 1> (default - 1): This parameter enables or disables the automatic creation or extension of chunks. It is set to 1 (enabled) by default. This parameter can be changed dynamically using the onmode -wm or onmode -wf command. All manual operations remain enabled irrespective of this parameter setting.

    Note that the enablement of auto expansion has no effect if the storage pool is empty and your instance contains no extendable chunks, which are the default states in both cases.
  • SP_THRESHOLD <percentage or kilobytes> (default - 0): This parameter is used to define the minimum amount of free kilobytes that can exist in a storage space before Informix automatically runs a task to expand the space, either by extending an existing chunk in the space or by adding a new chunk. This parameter accepts the values either in terms of a decimal percentage or a number of kilobytes. If the amount of free space in any storage container falls below this threshold, the 'mon_low_storage' sysadmin task will attempt to expand the space the next time it runs. If the value is 50 or less, Informix interprets the value as a percentage (for example, 10 = 10 percent and 2.84 = 2.84 percent). If the value is 1000 or higher, Informix interprets the value as a specific number of kilobytes. Values between 50 and 1000 are considered invalid.

    The default setting is 0, meaning the proactive trigger is disabled. This parameter can be modified dynamically using the onmode -wm or onmode -wf command.

    Examples and their meanings:
    • SP_THRESHOLD 7.5 - The threshold is set to 7.5%. If the amount of free space in a container falls below 7.5% of the total amount of space in that container, it is considered below the threshold.
    • SP_THRESHOLD 10000 - The threshold is set to 10000KB or approximately 10MB. If the amount of free space in a container falls below 10MB, it's considered to be below the threshold.
  • SP_WAITTIME <number of seconds> (default - 30 seconds): This parameter is set to a number of seconds. It is the maximum amount of time a user thread will wait for another thread to expand a space before it times out and returns ENOFREE. The default setting is 30 seconds. This parameter can be modified dynamically using the onmode -wm or onmode -wf command.

Storage space level configuration

Each Informix storage space will have the following two configurable values:

  • create size - This is the minimum size of a chunk that Informix can automatically create in that space. The default value for create size is 10% of the current size of the space.
  • extend size - This the minimum allocation size by which a chunk in this space can be automatically extended. The default value for extend size is 10MB.

Neither of these values affects manual chunk creation or chunk extension. (See the section "Expanding Informix storage spaces" for more details.)

Storage pool entry level configuration

For each entry in the storage pool, it is possible to configure the minimum size of a chunk that can be allocated from that entry. You can also configure the priority in which these entries are considered when space is needed. (See section "Managing storage pool" for more details.)

Table 1. Quick reference for enabling/disabling functionality
FunctionalityControlling parameterDescriptionScope
Automatic expansion of storage spacesSP_AUTOEXPAND1 = Enabled (default); 0 = Disabled. For example: onmode -wm SP_AUTOEXPAND=1, or modify the onconfig file, or update it through Open Admin Tool. Instance level
Pro-active trigger for adding spaceSP_THRESHOLD0 = Disabled (default), valid values 0 through 50, and 1000 through maximum size of a chunk. For example: onmode -wm SP_THRESHOLD=20, or modify the onconfig file, or update it through Open Admin Tool. Instance level
Automatic chunk creationcreate sizeTo disable automatic chunk creation for a particular storage space, set the create size to 0. Default is 10% of the current size of the space. (For examples, see the section "Expanding Informix storage spaces.") Individual storage space level
Automatic chunk extensionextend sizeTo disable automatic chunk extensions for a particular storage space, set the extend size for that space to 0. Default is 10MB. (For examples, see the section "Expanding Informix storage spaces.") Individual storage space level

Managing chunk extendibility

A chunk can be extended manually or automatically; however, it must be marked as "extendable" first. Chunks can be marked extendable or non-extendable using the modify chunk extendable sysadmin task() command:

EXECUTE FUNCTION task("modify chunk extendable [on]", <chunk number>);
EXECUTE FUNCTION task("modify chunk extendable off", <chunk number>);

Examples

  • Mark the chunk number 4 as extendable (the keyword "on" is optional).
    Listing 1. Marking a chunk as extendable
    EXECUTE FUNCTION task("modify chunk extendable", 4);
  • Mark the chunk number 13 as non-extendable.
    Listing 2. Marking a chunk as non-extendable
    EXECUTE FUNCTION task("modify chunk extendable off", 13);

Note that, by default, chunks are not extendable. Both cooked and raw chunks can be extended. In the case of a cooked chunk, an extension may result in the cooked file growing larger. But extending a raw chunk does not make the raw device larger; it simply moves the end offset of the chunk farther out into the raw device. If the raw device is not large enough for the new chunk end offset, the extension will fail. Informix will not allow the creation or extension of a chunk to overlap an existing chunk in the same instance, whether the operation is performed manually or automatically.

Understanding onstat -d output

There are two new flags in the onstat -d output: one for dbspaces and one for chunks. When SP_AUTOEXPAND is set to 1 (the default), and a storage space such as a dbspace has a non-zero create size or extend size, that storage space is considered auto-expandable, and will have an 'A' flag in position 5, as shown in Listing 3. If SP_AUTOEXPAND is set to 0 (disabled), or both the create size and extend size for a particular space are set to 0, that storage space is not considered auto-expandable and will not have an 'A ' flag in the onstat -d output.

Listing 3. onstat -d output: Listing of storage spaces
Dbspaces
address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
4e108028         1        0x60001    1        1        2048     N  BA    informix rootdbs
5075ddf8         2        0x60001    2        1        2048     N  B     informix dbspace1
50810800         3        0x60001    3        1        2048     N  BA    informix dbspace2
50526d90         4        0x60011    4        1        4096     N BBA    informix blbsp1
5075d4f0         5        0x68001    5        1        2048     N SBA    informix sbspace1

In the chunks section of the same output, an 'E' flag in position 5 indicates that the chunk is marked extendable.

Listing 4. onstat -d output: Listing on chunks
Chunks
address    chunk/dbs   offset   size     free     bpages   flags pathname
4e1081d0   1      1    0        100000   37373             PO-BE- /informix/rootdbs
508d5df0   2      2    0        5000     4947              PO-B-- /informix/dbspace1
508109a8   3      3    0        2500     2447              PO-BE- /informix/dbspace2
50810d70   4      4    0        5000    ~2494     2500     POBB-- /informix/blobsp1
5075fd10   5      5    0        5000     4587     4587     POSB-- /informix/sbspace1
                       Metadata 360      268      360
 5 active, 32766 maximum

Note that the chunk flags that used to be in position 5 (for concurrent I/O and direct I/O, respectively) have been shifted into position 6.

Manually extending chunks

An extendable chunk can be manually grown, or extended, using the modify chunk extend command. The general syntax is as follows:

EXECUTE FUNCTION task("modify chunk extend", <chunk number> , <extend amount>);

This command extends a chunk immediately. The "extend amount" has default units of kilobytes, but can be expressed as "10 MB", "32 GB", and so on. This command used for extending a chunk manually is unaffected by the storage space's 'extend size' value.

Examples

  • Extend the chunk number 6 by 400MB.
    Listing 5. Extending a chunk by 400MB
    EXECUTE FUNCTION task("modify chunk extend", 6, "400 MB");
  • Extend the chunk number 4 by 10KB.
    Listing 6. Extending a chunk by 10KB
    EXECUTE FUNCTION task('modify chunk extend', 4, '10 KB');

Informix will automatically round the extend amount to the nearest multiple of the page size. The following chunks cannot be marked extendable, and therefore cannot be extended:

  • Blobspace chunk
  • Smartblob space chunk
  • Mirrored chunk

Expanding Informix storage spaces

Each dbspace, blobspace, and sbspace has a 'create size' and an 'extend size'. The create size is the minimum size of a chunk that Informix can automatically create in that space. The extend size is the minimum size by which a chunk in this space can be automatically extended. Neither the create size nor the extend size affects manual chunk creation or chunk extension.

You can specify both sizes in terms of absolute kilobytes or as a percentage of the current size using sysadmin task commands. Setting both the create size and the extend size to 0 for a particular storage space effectively turns off automatic expansion for that space. In fact, just setting extend size to 0 will unset the extendable flag for all chunks in that space. However, it is possible to mark chunks in that space as extendable again. Only unmirrored dbspaces and temporary dbspaces can have a non-zero extend size, because chunks cannot be extended in any other type of space. The default value for create size is 10%. The default value for extend size is 10MB. You can modify these sizes using the following sysadmin task command:

EXECUTE FUNCTION task("modify space sp_sizes", < space name >, < new create size >, < new extend size >);

Examples

  • Set the create size and extend size to 60MB and 10MB, respectively, for dbspace8.
    Listing 7. Set create size and extend size
    EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", 60000, 10000);
  • Set the create size and extend size to 20% and 1.5%, respectively for dbspace8.
    Listing 8. Set create size and extend size
    EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", 20, 1.5);
  • Disable automatic chunk creation for dbspace8, but leave automatic chunk extension enabled.
    Listing 9. Disable automatic chunck creation; leave automatic chunk extension enabled
    EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", 0, 1.5);
  • Disable automatic chunk extension, but leave automatic chunk creation enabled.
    Listing 10. Disable automatic chunck extension; leave automatic chunk creation enabled
    EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", 20, 0);

Manually expanding a storage space

If you do not want to wait for Informix to automatically expand a space, you can manually expand it using the following sysadmin task command:

EXECUTE FUNCTION task("modify space expand", < space name >, < size >);

This command immediately expands a space, either by extending a chunk or by adding a new chunk. (If the space contains no extendable chunks and the storage pool does not contain the requested space, the command will fail.) In this case, 'size' is the minimum size by which you would like to expand the space. The actual number of kilobytes added to the space may exceed this minimum, depending on several factors, such as the space's page size and the minimum chunk size settings for available entries in the storage pool.

This manual operation is not affected by either the dbspace's create size or its extend size.

Example

  • Expand dbspace4 by a minimum of 10MB.
    Listing 11. Expand dbspace4
    EXECUTE FUNCTION task("modify space expand", "dbspace4", "10 MB");

The components of a storage pool

sysadmin:storagepool table schema

A new storagepool table in the sysadmin database keeps track of storage pool entries. Its schema is as follows:

Listing 12. Storage pool schema
create table storagepool
    (
    entry_id    serial not null,
    path        varchar(255) not null,
    beg_offset  bigint not null,
    end_offset  bigint not null,
    chunk_size  bigint not null,
    status      varchar(255),
    priority    int     default 2,
    last_alloc  datetime year to second,
    logid       int,
    logused     int
    ) lock mode row;

create unique index ix_storagepool_1 ON storagepool(entry_id);
Table 2. Storage pool table
Column nameDescription
entry_idSerial number used to identify an entry.
pathThe path to the device/directory/file.
beg_offsetStarting offset of entry.
end_offsetEnd offset of entry.
chunk_sizeMinimum size of an allocation from this entry.
statusOne of following three: Active, Full, Error.
priorityAffects order in which this entry will be considered.
Valid priority values are:
1 = High
2 = Medium (default)
3 = Low
last_allocDate/time of last allocation from this entry. This is initialized to null when the entry is added and reset to null when the entry is modified.
logidUsed internally to determine least recently used entry.
logusedUsed internally to determine least recently used entry.

An entry in this table stores a directory, a cooked file, or a raw device for use by the storage provisioning feature. Distinguishing between directories and files/devices is done internally by Informix.

All entries can be broken down into two categories: fixed length and extendable. The information stored in three of the columns is different for the two types of entry:

  • Fixed length:
    • beg_offset - Starting offset into device.
    • end_offset - End offset into device.
    • chunk_size - Minimum size of chunk to be allocated from this device.
  • Extendable:
    • beg_offset - Starting offset into device, 0 for directory.
    • end_offset - 0.
    • chunk_size - Initial size of either the device or the cooked chunks within the directory.

Status

A normal, healthy entry in the sysadmin:storagepool table is considered 'active.' Its status column will contain the word "Active."

As chunks are allocated from an active, fixed-length storage pool entry, eventually the space in that entry runs out. When that happens, the entry is retained in the pool, but its status is updated to "Full." Informix will not attempt to use full entries.

If an entry is successfully added to the pool or subsequently modified but generates some kind of error later when Informix attempts to use it, it is retained in the pool (for possible 'fixing' later by the admin), but its status is updated to "Error." Informix will not attempt to use any erroneous entries.

Managing storage pool

The following are sysadmin task commands that can be used to manage your storage pool:

  • storagepool add
  • storagepool modify
  • storagepool delete
  • storagepool purge

Each raw device, cooked file, or directory in the storage pool is called an 'entry.' It is possible to add, modify, and delete entries from the pool using the sysadmin:task() or sysadmin:admin() functions.

  • storagepool add

    When adding an entry, you must provide the path, the offset from which Informix can begin allocating chunk space, the total amount of space available to Informix in this entry, the minimum size of a chunk that can be allocated from this new entry, and a priority. The syntax for this command is as follows:

    EXECUTE FUNCTION task("storagepool add", <path>, <begin offset>, <total size>, <chunk size>, <priority>);

    The path must be a quoted string. An environment variable may be used in <path> as long as it is present in the shell that invokes oninit.

    Listing 13. Example
    EXECUTE FUNCTION task("storagepool add", "$DBSDIR/chunk1", 0, 100000, 20000, 2);

    The other parameters do not need to be quoted if they are pure numbers. However, if the parameter value is a number that include units, such as "100 MB," it must be quoted.

    For sizes and offsets, it is valid to include units as shown above. The default units are kilobytes, but the following examples are all acceptable:

    • 100000
    • "100000 K"
    • "100 MB"
    • "100 GB"
    • "0.6 TB"

    Valid priority values are 1 (for High), 2 (for Medium), or 3 (for Low). This priority affects the order in which an entry is considered when Informix goes to the storage pool looking for space.

    Let's take a look at a few examples.

    • Add part of a raw device to the pool. Informix may allocate a total of 10GB of space from this device, starting at an offset of 50MB. The minimum size of a chunk Informix can carve out of this block of space is 100MB. Give this entry a high priority.
      Listing 14. Add part of a raw device to the pool
      EXECUTE FUNCTION task("storagepool add", "/dev/rawdevice1", "50 MB", "10 GB", 
      "100 MB", 1);
    • Add a directory to the pool. The minimum size of a chunk Informix can create in this directory is 200MB. Give this entry a medium priority.
      Listing 15. Add directory to the pool
      EXECUTE FUNCTION task("storagepool add", "/informix/STORAGE_DIR", 0, 0, 
      "200 MB", 2);
    • Add a cooked file to the pool. Informix may allocate a total of 3GB of space from this device, starting at an offset of 0. The minimum size of a chunk Informix can carve out of this block of space is 10MB. Give this entry a low priority.
      Listing 16. Add a cooked file to the pool
      EXECUTE FUNCTION task("storagepool add", "/ifmx/CHUNKFILES/cooked1", 0,3000000, 
      "10 MB", 3);
    • Add an extendable cooked file to the pool. If it is used, only one chunk will be created from this file. Its initial size will be 4GB, and it will automatically be marked extendable. Give this entry a medium priority.
      Listing 17. Add an extendable cooked file to the pool
      EXECUTE FUNCTION task("storagepool add", "/ifmx/CHUNKFILES/cooked2", 0, 0, 
      "4 GB", 2);
  • storagepool modify

    Each entry in the storage pool has a unique ID, stored in the entry_id column. This command requires the entry ID of an existing entry to be modified. You can modify the total size of the entry, the minimum chunk size, and the priority. You cannot modify the path or the offset. If either of those values needs to be changed, the entry will have to be deleted and re-added with the new path, the offset, or both. The syntax for the modify command is as follows:

    EXECUTE FUNCTION task("storagepool modify", <entry ID>, <new total size>, <new chunk size>, <new priority>);

    Let's take a look at an example. Change the total size, chunk size, and priority of storage pool entry 4 to 10GB, 10MB, and 2, respectively.

    Listing 18. Change the total size, chunk size, and priority of storage pool
    EXECUTE FUNCTION task("storagepool modify", 4, "10 GB", 10000, 2);
  • storagepool delete

    This command requires an entry ID of the entry to be deleted. The syntax for the delete command is as follows:

    EXECUTE FUNCTION task("storagepool delete", <entry ID>);

    Let's take a look at an example. Delete storage pool entry 14.

    Listing 19. Delete storage pool entry 14
    EXECUTE FUNCTION task("storagepool delete", 14);
  • storagepool purge

    Storagepool entries can be deleted en masse with the purge command:

    EXECUTE FUNCTION task("storagepool purge <all|full|errors>");

    Using the 'all' keyword will delete all rows from the storagepool table; 'full' will delete only those entries with a status of "Full"; and 'errors' will delete only those entries with a status of "Error."

    Listing 20. Examples
    EXECUTE FUNCTION task("storagepool purge all"); 
    EXECUTE FUNCTION task("storagepool purge full"); 
    EXECUTE FUNCTION task("storagepool purge errors");

Creating spaces using the storage pool

The storage pool can be used for more than just adding chunks to existing spaces; it can also be used to create spaces from scratch. Each space type has its own creation command, but the general format is create <object> from storagepool. The set of commands is described below.

  • create dbspace from storagepool

    This command creates a dbspace using an entry from the storage pool. The optional page size argument is given in units of kilobytes, and the optional mirror flag is either 1 for mirroring, or 0 for no mirroring. The syntax for the this command is as follows:

    EXECUTE FUNCTION task("create dbspace from storagepool", <space name>, <size>, [<page size> [, <mirror flag>]]);

    Let's take a look at a few examples.

    • Create a mirrored, 1GB dbspace called "dbspace3" with a page size of 6k.
      Listing 21. Create a mirrored dbspace
      EXECUTE FUNCTION task("create dbspace from storagepool", "dbspace3", "1 GB", 
      6, 1);
    • Create an unmirrored, 5MB dbspace called "dbspace4" with the default page size.
      Listing 22. Create an unmirrored dbspace
      EXECUTE FUNCTION task("create dbspace from storagepool", "dbspace4", 50000);
  • create tempdbspace from storagepool

    This command creates a temporary dbspace using an entry from the storage pool. The optional page size argument is given in units of kilobytes. The syntax for the this command is as follows:

    EXECUTE FUNCTION task("create tempdbspace from storagepool", <space name>, <size>, [<page size>]);

    Let's take a look at an example. Create a 1GB temp dbspace called "tempdbspace1" with a page size of 12k.

    Listing 23. Create a temporary dbspace
    EXECUTE FUNCTION task("create tempdbspace from storagepool", "tempdbspace1", 
    "1 GB", 12);
  • create blobspace from storagepool

    This command creates a blobspace using an entry from the storage pool. Note that the optional blobpage size is given in units of base pages, not kilobytes. This is consistent with the page size units given to the onspaces command. The optional mirror flag is either 1 for mirroring, or 0 for no mirroring. The syntax for the this command is as follows:

    EXECUTE FUNCTION task("create blobspace from storagepool", <space name>, <size>, [<blobpage size> [, <mirror flag>]]);

    Let's take a look at a few examples.

    • Create a mirrored, 100GB blobspace called "blobspace1" with a blobpage size of 100 base pages.
      Listing 24. Create a mirrored blobspace
      EXECUTE FUNCTION task("create blobspace from storagepool", "blobspace1", 
      "100 GB", 100, 1);
    • Create an unmirrored, 5MB blobspace called "blobspace2" with the default blobpage size (1 base page).
      Listing 25. Create an unmirrored blobspace
      EXECUTE FUNCTION task("create blobspace from storagepool", "blobspace2", 5000);
  • create sbspace from storagepool

    This command creates a smart blobspace using an entry from the storage pool. The optional log flag is either 1 for logging, or 0 for non-logging. The optional mirror flag is either 1 for mirroring, or 0 for no mirroring. The syntax for the this command is as follows:

    EXECUTE FUNCTION task("create sbspace from storagepool", <space name>, <size>, [<log flag> [,<mirror flag>]]);

    Let's take a look at a few examples.

    • Create a mirrored, 240MB smart blobspace called "sbspace1" with logging.
      Listing 26. Create a mirrored blobspace
      EXECUTE FUNCTION task("create sbspace from storagepool", "sbspace1", "240 MB", 
      1, 1);
    • Create an unmirrored, 5GB smart blobspace called "sbspace2" with no logging.
      Listing 27. Create an unmirrored blobspace
      EXECUTE FUNCTION task("create sbspace from storagepool", "sbspace2", "5 GB");
  • create tempsbspace from storagepool

    This command creates a temporary smart blobspace using an entry from the storage pool. In this case, there is no log flag or mirror flag argument. The syntax for the this command is as follows:

    EXECUTE FUNCTION task("create tempsbspace from storagepool", <space name>, <size>);

    Let's take a look at an example. Create 100MB temporary smart blobspace.

    Listing 28. Create a temporary smart blobspace
    EXECUTE FUNCTION task("create tempsbspace from storagepool", "temp_sbspace1", 
    "100 MB");

Creating chunks using the storage pool

In order to manually add a chunk to a space using the storage pool, you use the following command.

EXECUTE FUNCTION task("create chunk from storagepool", < space name >, < size >);

Note that the modify space expand command may also create a chunk from the storage pool and add it to the space specified. But if the space has extendable chunks, it may also extend a chunk instead of creating a new one. The create chunk from storagepool command forces a chunk add.

Let's take a look at an example. Add a 200MB chunk to the "logdbs" space.

Listing 29. Add a chunk to the space
EXECUTE FUNCTION task("create chunk from storagepool", "logdbs", "200 MB");

Adding or returning dropped chunk space to the storage pool

When dropping an empty space it is possible to automatically add the dropped chunk(s) to the storage pool. It does not matter whether the space originally came from the storage pool. The general syntax for this command is as follows:

EXECUTE FUNCTION task("drop dbspace | tempdbspace | blobspace | sbspace | tempsbspace to storagepool", <space name>);

Let's take a look at an example. Drop an empty dbspace called "mydbs3," and add all freed chunk space to the storage pool.

Listing 30. Drop empty dbspace; add freed chunk space
EXECUTE FUNCTION task("drop dbspace to storagepool", "mydbs3");

You can also drop an individual chunk and add its space to the storage pool using the following command:

EXECUTE FUNCTION task("drop chunk to storagepool", <space name>, <path>, <offset>);

Listing 31. Example
EXECUTE FUNCTION task("drop chunk to storagepool", "bigdbs", "/dev/rawdisk23", "100 KB");

When returning or adding space to the pool during a chunk drop, Informix combines the space with any existing entries. However, this concatenation will not occur during manual storagepool add or storagepool modify commands.

Quick reference of admin task commands

Table 3. Managing chunk extendibility
CommandParametersPurpose
modify chunk extendable [on] | off<chunk number>Mark chunk as extendable.
modify chunk extend<chunk number>,
<extend amount>
Extend chunk manually by a given amount.
Table 4. Managing storage spaces
CommandParametersPurpose
modify space sp_sizes<space name>,
<new create size>,
<new extend size>
To update automatic expansion characteristics of a storage space.
Create size is the minimum size of a chunk that gets created automatically.
Extend size is the minimum size by which a chunk in the space can be automatically extended.
modify space expand<space name>,
<expand size>
To expand a space manually by a given amount.
Table 5. Managing the storage pool
CommandParametersPurpose
storage pool add<path>,
<begin offset>,
<total size>,
<chunk size>,
<priority>
Add a new entry to storage pool.
storage pool modify<entry id>,
<new total size>,
<new chunk size>,
<new priority>
Modify an existing storage pool entry.
storage pool delete<entry id>Delete an existing storage pool entry.
storage pool purge <all | full | errors>NoneDelete all existing storage pool entries.
Table 6. Creating and dropping storage spaces using the storage pool
CommandParametersPurpose
create dbspace from storagepool<space name>,
<size>,
[<page size>[,
<mirror flag>]]
Create a dbspace using an entry from the storage pool.
create tempdbspace from storagepool<space name>,
<size>,
[<page size>]
Create a temporary dbspace using an entry from the storage pool.
create blobspace from storagepool<space name>,
<size>,
[<blobpage size>[,
<mirror flag>]]
Create a blobspace using an entry from the storage pool.
create sbspace from storagepool<space name>,
<size>,
[<log flag>[,
<mirror flag>]]
Create a smart blobspace using an entry from the storage pool.
create tempsbspace from storagepool<space name>,
<size>
Create a temporary smart blobspace using an entry from the storage pool.
create chunk from storagepool<space name>,
<size>
Add a chunk to specified storage space using storage pool.
drop dbspace | tempdbspace | blobspace | sbspace | tempsbspace to storagepool<space name>Drop an empty storage space and add it to the storage pool.

Useful Information

Chunk path naming convention

When a chunk is created from scratch using an entry in the storage pool, its name takes the following format:

<server name>_<space name>_<p or m>_<counter>
where:

  • <server name> = The value of DBSERVERNAME
  • <space name>= The name of the space to which the chunk is being added
  • <p or m> = 'p' for primary chunk, 'm' for mirror chunk
  • <counter> = An integer that ensures we generate a unique file name

Let's take a look at a few examples.

  • ifmx1_dbspace4_p_1
  • ifmx1_logdbs_p_4
  • ifmx1_logdbs_m_1

Information in sysmaster tables

  • The sysmaster:syschunks view now has an additional column called 'is_extendable' to indicate whether the chunk is extendable or not.
  • The sysmaster:sysdbstab table has two new columns: 'create_size' and 'extend_size.'

Internal admin APIs

There are a couple of admin tasks that are used internally by Informix. They are described here for informational purposes only. DBAs should not attempt to execute these tasks by hand.

  • add_storage - This task adds a chunk to a DBSpace, temp DBSpace, BlobSpace, SBSpace, or temp SBSpace. If the Informix storage pool is not populated or, for whatever reason, cannot successfully add a chunk, the task fails.
  • mon_low_storage - This is a sensor task that runs periodically, monitoring DBSpace free space. When free space in a particular storage space falls below a specific threshold, the mon_low_storage task attempts to expand the space. The frequency with which this sensor executes can be set by the administrator using the following SQL, where <minutes> should be replaced by a number of minutes:
    Listing 32. Set mon_low_storage frequency
    DATABASE sysadmin;
    UPDATE ph_task 
    set tk_frequency = INTERVAL (<minutes>) MINUTE TO MINUTE
    WHERE tk_name = "mon_low_storage";

Conversion/reversion considerations

It is important to note that if you revert the server to an earlier version of Informix (prior to 11.70xC1), you will lose all storage provisioning settings and functionality. Reconverting to 11.70.xC1 or later will not restore any of the settings. However, any chunks or spaces created in 11.70, regardless of the method used, will remain intact during the reversion process.

Replication considerations

When a directory is given to a primary server for use in the storage pool, that same directory must also exist on all HDR and RSS secondary servers. Informix will verify its existence on the secondary at the time of its addition to the storage pool on the primary and fail the add operation on the primary if the directory does not exist on the secondary.


Conclusion

With the storage provisioning feature, you can configure the server to automatically manage storage spaces when more space is needed. The storage provisioning feature enables you to use space more effectively, and ensure that space is allocated as necessary, while reducing out-of-space errors and the time you need to manually monitor your space to determine which storage space will run out of free space and when. You can also configure the server to expand the space before it is full, when its free pages fall below a specified threshold. Even if you configure and enable automatic storage provisioning, it allows you to manually expand a space or extend a chunk.

Resources

Learn

Get products and technologies

  • Download a trial version of Informix.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=630177
ArticleTitle=Storage provisioning in Informix
publish-date=03032011