I was reviewing my many hundreds of slides that I use when training DBAs, and realized that some of my “Basics” slides might lead to good blog topics. My single most popular blog post ever is “How to Catalog a Database”, which is part of my “Basics” series of slides. While directed at Newbies, I bet any of my more experienced readers could find things to argue about, contribute to, or discuss as well, especially on this topic – “What is an Instance?”.
Comparing with Oracle
To be honest, experienced DBAs don’t spend a lot of time thinking about what an instance is. It is only when describing it to someone else – in training or otherwise – that it even comes up. You know what an instance is because you work with it every day. You know what kinds of configurations can be done at the instance level, and so on.
When I took an entry level Oracle class a few years ago, Oracle’s definition of an instance made me realize how much more difficult it is to define in DB2. With Oracle, you can only have one database per instance. In that context, essentially instances and databases are aspects of the same thing. For Oracle, at the most basic level, the instance refers to the processes, threads, and shared memory while the database refers to the actual files on disk.
With DB2, we have memory areas that are associated with the instance and those some that are associated with the database, so the same definition sure doesn’t hold true. We also have the possibility of multiple databases on a single instance.
Comparing with MS SQL Server
I’m sure not an expert on MS SQL Server, but my understanding is that there is also the concept of an instance there, but each instance requires an entirely separate installation of MS SQL Server code. For DB2, we can create many instances from just one installation.
What is an Instance, for DB2?
My favorite definition for an instance comes from a blog I used to follow regularly. Chris Eaton, on his blog, defined an instance as:
An instance in DB2 for LUW is like a copy of the RDBMS including all the processes that run DB2 and memory (address spaces) associated with that instance of DB2 and some configuration parameters (ZPARMS) to control that instance. Think of it as a copy of the DB2 code running on a server. You can have as many instances as you like running on a single server.
A DB2 instance includes copies of some files from the DB2 installation, and links to others. A DB2 instance includes memory areas – memory areas that contain all the memory areas for all of the databases. A DB2 instance includes processes/threads. Some processes/threads are associated with specific databases and even specific connections, but they are all tracked back to just one instance.
A DB2 Instance also stores information specific to that instance, in the instance owner’s home directory, including:
- Node directory
- Database directory
- db2diag.log, db2 notification log, and dump files
- DBM configuration file
The DB2 Info Center defines an instance:
An instance is a logical database manager environment where you catalog databases and set configuration parameters. Depending on your needs, you can create more than one instance on the same physical server providing a unique database server environment for each instance
A very utilitarian definition.
Any user who wishes to execute DB2 commands from a shell or command line first sources the db2profile associated with that instance. The sourcing is often hidden from the user by being in their .profile or .bash_profile.
The Instance Owner
There is a special user associated with the instance called the instance owner. The instance itself is created by root (assuming a root installation), but the instance owner is specified on creation.The instance owner cannot be changed once an instance is created, and neither can the instance’s home directory. These are things that must be defined before instance creation.
At times there have been things that the instance owner could do that no other user could do. When the db2pd command was introduced, you had to be the instance owner to execute it – simply SYSADM could not. That has changed, and now db2 can be executed with SYSADM or a few other levels of authority.
The instance name is identical to the instance owner – there is no way to separate them. When creating an instance, you must first ensure that the instance owner has the right primary group and the right home directory.
h2>Starting and Stopping
The db2 instance is started and stopped – doing so causes only the instance memory areas to be instantiated, and this also starts listeners for network protocols such as TCPIP that may have been enabled for the instance. This is in contrast to Oracle, where the TCP/IP listener is started separately from the instance. With Oracle, you can even have one listener serving more than one instance. In db2, there is only one listener for each instance, each listener can serve only one instance, and it is started when the DB2 instance is started.
How Multiple Instances are Used
- To have different sets of parameters (as required by different databases or applications) – tuning
- To prevent failure of one database/instance from impacting another database/instance
- To represent Production and Test environments separately on the same server
- To separate permissions – high level permissions are defined at the instance level
- To separate versions or FixPacks – in practice, I’ve only seen than done where more than one development environment is on a box, and we need to be able to test a Fix Pack in one but not another or in each in turn.
DB2 instances allow one to have multiple completely separate DB2 operating environments from one set of installation code on a server. The instance itself is comprised of files, processes, memory areas, and configuration parameters.