Migrating existing partitioned data in a DB2 database


Migrating existing partitioned data in a DB2 database
Prev	Database Dynamic Storage Tiering use cases for DB2	Next

Perhaps the simplest application of multi-tier storage to databases is relocation of partitioned data between different placement classes as usage requirements change. If exact relocation times are unpredictable, or if relocation is infrequent, administrators may wish to relocate table partitions as business requirements surface rather than defining strict periodic relocation schedules.

For example, a large retailer keeps the most recent year of sales information in the fast storage class and the rest of the data in the slow storage class. The database administrator creates one table per quarter, using four tables to represent a year of sales data. Each table resides in a separate tablespace. The database administrator then unions the four tables together, called year_sales, using the UNION ALL construct in DB2. Every quarter, the database administrator rolls out the oldest table to slower storage to make room for the new table in the fast storage class. Assume that the current tables are sales_0304, sales_0404, sales_0105, and sales_0205. The sales_0304 table represents the oldest quarter, and it resides in tablespace tbs_sales_0304.

First, 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=\
fast_storage:"Fast Storage for Production DB"

$ /opt/VRTS/bin/dbdst_admin -D PROD -o addclass=\
slow_storage:"Slow Storage for Production DB"

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:fast_storage,db2data_b4vset:fast_storage

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

Once the volumes are configured, an administrator can roll out the oldest table, sales_0304, and add a new table

To move the oldest sales data into a slower class and add a new tablespace

Use the dbdst_tbs_move command as follows:

$ /opt/VRTS/bin/dbdst_tbs_move -D PROD -t tbs_sales_0304 \
-c slow_storage

Drop the year_sales view.
Create a new tablespace, tbs_sales_0305, and create the table sales_0305 in this tablespace.
Recreate the file year_sales by performing a UNION ALL, including sales_0305 and excluding sales_0304.

Move the tablespace to fast storage as follows:

$ /opt/VRTS/bin/dbdst_tbs_move -D PROD -t tbs_sales_0305 \
-c fast_storage

Prev	Up	Next
Creating a preset placement policy for a DB2 SMS tablespace and DB2 automatic storage path	Home	Scheduling the relocation of archive logs