Introduction

Almost every DBA has had that sinking feeling when you realize that circumstances beyond your control have damaged or caused the loss of data in your database. Suddenly your only priority becomes helping to recover from the unexpected situation. Anything you can use to make the recovery task easier serves to shorten the length of the outage. In the long term, having recovery management become a more natural part of day-to-day activities reduces stress when the inevitable does occur and enables you to recover more quickly.

This article tells the story of a DBA named Erik. Erik works at JK Enterprises Great Outdoors (a fictitious company) and uses IBM Data Studio Administrator to back up the company's critical sales database. After bad application logic accidentally deletes data from multiple tables, Erik comes to the rescue and restores the database. He is also able to roll the database forward to a point in time just before the application did its damage.

The capabilities described in this article are available with the no charge version of Data Studio Administrator, which you can obtain by downloading the Data Studio Administrator trial and not enabling the Database Change Management feature. The advantage you gain by installing and buying the Database Change Management feature is that it provides support for extended alters, including data preservation and management of dependent objects such as constraints, views, triggers and referential integrity.

Here is an overview of the backup and recovery steps that are described in this article:

1. If not connected, establish a connection to the affected database.
2. View data to see what rows you have in your tables.
3. Use the backup task assistant to back up your system.
4. Run sample delete statements to reproduce the bad application logic that deleted all rows in two tables.
5. Use the restore task assistant to restore the database and roll forward to a point in time before the delete statements were run.
6. Look at updated row counts to verify that the data is intact after the restore operation.

Back to top

Prerequisites

Use these steps to install and configure an environment you can use to follow along with Erik's scenario:

1. The scenario requires that you have Data Studio Administrator Version 2 Release 1 with Fix Pack 1 installed.

   If you do not have Data Studio Administrator Version 2 Release 1 installed, use the link in the Resources section to download the trial version of the product.

   If you have not installed Fix Pack 1 onto your Data Studio Administrator Version 2 Release 1 system, use the link in the Resources to get the Fix Pack 1 installation files and instructions.

2. You must also have a supported version of DB2® installed. If you do not have DB2, use the link in the Resources section to download DB2 Express-C, which is a no-charge version of DB2 Express Edition.
3. Follow these steps to create a sample database named GSDB:
1. Get the GSDB_Database.zip file from the Download section of this article and extract the GSDB_Database.sql file from it.
2. Open a DB2 Command Window.
3. Navigate to the location where you saved the GSDB_Database.sql file.
4. Enter the following command:
   db2 -td~ -f GSDB_Database.sql
4. Change from default circular logging to use archive logging. To use archive logging, you must use the UPDATE DATABASE CONFIGURATION command to set LOGARCHMETH1 to a value other than OFF, as shown here:

   UPDATE DB CFG FOR GSDB USING logarchmeth1 "DISK:C:\GSDB_ARCHIVE" failarchpath \ "C:\GSDB_failure" logprimary 2 logsecond 0 logfilsiz 1024;

   
   

   Note: The backslash character at the end of the first line in the command shown above is used only as a mechanism to indicate a line continuation. Do not enter the backslash as not part of the command. You would actually enter the command as one continuous string in the DB2 Command Window.

   
   
   

   The above command specifies:

   • Use archive logging.
   • Save archive logs to the GSDB_ARCHIVE directory.
   • If archive logging fails, write to the temporary directory called GSDB_failure.
   • There are two primary logs and no secondary logs.
   • The primary log size is 1024 4KB pages.

   During one of the steps of the scenario, you perform a full offline backup of the database after changing the logging option.

The following sections of the article describe the major events of the day for Erik and provide details of the actions he takes. The details are worded as step-by-step instructions that you can follow to gain your own hands-on learning experience.

Back to top

Connect to the database

All Data Studio products use a common Data Source Explorer to connect to databases. The first task Erik performs in this scenario is to establish a connection to the GSDB database as follows:

1. In Data Source Explorer, right click on Database Connections and select New… as shown in Figure 1.
   
   Figure 1. Connecting to the database from Data Source Explorer
   
   
   
