Multiple Instances in DB2 for Linux/UNIX and in DB2 for Windows

Posted by

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.

The Basics

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.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home


  1. HI Ember,
    How are u.I have a small doubt when creating instance like can i assign multiple fenced users for single instance.If yes,May i know how to do that(can u name out syntax also)

    AVManoj Kumar

    1. I always go with one fenced user for each instance. I don’t think you can have more than one for a single instance. You could theoretically use the same fenced user for multiple DB2 instances on a server, but I think that would be a bad idea.

  2. 1 server with 5 instances, each instance different version, how we can list out all the instances with that versions in 1 command ???

    1. There is no way that I know of to do it. db2ilist would only list the instances for the code copy or path that it was executed from.

  3. How to connect to different instances in a windows server?? eg. I executed db2ilist command on the db2CLI on windows server and got 3 instances DB2, QUIDRO & PLCAT. but by default it connected to only DB2 and showed its databases. Now I want to connect to other instances. How to do that, Kindly advise..!!

    1. If they’re all on the same copy, you simply need to set the DB2INSTANCE environment variable. This can be done in a command window using `set DB2INSTANCE=QUIDRO` or at a PowerShell command line using `set-item -path env:DB2INSTANCE -value QUIDRO`

  4. Hi Ember, i also try to create an instance for my database. When created node in the default instance DB2, everything is working. I can create ODBC connection and access the database.
    But when i create an instance (in the same copy) and configure the node in it, I can’t access the database. I configured in ODBC also the advanced properties: Instance, Protocol and Database. But it doesn’t work and prompt me: SQL1032N No start database manager command was issues SQLSTATE=57019
    Any tips ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.