Shakil is a member of the DB2 LUW U.S. support team and has been dedicated to providing client success for over 5 years.

Database Administrators sometimes may need to restore a portion of their databases from Production to a Test environment for various reasons, ( testing etc ). They need to restore a small portion of their Production database because the Production database is too big to restore in test env and/or the Production database restore takes a very long time.

It was not possible to do this prior to Version 9.1.

In Version 9.1 DB2 offers partial restore ( tablespace level ). The following example shows how to do that and it also explores a specific case where two tablespaces have dependencies in the database.

The example below shows that one table has been created in one tablespace and its CLOB data is kept in different tablespace and the DBA decides to avoid these two tablespaces ( partial restore ) and what a DBA may face after the restore is completed and are about to drop those tablespaces.

In DB2 Version 9.1 we have options of restoring a database without some of the tablespaces. This is new in V9.1.

The following shows a step by step test.

Lets first create a database name Testdb. Then create two tablespaces.

Tablespace creation syntex is not given here.

I have created one tablespace name myspace3 ( regular ) and which is DMS

I have Created another tablespace name myspace4 ( large ) and which is DMS

Create one table MYTABLE1 in myspace3, and insert some values in table MYTABLE1.

Then create another table MYTABLE2 in myspace3 with CLOB field and then make sure CLOB field goes to large tablespace myspace4.

Insert some values in the MYTABLE2.

Then do a offline backup of the database Testdb and then drop the database.

You will also see more tablespaces but for the sake of this doc, I am not showing those.

Now, We will try to drop MYTABLE1 and MYTABLE2 and then try to drop tablespace myspace3 and myspace4

/home/shakilc: db2 drop table MYTABLE1 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0290N Table space access is not allowed. SQLSTATE=55039

/home/shakilc: db2 drop table MYTABLE2 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0290N Table space access is not allowed. SQLSTATE=55039

/home/shakilc: db2 drop tablespace MYSPACE3 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "MYSPACE3" cannot be dropped because at least one of the tables in it, "SHAKILC.MYTABLE2", has one or more of its parts in another table space. SQLSTATE=55024

/home/shakilc: db2 drop tablespace MYSPACE4 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "MYSPACE4" cannot be dropped because at least one of the tables in it, "SHAKILC.MYTABLE2", has one or more of its parts in another table space. SQLSTATE=55024

Now you have databases without two tablespaces ( myspace3 and myspace4 ).

From doc:

You can drop a user table space that contains all of the table data including index and LOB data within that single user table space. You can also drop a user table space that might have tables spanned across several table spaces. That is, you might have table data in one table space, indexes in another, and any LOBs in a third table space. You must drop all three table spaces at the same time in a single statement. All of the table spaces that contain tables that are spanned must be part of this single statement or the drop request will fail.