Creating a preset placement policy for a DB2 SMS tablespace and DB2 automatic storage path


Creating a preset placement policy for a DB2 SMS tablespace and DB2 automatic storage path
Prev	Database Dynamic Storage Tiering use cases for DB2	Next

In many cases it is desirable to permanently segregate certain types of database files from other data managed by a system for performance or availability reasons. For example, a data center might choose to reserve its highest-quality storage for database datafiles and index files, and relegate other data to a second tier in the storage hierarchy.

Database Dynamic Storage Tiering preset file placement policies make such segregation possible. An administrator can create a preset placement policy and assign it to a database's file system as the active DST policy.

Database Dynamic Storage Tiering preset policies are useful with DB2's System Managed Storage (SMS) tablespace and automatic storage path features. Preset policies specify where files should be created before the files exist. For automatic storage paths, the database must be created before assigning the preset policies for tablespaces to be managed by automatic storage when they are created.

Figure: Database storage configuration example shows an example of storage configuration for a file system that contains files for the DB2 database PROD. For SMS tablespaces, the database administrator wants to reserve tier1 volumes for DB2 data and index files, and allocate all other files to tier2 volumes. For automatic storage paths, the database administrator wants to reserve tier1 volumes for the System Catalog Tablespace and all other tablespaces on tier2 volumes.

Figure: Database storage configuration example

First, create a DB2 database, PROD, using automatic storage path. Then, run the db2ed_update command as follows:

$ /opt/VRTS/bin/db2ed_update -D PROD

Then you must set up your system to use Database Dynamic Storage Tiering.

To add the fast_storage and slow_storage storage classes

Use the dbdst_admin command as follows:

$ /opt/VRTS/bin/dbdst_admin -D PROD -o addclass=\
tier1:"Fast Storage"

$ /opt/VRTS/bin/dbdst_admin -D PROD -o addclass=\
tier2:"Slow Storage"

To convert the database's file system and add volumes for use with Database Dynamic Storage Tiering

Use the dbdst_convert command as follows:

$ /opt/VRTS/bin/dbdst_convert -D PROD \
-M /dev/vx/dsk/db2dg/db2data -v new_vol1,new_vol2,new_vol3

To classify volumes into storage classes

Use the dbdst_classify command as follows:

$ /opt/VRTS/bin/dbdst_classify -D PROD \
-M /dev/vx/dsk/db2dg/db2data -v new_vol1:tier1

$ /opt/VRTS/bin/dbdst_classify -D PROD \
-M /dev/vx/dsk/db2dg/db2data -v new_vol2:tier2,\
new_vol3:tier2

Once the volumes are configured, an administrator can set policies for SMS tablespaces and automatic storage path.

To create a policy for the System Catalog Tablespace in automatic storage path

Use the dbdst_preset_policy command as follows:

$ /opt/VRTS/bin/dbdst_preset_policy -D PROD -o ats -P \
"tier1=*.CAT;tier2=*.TMP,*.UTM,*.USR,*.LRG"

To create a SMS directory, and create and assign a file placement policy

Create a directory for your SMS tablespace.
```
$ mkdir /db2data/tbs01
```

Use the dbdst_preset_policy command as follows:

$ /opt/VRTS/bin/dbdst_preset_policy -D PROD -d /db2data/tbs01 \
-P "tier1=SQL*.DAT,SQL*.INX:tier2=SQL*.LB,SQL*.LBA,SQL*.LF,SQL*.BKM"

The command creates the SMS tablespace container directory /db2data/tbs01, as well as a file placement policy for the file system /db2data.

Once the preset policy has been created and assigned to the database's file system, a database administrator can use DB2 administrative commands to create the SMS tablespace tbs01 specifying /db2data/tbs01 as the tablespace container. Space for data and index files created by DB2 during the course of operations are allocated on fast_storage volumes; all other files are allocated on slow_storage volumes. Database Dynamic Storage Tiering also enforces the policy to relocate already-existing database files to the appropriate placement class.

It is important to run db2ed_update to record the tablespace information in the SFDB repository:

$ /opt/VRTS/bin/db2ed_update -D PROD