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.

DB2 Autonomic Computing

One of the highlights of DB2 Version 9.5 is “Manage your business, not your database”. IBM claims that the DB2 autonomic computing environment is self-configuring, self-healing, self-optimizing and self-protecting. Autonomic computing is not new in Version 9.5. Past several releases of DB2 have consistently added enhancements in this direction. Just look at the database and database manager configuration parameters in Version 8.1, 8.2, 9 and 9.5 and you can see a trend of how more and more configuration parameters have started supporting the AUTOMATIC setting. Some of the other important features under autonomic computing are Self-tuning memory manager, Automatic storage, Automatic database backup, Automatic statistics collection, Configuration advisor, Health monitor and Utility throttling.

However, somehow I feel that not all the autonomic features in DB2 are widely used. For example, most of the DB2 DBAs are taking their database backup using scheduled scripts rather than using the Automatic database backup. It would be interesting to know which autonomic features are really getting embraced and which ones are not so popular. So, I have added a poll (on the right side panel of my blog) to check which DB2 autonomic features you use in your environment. You can select more than one options in this poll. Also, it would be great if you can provide comments here about why you like or dislike a particular autonomic computing feature.

DB2 Installation Enhancements

Prior to Version 9, the installation path of DB2 was fixed on each platform. As a result, you could not have multiple copies of DB2 on the same computer under a single OS. DB2 Version 8 introduced a patchy solution to this problem in the form of Alternate Fix Packs, which allowed coexistence of multiple Fix Pack levels of DB2 version 8 on the same computer. However, now you don’t have to deal with Alternate Fix Packs and you don’t have to get frustrated with the stiffness of DB2 Installation any more.

With DB2 Version 9, you can use multiple DB2 copies on the same computer. Each DB2 copy can be at the same or different code levels. We can also have DB2 Version 8 and Version 9 on the same computer. IBM has listed the following benefits of doing this (which sounds reasonable):

• The ability to run applications that require different DB2 versions on the same machine at the same time.
• The ability to run independent copies of DB2 products for different functions.
• The ability to test on the same computer before moving the production database to the latter version of the DB2 product.
• For independent software vendors, the ability to embed a DB2 server product into your product and hide the DB2 database from your users.

A DB2 copy can contain one or more different DB2 products. This refers to the group of DB2 products that are installed at the same location.

How is this possible now? You might have already guessed: now you can choose your own path while installing DB2. Each DB2 Copy has its own installation path. That’s cool, but now how will you find out all the installed DB2 products? DB2 Version 9 on Linux and UNIX comes with a command db2ls, which you can use to list:

• Where DB2 products are installed on your system and list the DB2 product level.
• All or specific DB2 products and features in a particular installation path.

Unfortunately, db2ls is not available on Windows. However, on Windows you can find all the installed DB2 copies and their installation path in Windows Registry under HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies.

Another notable enhancement for DB2 Installation is non-root installation on Linux and UNIX. Non-root installation is available with DB2 Version 9.5. You can check the details of it at the link: Non-root installation overview. However, there are several limitations of non-root installation. Apart from limitations, there are few differences between the directory structure of root and non-root installations.

More good news: Fix Pack post-installation tasks are automated on Linux and UNIX in DB2 Version 9.5. In previous releases, you had to update instances and bind the packages manually after installing a fix pack. Now you don’t have to run db2iupdt or dasupdt commands to update the instances after installing a fix pack, they will be performed automatically when you install the fix pack. In addition, binding occurs automatically at the first connection. As a result, once you start the database manager, the DB2 product is ready to use immediately after fix pack installation. You may check the details of applying fix packs at DB2 Information Center.

How to Recreate DB2 Instance

Can we drop and recreate a DB2 instance without restoring all the databases underneath? The answer is YES. This is not a very common requirement, but once I ended up doing this. I had few DB2 version 8.2 single partition instances of type WSE (Workgroup Server Edition). I needed to use db2_all command with these instances to have my automation scripts work with these instances (You may read my previous posting “db2_all and RAHUSER” to understand the use of db2_all even for single partition instance). However, db2_all does not work with WSE instance and hence I needed to convert these instances from WSE to ESE (Enterprise Server Edition). I found that the installed product and license was good for allowing an ESE instance. Only problem was that the instances were created with WSE option.

Initially, I thought of using db2iupdt command for this as I remembered converting 32-bit instances to 64-bit using db2iupdt. Unfortunately, db2iupdt did not have any option to convert a WSE instance to ESE. I could not find any other one step command to perform this task. The databases under these instances were small non-production databases. Hence, I thought even if I will have to recreate all the instances as ESE and restore all the databases underneath, I would go for this instance conversion from WSE to ESE. The next question was whether I really need to restore all the databases after recreating the instance. When we drop an instance using db2idrop command, it does not drop the databases underneath. So if we recreate the instance, we should be able to re-catalog the databases under the newly created instance and it should be good to go. Logically this sounded good, but I had never done it before. So, first I tested it using the steps listed below and it worked fine. I used the same steps to convert all my WSE instances to ESE and did not have to restore the databases underneath.

1. Capture the details of databases cataloged under the instance (using “list db directory”).
2. Capture the Database Manager configuration (using “get dbm config”) and registry variables (using “db2set –all”).
3. Copy any files that you may need to refer or use with the newly created instance (e.g. files of external routines, any script, log files etc.).
4. Ensure a good backup of local databases under the instance (in case something goes wrong, backup will be your savior).
5. Stop the instance.
6. Drop the instance (using db2idrop command).
7. Recreate the instance as per your requirement (using db2icrt command).
8. Make the Database Manager configuration and registry variables same as original instance using the info captured in step-2.
9. Copy any required files captured in step-3 under the same path as it was with the original instance.
10. Start the instance.
11. Catalog the databases captured in step-1 under the newly created instance (using catalog database command).

If for some reason, you also need to drop and recreate your instance using the above steps, I would strongly recommend you to test it first in your specific environment. You may require few additional steps in your environment that you may discover during your testing.

Hope you enjoyed the holidays. Wish you very happy New Year.