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.
Sometimes, changing the current instance from the same user is not easy nor fast. In order to improve this task, I created a script that ease this process: https://github.com/angoca/db2admintools/blob/master/bin/db2env
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)
Thanks&Regards
AVManoj Kumar
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.
can i add multiple fenced ids to single instance??if yes can u pls say.
1 server with 5 instances, each instance different version, how we can list out all the instances with that versions in 1 command ???
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.
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..!!
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`
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 ?
I wish I had a solution for you, but I don’t have enough experience or the time to play with it right now.
Hi Ember, I prefer to limit the number of databases and instances that I need to management. Where I work we have several high use OLTP applications that all run in production in the same db2 instance on the same server. We do get the occasional bad query that consumes a lot of cpu and can affect the other running applications. I have suggested that we need to start using the DB2 WLM to limit cpu consumption across applications in the instance (it is not used currently).
The other option is to separate the applications into their own database/instance/server which provides complete separation but would result in possible under utilization of resources and significant additional admin for upgrades, fixpacks and DR (We use HADR from the single database currently).
Do you have any thoughts on the best practices in this area?
I’m becoming a huge fan of running databases in containers or EC2 instances. These make it easier to run one database per “server”, and with infrastructure as code, fixpacks and upgrades become at least somewhat easier. Containers aren’t quite there for Db2 outside of OpenShift, but they really are a dream for managing many individual environments. The world really is going to many smaller databases, and with many hosting or even cloud providers, changing the size of your hardware is so much easier than it used to be. I know that’s not the answer for everything. WLM is a particularly good option when reporting and OLTP workloads share the same database.
One interesting solution if you’re already using HADR is to have some databases with one server as primary, and other databases with the other. This gives you more capacity most of the time, as long as degraded performance is acceptable in the case of a failover.
“Best Practice” in my book is simply one database, one instance, one “server”, where the server can be virtual or a container. Configurations beyond that require a careful evaluation of SLAs, resources, and priorities.
Thanks for your reply Ember, much appreciated. We do run one database/instance/server but have multiple OLTP applications running within. It is the cpu limiting and separation of applications that we intend to use WLM for.
The HADR idea is interesting but probably won’t work for us as we are primarily using this for DR purposes and the standby needs to be able to support full capacity at all times.
Thanks, Geoff
Yeah, that only works for HA standbys, not for DR standbys.