Posted by 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.
(http://it.toolbox.com/blogs/db2luw/whats-an-instance-and-where-is-my-subsystem-6457)
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
- db2nodes.cfg
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
<
h2>
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.
Summary
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.
Very useful definition.
very good article indeed
so in DB2 after you defined an instance, can you have multiple databases?
regards
Yes. DB2 has allowed multiple databases per instance for years.
Nice article 🙂 I was explaining this to novice db2 users and related instance concept with listener for simplicity.
–
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.
–
Your article sheds light on how much more the db2 instance is with enough clarity. 🙂
One doubt though, what’s the advantage of having multiple databases in one instance which DB2 provides. Does Oracle also allows multiple dbs in one instance too ?
Best Regards
Shivraj
The advantage of having multiple databases in one instance is usually ease of administration – each instance requires a certain amount of work (each is upgraded separately, usually scripts and other maintenance can run for multiple databases in the same instance, but not for databases in separate instances without root), and if you have a lot of small databases with compatible requirements for instance-level settings, it is easier to have them in one instance. For important databases or large databases one database per instance (per server, even) is better. I’m a firm believer in separate instances in a lot of scenarios, but not all.
Oracle in recent versions has introduced the ability to have more than one database in an instance (called transportable databases) – BUT because of their different definition of an instance, this means that all databases in an instance share the same memory areas – unlike DB2 where some memory areas would be shared, but most are per-database (or per-connection). This is a distinct disadvantage in my opinion, but then DB2’s bufferpools have always been more configurable than Oracle’s buffer cache, in my opinion.
Hi,
Thanks for all the work that you do in blogging about DB2.
How it is different of having multiple databases under one instance from having the one database under one instance ? assuming the each database size as 10 GB and total number of databases as 8 in db2 v10.1
Which is recommendable as per IBM.
Generally for production, we prefer to see one database per instance. There are a number of parameters that can only be set at the instance level. The thing to keep in mind for non-production is that you want to ensure that the databases on the same instance would be upgraded together and would not have incompatible instance level settings. For small databases, grouping into one instance makes more sense – particularly if the databases are related in some way, such as configuration databases for the same tool or set of tools. If one database crashes, it can crash others or require an instance-level restart, so for keeping that separate, separate instances are also good. There are a lot of factors to consider, so no one rule of thumb.
Hello Guys,
Newbie to database environment ,just need to ask instance account and the database account what is different ?
Usually there is an instance owner. I have not heard of a “database account” so I cannot say how it is used in your enviornment.