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.
To create a database duplicate for testing database upgrade:
- 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:
Edit the generated DDL scripts and change:
db2look -d sample -a -e -m -l -x -f -o sample.ddl
- 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
- 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.
- Adjust the size of the system catalog table space, temporary table space, and log space in your test databases if required.
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.
- Verify that your database duplicates were created successfully by connecting to the them and issue a small query.