Mimicking Production Database

If you are a DB2 DBA, most likely you have already used db2look to extract the DDL from a DB2 database. However, db2look can do more than just extracting the DDL. Sometimes you may want to create a test database similar to production database in order to test your applications and query access plans. If the database is small, you may create a test database by restoring the production database backup image. What if the database is not small? Creating a test database of same size as production database may be cost and time prohibitive. In that case, you may want to have only a subset of production data loaded into the test database. Or, you may even want to have dummy data loaded into your test database instead of actual production data due to security reasons.

For the reasons explained above, you may have to create a test database which will not have exactly the same data as your production database. DB2 has a cost based optimizer and if the data is different in the test database, you may get different query access plans than the production database. This might be okay, if you are only testing the functionality of an application. But what if you have to test the performance or debug some SQL performance related issue? In that case, you would want to have the same query access plan in your test database as it will be in your production database. What’s the solution now? The solution is to give the production environment feeling to DB2 optimizer even in your test environment. How do we do that? DB2 optimizer generates access plan based on following

· Statistics

· Configuration parameters

· Registry and environment variables

You can capture the above from production database and apply to your test database. You can use db2look for this purpose. The –m option of db2look can be used to create UPDATE statements against the updatable catalog tables under the SYSSTAT schema as well as RUNSTATS command against all of the tables. The –f option of db2look can be used to generate necessary configuration updates and set statements for registry and environment variables. Check the following link at DB2 Information Center for more details

Mimicking databases using db2look

The above approach of mimicking database for performance testing has another advantage. Once you set up a test database, you will have to update only the statistics in the test database if the volume of data in production database changes over time. Updating the statistics only will take much less time than rebuilding the test database.