Thursday, June 24, 2010

Moving? Pack your tablespaces - Transportable tablespaces and schemas (sets)

Starting with DB2 9.7 FP2 it is possible to transport tablespaces and schemas. What does that mean? You take a backup image of an existing database and RESTORE the database schema(s) and associated tablespaces into another existing database.

The associated schemas and tablespaces are named a transportable set, neither of them can be partial. That is, you need all tablespaces that hold data of database objects for a given schema or given schemas as well as all schemas that have objects in the transported tablespace need to be restored. This is something to consider when planning new database layouts.

Sometimes mini-databases are realized as objects in dedicated schemas in a single database. With transportable sets, they can be moved across databases and servers.

Note that another method of moving data and schema information is db2move. db2move is based on using export and import or load and is utilizing IXF files for holding the data. In contrast, transportable sets (tablespaces and schemas) are enhancements to backup/restore.