Section 13.13. Case Study

13.13. Case Study

You have just been assigned a new task: to clone the production database PROD1 on a Windows server to another Windows test server. These are the criteria:

• The cloned database name is TEST1.

• The containers for TEST1 must be redefined. They must reside on the D: drive of the test server.

• TEST1 must contain the most recent data in PROD1.

1. First, take a backup of the database PROD1. Because PROD1 is a 24x7 production database, it is not possible to perform an offline backup. Therefore, you decide to perform an online backup:

    db2 backup db prod1 online to d:\temp 

   
   

   The backup image is created as:

    D:\temp\PROD1.0\DB2\NODE0000\CATN0000\20040414\170803.001 

   
   

2. Obtain the table space container information on PROD1. This information is needed to define table space containers for the test database.

   1. First, list all table spaces in PROD1, as shown in Figure 13.19.

      Figure 13.19. Getting table space information from PROD1

      
      
      

   2. Then, list the containers for each table space, as shown in Figure 13.20.

      Figure 13.20. Getting container information from PROD1

      
      
      

      Since all three table spaces are SMS table spaces, all the containers are directories.

3. Transfer the backup image over to the test server. FTP the backup image, including the folders and directories starting at D:\temp, over to the test server.

4. Perform a database redirected restore and define the table space containers for database TEST1, as shown in Figure 13.21.

   Figure 13.21. Performing redirected restore of PROD1 into TEST1

   
   
   

5. Perform a roll forward on the newly restored database TEST.

   1. At this point, you cannot connect to TEST1 yet. A connect attempt will receive the error SQL1117N.

   2. To roll forward, you need to find out which logs are needed. Run the ROLLFORWARD DATABASE command with the QUERY STATUS option (see Figure 13.22).

      Figure 13.22. Finding out which logs are needed to perform roll forward

      
      
      

      Figure 13.22 shows the next log file to be read is S0000000.LOG. This means that you have to transfer all the logs in the active log directory of PROD1, starting at S0000000.LOG, to the active log directory of TEST1.

   3. To find out where the active log directory is for PROD1, issue the GET DB CFG FOR prod1 command. The active log directory is indicated as Path to log files.

       Path to log files                = D:\DB2\NODE0000\SQL00003\SQLOGDIR\ 

      
      

   4. The logs in this directory are: S0000000.LOG, S0000001.LOG, S0000002.LOG, and S0000003.LOG. Transfer all of these logs to the test server and into the active log directory of TEST1, which is on D:\DB2\NODE0000\SQL00001\SQLOGDIR\. (If you list this directory, you will see that it is currently empty.)

   5. Perform a roll forward operation on TEST1, as shown in Figure 13.23.

      Figure 13.23. Performing the roll forward operation on TEST1

      
      
      

6. Connect to TEST1 and verify that it contains the correct data.