Cataloging databases after reinstalling DB2

On Windows, uninstalling DB2 will remove the instance but keep the databases in place. After you reinstall, you’ll want to recatalog these databases. This is fairly straightforward.

Find out their names:

Map each of the databases that you want:

You can also catalog remote databases the same way. In my experience, this is much easier than going through Control Center:

The database will now show up in your Control Center catalog. You will also be able to connect to the local alias from Data Studio and other tools.

Going from DB2 Enterprise to DB2 Express-C

Downgrading from DB2 Enterprise to DB2 Express-C is a fairly straightforward process.

Stop DB2.

Uninstall DB2 Enterprise.

Install DB2 Express-C without creating any users, etc.

Update the instance with the new edition information.

You might encounter this error.

Look up your DB2 TCP/IP service name. It should be similar to db2c_db2inst1 where db2inst1 is the instance owner.

Make sure it’s set correctly in the DB2 configuration.

Start DB2.

You should now have a working DB2 Express-C installation with all the same databases.

Enable NFS services for HADR

You might need to enable network file system (NFS) services when configuring the High Availability Disaster Recovery (HADR) or Database Partioning (DPF) DB2 features. For example, with HADR, you might want to set up a slave database that shares a backup with the master database through NFS.

These commands should enable NFS services on most distributions of Linux:

Some of the services might not exist on your distribution. Additional steps may be needed.

IBM Smart Business Cloud for Test & Development might be one of the environments where you’d have to do this.

Simplify DB2 Express-C security

Starting in DB2 9.7, the DB2 security model was changed. Security administration (SECADM) was split off from database administration (DBADM), and users with SECADM permissions could no longer peek at data, at least not by default.

Secure by default is a great practice. It’s fantastic when deploying a production database at a financial institution. However, it’s not so good when you are a new user trying to learn a tool.

Control Center seems to run as SYSTEM by default on WinXP, which leads to errors like this:

Let’s remedy this by granting lots of privileges all around.

Open up the DB2 Command Window.

Connect to the database as a user that already has permissions (leonsp for me):

Grant all privileges to SYSTEM:

Did you create DB2ADMIN or some other id when installing DB2 on Windows? Let’s grant all privileges to that user as well:

You should now no longer see the SELECT privilege error. Note that you should not do this in a production environment, as this is much less secure than the default configuration.

Cataloging local DB2 databases

On Windows, uninstalling DB2 will remove the instance but keep the databases in place. After you reinstall, you’ll want to recatalog these databases. This is fairly straightforward.

Find out their names:

Map each of the databases that you want:

You can also catalog remote databases the same way. In my experience, this is much easier than going through Control Center:

The database will now show up in your Control Center catalog. You will also be able to connect to the local alias from Data Studio and other tools.

Reference

Going from DB2 Enterprise to Express-C

Downgrading from DB2 Enterprise to DB2 Express-C is a fairly straightforward process.

Stop DB2.

Uninstall DB2 Enterprise.

Install DB2 Express-C without creating any users, etc.

Update the instance with the new edition information.

You might encounter this error.

Look up your DB2 TCP/IP service name. It should be similar to db2c_db2inst1 where db2inst1 is the instance owner.

Make sure it’s set correctly in the DB2 configuration.

Start DB2.

You should now have a working DB2 Express-C installation with all the same databases.

Enable NFS for DB2 DPF or HADR

You might need to enable network file system (NFS) services when configuring the High Availability Disaster Recovery (HADR) or Database Partioning (DPF) DB2 features. For example, with HADR, you might want to set up a slave database that shares a backup with the master database through NFS.

These commands should enable NFS services on most distributions of Linux:

Some of the services might not exist on your distribution. Additional steps may be needed.

IBM Smart Business Cloud for Test & Development might be one of the environments where you’d have to do this.

At Impact 2010

I’m in Las Vegas Saturday through Thursday for Impact 2010. Drop me a word if you are there too.Impact 2010

Having built the DB2 images for WebSphere CloudBurst, I’ll be helping Dustin Amrhein run a lab on Tuesday:

Hands-on Lab with WebSphere CloudBurst Appliance
Tuesday at 1:30 PM
TDC-1369A
Venetian, San Polo 3403

I’ll also spend a lot of time demoing and discussing DB2 and WAS cloud images at the RightScale pedestal:Getting Started

Run DB2 and WebSphere in the Cloud with RightScale
Demo at ped A3

Oh, and if you are looking for a hard copy of the legendary Getting Started with DB2 Express-C book, be sure to drop by.