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.