2. Verify the connection by entering the required connection parameters and clicking the Test Connection button as shown in Figure 2.
   
   Figure 2. Verify the connection
   
   
   
3. If the connection is successful, click the Next button.
4. From the Filter screen, make the selections to include the GOSALES and GOSALESCT schemas and click the Finish button as shown in Figure 3.
   
   Figure 3. Filtering by schemas
   
   
   

Back to top

View number of rows in your table

Once connected to the GSDB database, Erik wants to view the row count of selected tables.

This scenario uses the row count as an indicator of the current state of the tables. After you perform the restore operation, you can use the count to verify that the table is back to this same state.

To view the row count of the selected tables, do the following:

1. In Data Source Explorer, double click the Tables folder for the GSDB database as shown in Figure 4.
   
   Figure 4. Double-click on Tables
   
   
   
2. The Object List window now shows the table schema, table name, and row count for each table as shown in Figure 5. Note that the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables have 274 rows and 2728 rows, respectively.
   
   
   Figure 5. Object List window contains row counts
   
   
   
3. Optionally, another way to do this while also being able to see the actual data, is to right-click an individual table and choose Data > Return All Rows as shown in Figure 6.
   
   Figure 6. Returning all rows for an individual table
   
   
   

Back to top

Back up the database

Erik now performs the following steps to back up the GSDB database:

1. In the Data Source Explorer, right click on the GSDB database and select Backup… (as shown in Figure 7) to launch the Backup task assistant.
   
   Figure 7. Choose Backup from Data Source Explorer
   
   
   
2. On the 1. Type tab, select Back up the entire database as shown in Figure 8.
   
   Figure 8. Choose backup type from Backup task assistant
   
   
   
3. Select the 2. Image tab.
4. Select File System as the media type from the dropdown menu (Figure 9).
5. Click the Browse button and select C:\GSDB_BACKUP as the location for the backup image (Figure 9).
   
   
   Figure 9. Select backup media type and location
   
   
   
6. Select the 3. Options tab.
7. Select Full backup – Back up all data for Backup type (Figure 10).
8. Select Offline - Users cannot access the database during the backup for Availability. After making this selection, the quiesce checkbox option is enabled. Select the checkbox (Figure 10). You need to perform a full offline backup because after changing the logging type, this type of backup is required in order to later be able to perform a restore to point in time.
   
   Figure 10. Selecting backup options
   
   
   
9. Click Preview Command to generate and view the commands in the Command window as shown in Figure 11.
   
   Figure 11. Preview of backup commands
   
   
   
10. Click the Run button .
11. Data Studio Administrator should return a success message for the backup as shown in Figure 12.
    
    Figure 12. Message window shows success
    
    
    

Back to top

Oops!

Later during the day, Erik looks at the application logs and discovers that bad application logic has deleted all the rows from the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables. He verifies this by going to the Object List window and seeing that the row count for both tables is 0 as shown in Figure 13. He knows there is a problem because these tables previously had 274 rows and 2728 rows, respectively (see Figure 5).

In order to follow along with Erik as he recovers his data, you first need to reproduce the deletion of the rows by entering the following DB2 commands:

delete from GOSALES.PRODUCT
delete from GOSALES.PRODUCT_FORECAST

Make a note of the time so that you will be able to recover to a point before you executed the delete commands.

Back to top

Restore to a point in time

Erik is now anxious to use the capabilities of Data Studio Administrator to restore the database to its proper condition. To put the database in its original state before the bad application logic occurred, Erik performs a restore of the database and a roll forward to a point in time by following these steps:

1. In the Data Source Explorer, right click on the GSDB database and select Restore… (as shown in Figure 14) to launch the Restore task assistant.

1. On the 1. Type tab, select Restore to an existing database and Restore the entire database as shown in Figures 15 and 16.
   
   Figure 15. Selection for restoring to an existing database
   
   
   
   
   Figure 16. Selection for restoring the entire database
   
   
   
