DB2 Automatic Storage

DB2 Automatic storage was initially introduced from version 8 Fixpack 9, but it was enabled by default only from Version 9. It was intended to make the storage management easier from the storage perspective as well as administration for backup,restore and creating additional tablespaces. It only applies to DMS tablespaces and can't be reverted back once its enabled.

If we go by naming convention "automatic storage", it enables the storage autogrow and we need not worry for defining the container paths while adding the tablespaces anymore.

How do we enable automatic storage in database :
It can be enabled when you create the database initially. By default it is AUTOMATICS STORAGE YES since the db2 version 9.x

CREATE DATABASE TEST ( It creates the storage path on home directory)
CREATE DATABASE TEST on '/fs1' (It creates the storage path on /fs1)
CREATE DATABASE TEST on '/fs1','fs2' (It creates the storage path on two filesystems /fs1 & /fs2)

If you are creating the database on version under 9.x, always specify the AUTOMATIC STORAGE YES like below :
CREATE DATABASE TEST AUTOMATIC STORAGE YES

Note:- There is a restriction on windows operating system while defining the storage path. The automatic storage path must be a drive letter only unless DB2_CREATE_DB_ON_PATH registry variable is set to YES

For example :
CREATE DATABASE TEST on 'C:\DATA'
CREATE DATABASE TEST on 'C:'

There is other way where you can create the windows database with the storage path but you also need to specify the database path like below :

CREATE DATABASE TEST on 'C:\DATA' DBPATH ON 'C:'

In case you are not sure for creating the automatic storage on DB2 9.x & above, you need to explicitly specify ATOMATIC STORAGE NO

CREATE DATABASE TEST AUTOMATIC STORAGE NO


Would you need to add the storage path later in future : You can simply add a storage to the database by using the below command.

ALTER DATABASE TEST ADD STORAGE ON '/fs3'

Would you need to drop the storage path : You can drop the storage path and data can be moved off to the other storage paths after the tablespace rebalance.

For example :
Drop the storage :
ALTER DATABASE TEST DROP STORAGE ON '/fs3'

Rebalance the tablespaces :
ALTER TABLESPACE <tb-space-1> REBALANCE
ALTER TABLESPACE <tb-space-2> REBALANCE
ALTER TABLESPACE <tb-space-n> REBALANCE


Database restore of automatic storage enabled database : Restore is way simper now than earlier non-automatic storage specially the redirected restore. Some standard examples are given below :

RESTORE DATABASE TEST
RESTORE DATABASE TEST TO C:
RESTORE DATABASE TEST DBATH ON C:
RESTORE DATABASE TEST ON /fs1,/fs2,/fs3
RESTORE DATABASE TEST ON C:\newpath DBPATH ON C:

Important information about database path & storage path :
The database path (which is where the database manager stores various control files for the database)
If TO or DBPATH ON is specified, this indicates the database path.
If ON is used but DBPATH ON is not specified with it, the first path listed with ON is used as the database path (in addition to it being a storage path).
If none of TO, ON, or DBPATH ON is specified, the dftdbpath database manager configuration parameter determines the database path.

The storage paths (where the database manager creates automatic storage table space containers)
If ON is specified, all of the paths listed are considered storage paths, and these paths are used instead of the ones stored within the backup image.
If ON is not specified, no change is made to the storage paths (the storage paths stored within the backup image are maintained).

Note:- This information is shared based on my knowledge and the experience.