Multiple Instances in DB2 for Linux/UNIX and in DB2 for Windows
I have been working with multiple instances on Windows servers lately, and have learned a few things about them, so I thought an article about multiple instances in general would be a good thing.
First, if you don’t understand what an instance is, check out my earlier blog entry: DB2 Basics: What is an Instance?
When Multiple Instances Work and When They Don’t
In general, for production systems, the one-server -> one-instance -> one database approach is best. This allows us to fully isolate the workload and is easiest for the intense workload related performance problems that may be encountered for production systems. There are exceptions to that, of course. I’ve seen an application that required 8 (EIGHT!) separate, tiny, and not very busy databases. I would certainly not recommend each of those go on a different server, and even put all eight databases on a single instance. But if you’re talking about databases of greater than 10 GB that are reasonably heavily used, the more isolated, the better.
On non-production database servers, multiple instances are much more common. If you have multiple development/test/QA/staging environments on the same non-production server, you need them to be on separate instances. Why? Well, you need to be able to move a change to an instance-level parameter through the enviornments one-by-one to test it. Perhaps more importantly, you need to be able to independently upgrade the instances so you can move an upgrade through the environments.
Sometimes I see HADR standbys for multiple different production databases servers on the same standby server. I’ve also seen Dev and HADR standbys the the same server. Either of these setups require not just separate instances, but the ability for the separate instances to run on different versions or Fixpacks of the DB2 code – you should be upgrading them independently and likely at different times.
Separate DB2 Base Installation Paths/Copies
We talk not just about multiple instances but multiple base installation paths (Linux/UNIX) or multiple copies (Windows). You can have two instances on the same server running on the same path/copy if they will be upgraded together. But if there is some need to upgrade or patch them separately, they need to run on separate paths/copies.
Multiple Instances on Linux/UNIX
The Instance Owner
On Linux and UNIX systems, there is a DB2 Instance Owner. This ID is the same exact name as the DB2 instance, and the DB2 Instance’s SQLLIB directory is always in the DB2 Instance Owner’s home directory. One of the pre-instance creation verification steps is to make sure that this user exists and has an appropriate home directory, so your Instance Home does not end up in someplace inappropriate like the root or /home filesystems.
The Base Installation Path
When I started working with DB2, it wasn’t possible to change the installation path. Then they realized that some servers require the ability to have different instances on different fixpacks, so they introduced “alternate” fixpacks, which were a bit of a pain to work with. Finally, they gave us the option to choose our own installation path, which makes it so that we can have as many different versions and fixpacks as we like on the same server. This also means that whenever you install a fixpack you have to specify the base installation path (using the -b option or as prompted). You can also change an instance from one base installation path to another using a db2iupdt command (offline).
Administering the Instance
DB2 instances are administered separately, as you can really only work on one instance at a time. A DB2 instance is administered either as the DB2 instance owner or as an ID in the SYSADM group for an instance. In order to take actions for the instance or the databases under it, you will execute the
db2profile from the SQLLIB directory of the Instance Home directory. You cannot use the same ID to take actions on two different instances without switching from one to another.
Multiple Instances on Windows
Multiple instance support feels to me like it has come slower to Windows than it did on UNIX/Linux, but I don’t have the facts to support that, as I have only worked extensively with DB2 on Windows in recent years.
The Instance Owner
There isn’t a userid that is quite as tightly tied to an instance as it is on Linux/UNIX. You still have a name for each instance, and can add additional instances on a server.
The DB2 Copy
The equivalent concept to the base installation path on UNIX/Linux is the DB2 Copy on Windows. You’ll have a name for the DB2 Copy in addition to the instance name. By default, this name is DB2COPY1.
Administering the Instance
Many times on DB2 servers, the local administrators will all have SYSADM rights on the database. If not, every ID in the DB2ADMINS group will have SYSADM. You may use the same ID to administer two DB2 instances at once, but any given command window, PowerShell prompt, or DataStudio window only accesses one instance at a time. In a PowerShell window, you can set the environment variable DB2INSTANCE to switch between instances on the same DB2 Copy, and can set the PATH variable along with DB2INSTANCE to switch between instances on different DB2 copies.