DB2 Version 9.7 for Linux, UNIX, and Windows

Automatic re-sizing of DMS table spaces

Enabling database-managed (DMS) table spaces that use file containers for automatic resizing allows the database manager to handle the full table space condition automatically by extending existing containers for you.

DMS table spaces are made up of file containers or raw device containers, and their sizes are set when the containers are assigned to the table space. The table space is considered to be full when all of the space within the containers has been used. However, unlike for SMS table spaces, you can add or extend containers manually, using the ALTER TABLESPACE statement, allowing more storage space to be given to the table space. DMS table spaces also have a feature called auto-resize: as space is consumed in a DMS table space that can be automatically re-sized, the database manager increases the size of the table space by extending one or more file containers.

The auto-resize capability for DMS table spaces is related to, but different from capabilities of automatic storage table spaces. For more information see Comparison of SMS, DMS and automatic storage table spaces.

Enabling and disabling the auto-resize feature

By default, the auto-resize feature is not enabled for a DMS table space. The following statement creates a DMS table space without enabling auto-resize:

   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)

To enable the auto-resize feature, specify the AUTORESIZE YES clause for the CREATE TABLESPACE statement:

   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES

You can also enable or disable the auto-resize feature after creating a DMS table space by using ALTER TABLESPACE statement with the AUTORESIZE clause:

   ALTER TABLESPACE DMS1 AUTORESIZE YES
   ALTER TABLESPACE DMS1 AUTORESIZE NO

Two other attributes, MAXSIZE and INCREASESIZE, are associated with auto-resize table spaces:

Maximum size (MAXSIZE)

The MAXSIZE clause of the CREATE TABLESPACE statement defines the maximum size for the table space. For example, the following statement creates a table space that can grow to 100 megabytes (per database partition if the database has multiple database partitions):

   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES MAXSIZE 100 M

The MAXSIZE NONE clause specifies that there is no maximum limit for the table space. The table space can grow until a file system limit or table space limit is reached (see SQL and XML limits ). If you do not specify the MAXSIZE clause, there is no maximum limit when the auto-resize feature is enabled.

Use the ALTER TABLESPACE statement to change the value of MAXSIZE for a table space that has auto-resize already enabled, as shown in the following examples:

   ALTER TABLESPACE DMS1 MAXSIZE 1 G
   ALTER TABLESPACE DMS1 MAXSIZE NONE

If you specify a maximum size, the actual value that the database manager enforces might be slightly smaller than the value specified because the database manager attempts to keep container growth consistent.

Increase size (INCREASESIZE)

The INCREASESIZE clause of the CREATE TABLESPACE statement defines the amount of space used to increase the table space when there are no free extents within the table space but a request for one or more extents was made. You can specify the value as an explicit size or as a percentage, as shown in the following examples:

   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES INCREASESIZE 5 M

   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES INCREASESIZE 50 PERCENT

A percentage value means that the amount by which to increase is calculated every time that the table space needs to grow; that is, growth is based on a percentage of the table space size at that point in time. For example, if the table space is 20 MB in size and the INCREASESIZE value is 50% , the table space grows by 10 MB the first time (to a size of 30 MB) and by 15 MB the next time.

If you do not specify the INCREASESIZE clause when you enable the auto-resize feature, the database manager determines an appropriate value to use, which might change over the life of the table space. As with AUTORESIZE and MAXSIZE, you can change the value of INCREASESIZE using the ALTER TABLESPACE statement.

If you specify a size increase, the actual value that the database manager will use might be slightly different than the value that you provide. This adjustment in the value used is done to keep growth consistent across the containers in the table space.

Restrictions for using AUTORESIZE with DMS table spaces

You cannot use this feature for table spaces that use raw device containers, and you cannot add raw device containers to a table space that can be automatically resized. Attempting these operations results in errors (SQL0109N). If you need to add raw device containers, you must disable the auto-resize feature first.
If you disable the auto-resize feature, the values that are associated with INCREASESIZE and MAXSIZE are not retained if you subsequently enable this feature.
A redirected restore operation cannot change the container definitions to include a raw device container. Attempting this kind of operation results in an error (SQL0109N).
In addition to limiting how the database manager automatically increases a table space, the maximum size also limits the extent to which you can manually increase a table space. If you perform an operation that adds space to a table space, the resulting size must be less than or equal to the maximum size. You can add space by using the ADD, EXTEND, RESIZE, or BEGIN NEW STRIPE SET clause of the ALTER TABLESPACE statement.

