modify space sp_sizes argument: Modify sizes of an extendable storage space (SQL administration API)
Use the modify space sp_sizes argument with the admin() or task() function to modify the create, extend, and maximum sizes that are associated with expanding a storage space. Use the modify space sp_sizes argument with the admin() or task() function to modify two different sizes that are associated with expanding a storage space, the extend size and the create size. Modify the sizes to control how OneDB uses storage pool entries for a particular storage space.
| Element | Description | Key Considerations |
|---|---|---|
| space_name | The name of the storage space. | |
| max_size | The maximum size of the storage space, in KB. | The default maximum size is 0, which indicates unlimited size. |
| new_create_size | The minimum size of a new chunk that the server can create when automatically expanding this space using the storage pool. You can define the size as a number of KB or as a percentage of the total space. | The default create size is set to 10 percent
of the total size of the space. The size that you specify affects chunks the server creates automatically. It does not affect any manual chunks that you might create for the associated space. |
| new_extend_size | The minimum size that the server can use when automatically extending a chunk in an unmirrored dbspace or temporary dbspace. The size can be a specified number of KB or a percentage of the total space. | The default extend size is 10 MB. The size that you specify affects chunks the server extends automatically. It does not affect any manual chunk extensions that you might initiate for the associated space. |
Usage
If the create or extend size value is 100 or a lower value, OneDB interprets the value as a percentage (for example, 10 = 10 percent and 2.84 = 2.84 percent). If the value is 1000 or higher, the server interprets the value as a specific number of KB. Values 100 - 1000 are not valid.
If you set the create size and the extend size to 0, OneDB does not automatically
expand the space, even when the space becomes full. Additionally, if you set the
extend size to 0, you also remove the Extendable flag from all
chunks in that space. This is an easy way to mark all chunks in a space as not
extendable, using one operation.
The create and extend size values are minimum sizes. The actual size by which a space is expanded might be larger, depending on the chunk size of the storage pool entry that the server is using or the amount of space that the server needs at that particular time.
For example, suppose you created a storage pool entry to expand storage space when necessary. Then suppose that a dbspace named logdbs is out of free pages and requires an extra 500 MB for a new log. If none of the chunks in logdbs can be extended, OneDB adds a chunk that has the minimum size that is specified by the create size value for the logdbs dbspace. If the create size for the logdbs dbspace is less than or equal to 500 MB, the server attempts to find a minimum of 500 MB of space. If the create size for logdbs is 1 GB, the server ignores the requested size and adds a 1 GB chunk.
If the server is unable to find the minimum amount of space that is required, the server returns an out-of-space error and the log creation fails.
If you set the maximum size of the storage space to a value other than 0, the storage space cannot exceed the maximum size, regardless of the new extend size. When the amount of expansion space that is left before the maximum size is less than the new extend size, the extend size is truncated and the space is extended to the maximum size. The event alarm 86001 is triggered when the space reaches the maximum size. When the amount of expansion space left is less than the minimum chunk size for the storage pool, the space is not expanded and an error is returned.
Examples
The following command sets the minimum create size to 60 MB, the minimum extend size to 10 MB, and the maximum size to 100 MB for a dbspace that is named dbspace3:
EXECUTE FUNCTION task("modify space sp_sizes", "dbspace3", "60000",
"10000", "100000");The following command sets the minimum create size to 60 MB and the minimum extend size to 10 MB for a dbspace that is named dbspace3:
EXECUTE FUNCTION task("modify space sp_sizes", "dbspace3", "60000",
"10000");The following command sets the minimum create size to 20 percent and the minimum extend size 1.5 percent for a dbspace that is named dbspace8:
EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", "20", "1.5");