Sunday, January 06, 2008

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.

7 comments:

Anonymous said...
This comment has been removed by the author.
Anonymous said...

Your blog is great
Thanks,
Jack
http://db2examples.googlepages.com/

Anonymous said...

I will not approve on it. I over precise post. Specially the title-deed attracted me to study the whole story.

Anonymous said...

Genial fill someone in on and this enter helped me alot in my college assignement. Gratefulness you on your information.

Ashu said...

Radhesh,

Can you please explain, how did you recreate the Instance?

Regards,
Ashootosh

Radhesh Kumar said...

Hi Ashu,

Sorry for long delay in response, somehow I missed this comment. The heading of this post itself is how to recreate a DB2 instance and I have explained that in this post :)

Can you be more explicit in your question and let me know exactly what information you are looking for (that's not covered in this post)?

Thanks,
Radhesh

Anonymous said...

I congratulate, what necessary words..., an excellent idea