2. Select the 2. Available Images tab.
3. Select Select the backup images from a list (Figure 17).
4. From the list of backup images, select the most recent image prior to when the bad application ran (when you deleted the table rows) and click the right arrow button to move the backup image to the right box (Figure 17).
   
   Figure 17. Choose most recent image before bad application ran
   
   
   
5. Select the 4. Roll forward tab.
6. Select Restore the database and roll forward as follows (Figure 18).
7. Select Roll forward to a point in time – Local (Figure 18).
8. Enter appropriate values for the Roll forward to transaction date and time. The date and time should be prior to when the bad application ran (when you deleted the table rows).
   
   Figure 18. Select roll forward time to before bad commands were executed
   
   
   
9. Select the 5. Final State tab.
10. Select Complete the restore and return the database to the active state as shown in Figure 19.
    
    Figure 19. Choosing to return database to active state after roll forward completes
    
    
    
11. Click Preview Command to generate and view the commands in the Command window, as shown in Figure 20.
    
    Figure 20. Preview of Restore commands
    
    
    
12. Click the Run button and watch the results appear in the Messages and SQL Results window as shown in Figure 21.
    
    Figure 21. Progress and status of the restore
    
    
    

Back to top

Verify the restore

Erik lets out a sigh of relief! He has restored the database to the state before the bad application logic occurred. Erik verifies this by using Data Studio Administrator to check the table row counts as follows:

1. In Data Source Explorer, double click the Tables folder.
2. The Object List window now shows the GSDB database tables. Right click on the GOSALES.PRODUCT table and select Update statistics as shown in Figure 22.
   
   Figure 22. Choose Update Statistics to refresh row count
   
   
   
3. Click in the SQL Results window. This refreshes the Object List window with current row counts.
4. Use the same process to refresh the current row counts for the GOSALES.PRODUCT_FORECAST table (right click on the table, select Update statistics, click in the SQL Results window).
5. As shown in Figure 23, the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables are now back to the same counts as before the bad application logic occurred (274 rows and 2728 rows, respectively).
   
   Figure 23. Rows have been Recovered
   
   
   

Back to top

Conclusion

Data Studio Administrator provides database administrators with a useful tool to perform regularly scheduled backups as part of their backup and recovery strategy. These backups can be used to restore databases to their proper state after data corruption events, such as software failures, hardware failures, bad application logic, and user errors.

Erik has verified that the database was successfully recovered. With the crisis avoided, he decides to reward himself by taking a stroll to the corner café and enjoying an iced double tall caramel mocha latte.

Back to top

Download

Information about download methods

Resources

Learn

• Understand how Data Studio Administrator fits in with the rest of the Data Studio portfolio in this article: “IBM Data Studio software: The big picture.”
  
  
• Understand Data Studio packaging, including the no-charge administrative capabilities available in the Data Studio Administrator download by reading this article: “Understanding Data Studio software packaging.”
  
  
• Find out all the new capabilities in Data Studio Administrator: “What’s new in Data Studio Administrator 2.1.”
  
  

Get products and technologies

• Download a trial version of Data Studio Administrator.
  
  
• Download and install Fix Pack 1 for Data Studio Administrator 2.1. The fix pack is required for the capabilities described in this article.
  
  
• Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  

Discuss

• Data Studio Team blog
  
  
• Data Studio Community space
  
  

About the authors

Sailaja Bandlamoori is the QA Team Lead for Data Studio Administrator in the Information Management Data Studio group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the Data Studio Administrator product since 2006. Prior to that, she worked in DB2 for z/OS client/server System Test.

TaWanna Harris is a QA Staff Software Engineer in the Information Management Data Studio group at Lenexa, KS. She has worked on the Data Studio Administrator product since 2008. Prior to that, she worked on IBM Migration Toolkit, partner and customer application testing, and interoperability testing.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Connect to the database
• View number of rows in your table
• Back up the database
• Oops!
• Restore to a point in time
• Verify the restore
• Conclusion
• Download
• Resources
• About the authors
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers