The Set Up
To apply the dropped table recovery feature to an end-to-end example, first create a database (the sample DB in this Solution is named
DBSTUDY). You can do so either graphically using the DB2 Control Center or via the command line:
db2=> create database DBSTUDY
Next, connect to the database. In the case of this sample, DBSTUDY has a user named db2admin with a password of db2admin:
db2=> connect to DBSTUDY user db2admin using db2admin
Enable Dropped Table Recovery
Inside DB2, the database is divided into table spaces. Database tables are assigned to these table spaces. In order for a dropped table to be recoverable, you have to turn on the DROPPED TABLE RECOVERY option on the table space that houses it. This option is enabled by default for newly created data table spaces if you used the command line to create your table space. However, if you use the Control Center to create a housing table space, you must enable the option in the wizard (see Figure 1).
| Figure 1: Enabling the Dropped Table Recovery Option with the Create Table Space Wizard |
To make sure a given name space has table recovery enabled, query the DROP_RECOVERY column of the SYSCAT.TABLESPACES catalog table:
db2 => select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
In the sample case, the DBSTUDY database exists in the table space named USERSPACE1. Figure 2 shows the execution of the above query, which confirms that USERSPACE1 has DROP_RECOVERY enabled.
| Figure 2: Query to See DROP_RECOVERY Status of Table Spaces |
To enable the DROPPED TABLE RECOVERY option after you've created a table space, use the following syntax:
ALTER TABLESPACE <tablespace-name> DROPPED TABLE RECOVERY ON
Digg
del.icio.us
Newvine
furl
StumbleUpon
BlinkList
Newsvine
Magnolia
Facebook
Tailrank
Slashdot
Technorati
Google Bookmarks
Yahoo Favorites
Windows Live
Ask
Previous Page