How table spaces are extended

When AUTORESIZE is enabled, the database manager attempts to increase the size of the table space when all of the existing space has been used and a request for more space is made. The database manager determines which of the containers can be extended in the table space so that a rebalancing of the data in the table space does not occur. The database manager extends only those containers that exist within the last range of the table space map (the map describes the storage layout for the table space - see Table space maps for database-managed table spaces for more information) and extends them by an equal amount.

For example, consider the following statement:

   CREATE TABLESPACE TS1 MANAGED BY DATABASE
     USING (FILE 'C:\TS1CONT' 1000, FILE 'D:\TS1CONT' 1000,
            FILE 'E:\TS1CONT' 2000, FILE 'F:\TS1CONT' 2000)
     EXTENTSIZE 4
     AUTORESIZE YES

Keeping in mind that the database manager uses a small portion (one extent) of each container for metadata, following is the table space map that is created for the table space based on the CREATE TABLESPACE statement. (The table space map is part of the output from a table space snapshot.)

   Table space map:

    Range  Stripe Stripe  Max         Max  Start  End    Adj.   Containers
    Number Set    Offset  Extent      Page Stripe Stripe
    [   0] [   0]      0     995      3983      0    248   0    4 (0,1,2,3)
    [   1] [   0]      0    1495      5983    249    498   0    2 (2,3)

The table space map shows that the containers with an identifier of 2 or 3 (E:\TS1CONT and F:\TS1CONT) are the only containers in the last range of the map. Therefore, when the database manager automatically extends the containers in this table space, it extends only those two containers.

Note: If you create a table space with all of the containers having the same size, there is only one range in the map. In such a case, the database manager extends each of the containers. To prevent restricting extensions to only a subset of the containers, create a table space with containers of equal size.

As discussed previously, you can specify a limit on the maximum size of the table space, or you can specify a value of NONE, which does not limit growth. If you specify NONE or no limit, the upper limit is defined by the file system limit or by the table space limit; the database manager does not attempt to increase the table space size past the upper limit. However, before that limit is reached, an attempt to increase a container might fail due to a full file system. In this case, the database manager does not increase the table space size any further and returns an out-of-space condition to the application. There are two ways to resolve this situation:

Increase the amount of space available on the file system that is full.
Perform container operations on the table space such that the container in question is no longer in the last range of the table space map. The easiest way to do this is to add a new stripe set to the table space with a new set of containers, and the best practice is to ensure that the containers are all the same size. You can add new stripe sets by using the ALTER TABLESPACE statement with the BEGIN NEW STRIPE SET clause. By adding a new stripe set, a new range is added to the table space map. With a new range, the containers that the database manager automatically attempts to extend are within this new stripe set, and the older containers remain unchanged.
Note: When a user-initiated container operation is pending or a subsequent rebalance is in progress, the auto-resize feature is disabled until the operation is committed or the rebalance is complete.

For example, for DMS table spaces, suppose that a table space has three containers that are the same size and that each resides on its own file system. As work is done on the table space, the database manager automatically extends these three containers. Eventually, one of the file systems becomes full, and the corresponding container can no longer grow. If more free space cannot be made available on the file system, you must perform container operations on the table space such that the container in question is no longer in the last range of the table space map. In this case, you could add a new stripe set specifying two containers (one on each of the file systems that still has space), or you could specify more containers (again, making sure that each container being added is the same size and that there is sufficient room for growth on each of the file systems being used). When the database manager attempts to increase the size of the table space, it now attempts to extend the containers in this new stripe set instead of attempting to extend the older containers.

Monitoring

Information about automatic resizing for DMS table spaces is displayed as part of the table space monitor snapshot output. The increase size and maximum size values are included in the output, as shown in the following sample:

   
   Auto-resize enabled                      = Yes or No
   Current tablespace size (bytes)          = ###
   Maximum tablespace size (bytes)          = ### or NONE
   Increase size (bytes)                    = ###
   Increase size (percent)                  = ###
   Time of last successful resize           = DD/MM/YYYY HH:MM:SS.SSSSSS
   Last resize attempt failed               = Yes or No