Creating database duplicates

Creating production database duplicates in a test environment allows you to test upgrading your databases before you upgrade them in your production environment.

Before you begin

Ensure that you have SYSCTRL or SYSADM authority.

About this task

This procedure uses DDL scripts to create database duplicates. If you have enough resources, you can also create database duplicates by restoring a database backup to create a new database.

Procedure

To create a database duplicate for testing database upgrade:

  1. Log on as the instance owner on the production database server and use the db2look command to generate DDL scripts with all the existing objects in your databases.
    The following command shows how to generate the sample.ddl script for the SAMPLE database: 
    db2look -d sample -a -e -m -l -x -f -o sample.ddl
    Edit the generated DDL scripts and change:
    • The database name in the CONNECT statements
    • The path of the user table space containers or data and reduce the sizes to a minimum size since to re-create a database with no data or just a data subset
    You can use your own DDL scripts to create test databases in the test instance instead of generating DDL scripts.
  2. Log on as the instance owner in the test database server and create your database duplicates.
    The following example shows how to create a database duplicate of the SAMPLE database using the sample.ddl script: 
    db2 CREATE DATABASE NSAMPLE
db2 -tvsf sample.ddl
db2 UPDATE DBM CONFIGURATION USING diaglevel 4

    All significant upgrade events are logged in the db2diag log files when the diaglevel database manager configuration parameter is set to 3 (default value) or higher. A value of 4 captures additional information that can be helpful in problem determination.

  3. Adjust the size of the system catalog table space, temporary table space, and log space in your test databases if required.
    Refer to Increasing table space and log file sizes before upgrade.
  4. Export data subsets of your production databases and import these data subsets into your test databases.
    You only need a data subset if you are going to test your applications in your testing environment.
  5. Verify that your database duplicates were created successfully by connecting to the them and issue a small query.