Recover Dropped Tables in DB2

Recover Dropped Tables in DB2

lot of ugly things can happen in the early morning hours when you are frantically trying to meet a deadline. For example, your mind can falter for a split second and you might accidentally drop an IBM DB2 Universal Database table. Before your stomach starts twisting in knots and you scramble for your stress ball, relax. Believe it or not, you might be able to recover the table.

This article demonstrates a DB2 feature that allows you to recover tables. By performing a few steps when you create a database, you can recover (no pun intended) from a table dropping mishap.

This Solution assumes you are using DB2 Universal Database Version 8.1. You can download a trial version from IBM.



How do I ensure that the critical tables in my DB2 database are recoverable in case I inadvertently drop them?



Apply DB2’s built-in dropped table recovery feature to recover accidentally dropped tables.

Why Not Just Use the Database Restore and Rollforward Functions?
DB2 allows you to recover a database’s data using a database restore operation (see Defy Murphy’s Law with DB2 Backup and Restore). In fact, you could use the restore operation in tandem with the rollforward operation to restore the database to a point before the table was dropped, but restoring the entire database can be very time consuming. More importantly, during this database-level operation, you can’t access your database. On the flipside, with the table recovery feature, your table-level restore effort is more directed, allowing you to use the database in question.

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  DROPPED TABLE RECOVERY ON

Backing Up
Before you can recover a database table, you must have backed up its housing database. Don’t worry, that doesn’t mean clairvoyance is a requirement. As you will see shortly, you don’t have to backup your database right before your table is dropped. The database backup can occur right after you create the database. Furthermore, you could even perform it before you create the deleted table. You can then use the database logs to reconstruct your database (for more on this subject, read through my previous Solution on DB2 database recovery).

As mentioned previously, DB2 uses database logs to recover the changes that occurred to the database after a backup. For recovery to take place, you need to enable a logging type called archival logging. Enter the following command into the command line processor:

db2 => update database configuration for DBSTUDY using 
logretain on

The command should return a message similar to the following:

DB20000I  The UPDATE DATABASE CONFIGURATION command 
completed successfully.SQL1363W One or more of the parameters submitted for
immediate modification were not changed dynamically.
For these configuration parameters, all applications
must disconnect from this database before the changes
becomeeffective.

As the message implies, you have to disconnect all applications from the database for the changes to take effect.

You can also enable archival logging using the Database Logging wizard of the Control Center as shown in Figure 3.

Figure 3: Enabling Archive Logging via the Database Logging Wizard

You can backup your database via the command line with the following syntax:

db2 => backup database DBSTUDY user db2admin using db2admin to 
c:dbbackup

This syntax backs up your database to c:dbbackup. If the backup operation is successful, you should receive a notification like the following:

Backup successful. The timestamp for this backup image is : 
20040320123838

Be sure to keep the timestamp of the backup operation in a safe place. You’ll use it later.

A Table to Be Recovered
To learn how to recover a dropped table, you obviously need a table to work with first. The following syntax creates a simple table consisting of one column that holds names:

db2=> create table RECOVERME(NAME varchar(55) not null 
primary key)

Now, populate the table with a row of data:

db2=> insert into RECOVERME values('Kulvir Singh Bhogal'). 

To showcase the power of the table recovery feature, create another table and add a row to it also (you’ll see why later):

db2=> create table ANOTHERTABLE(animals varchar(55) not 
null primary key)

Next, insert a row:

db2=> insert into ANOTHERTABLE values('Giraffe'). 

Now “accidentally” drop the RECOVERME table:

db2=> drop table RECOVERME

The Recovery Process
Pretend you don’t remember the name of the table you dropped. You can find out with the following command:

db2=> list history dropped table all for DBSTUDY

This syntax gives you information about tables that have been dropped, including the name of the table space that housed them and backup IDs for the tables (see Figure 4).

Figure 4: Results of the List History Dropped Table Command

Figure 4 also shows the DDL (Data Definition Language) necessary to reconstruct the table you dropped. Take note of this for later.

Now you can restore your database from the backup image you made earlier using the RESTORE backup command. This can be either a database-level restore or a tablespace-level restore. When deciding which one to choose, understand that tablespace-level restore requires exclusive access only to the tablespace and not the entire database during the recovery. The following syntax restores your database tablespace (USERSPACE1) using the backup image you created before:

db2 => restore database DBSTUDY tablespace (USERSPACE1) 
from "c:dbbackup" taken at 20040320123838 without
rolling forward without prompting

You can also graphically perform the restore operation using the Restore Data wizard of the Control Center (see Figure 5).

Figure 5: The Restore Data Wizard

At this point in time, if you tried to query for data in the RECOVERME and ANOTHERTABLE tables, DB2 would inform you that the tables do not exist. You need to rollforward to the end of your logs to get back to the point of your last data transaction (i.e., the insert on ANOTHERTABLE). You do this with the following command, which instructs the database to recover the dropped table’s data to c:dbback (specifying which table with the backup ID obtained from the list history statement):

db2 => rollforward database DBSTUDY user db2admin using 
db2admin to end of logs
and complete recover dropped table 000000000000af000002002
to c:dbbackup

If the rollforward operation completes successfully, you should see a confirmation like the one Figure 6 shows.

Figure 6: Confirmation That the ROLLFORWARD Command Completed Successfully

Now you’re at the point where you can see ANOTHERTABLE and its data. However, you still cannot see the RECOVERME table since by this time in your database history, it had been deleted. You should confirm this by issuing a couple of simple SELECT queries.

You can use the information from the DDL statement you saw in the LIST history command to reconstruct the RECOVERME table:

DDL: CREATE TABLE "DB2ADMIN"."RECOVERME" ( "NAME" 
VARCHAR(55) NOT NULL ) IN "USERSPACE1" ;

In your ROLLFORWARD operation, you specified the predicate of:

and complete recover dropped table 000000000000af000002002 
to c:dbbackup

When you did that, you created subdirectories under your export directory (i.e., c:dbbackup). The subdirectories are named NODEnnnn (where nnnn represents the database partition or node number), and the data files created house your dropped table data in a subdirectory called data. Take a look at this backup file (see Figure 7).

Figure 7: Peeking into Your Backup Table Data

Importing the Table Content
Now that you have reconstructed your table, you can import data from where you backed up earlier. You can perform this data import via the command line with a statement like the following, which grabs the data from your backup file:

db2=> IMPORT FROM C:dbbackupNODE0000data OF DEL METHOD P 
(1) MESSAGES C:importlog.msg INSERT INTO
DB2ADMIN.RECOVERME (NAME)

The table data import process can also be performed using the Control Center (see Figure 8).

Figure 8: Using the Control Center to Import Your Dropped Table’s Data

At this point, you can confirm that your data recovery process was successful with a query of your RECOVERME table (see Figure 9).

Figure 9: Confirming Your Efforts Were Successful

Avoid Disaster with a Little Due Diligence
Inadvertently dropping a database table can be catastrophic. Fortunately, DB2 has a built-in, easy-to-use dropped table recovery feature that can save the day (or some heads from rolling). You have to do some work upfront to make your critical tables recoverable, but as you have seen, doing so is a pretty painless process that DB2 administrators definitely should consider.

devx-admin

devx-admin

Share the Post:
Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW)

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will

Renesas Tech Revolution

Revolutionizing India’s Tech Sector with Renesas

Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings.

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional