From a user's perspective, a DB2 instance provides an independent environment where database objects can be created and applications can run. Several instances can be created on one server, and each instance can have a multiple number of databases, as illustrated in Figure 5.5.
Because of these independent environments, one instance cannot “see” the contents of another instance; therefore, objects of two or more different instances can have the same name. In Figure 5.5, the database called MYDB1 is associated with instance Development, and another database also called MYDB1 is associated with instance Test. Instances allow users to have different environments for production, test, and development purposes. In addition, independent environments let you perform instance and database operations without affecting other instances. For example, if you stop and start the instance Test, the other two instances are not affected.
From an architectural perspective, an instance serves as a layer between the DB2 binary code and the user database objects. It is important to understand that this is just an association of the DB2 code to the database objects. There is a common misconception among new DB2 users that dropping an instance also drops the databases associated to that instance; this is not necessarily true. When an instance is dropped, the association to the user databases is broken, but it can later be reestablished, as discussed in Chapter 2, DB2 at a Glance: The Big Picture.
Figure 5.6 shows an example of two instances in a Linux/UNIX environment. Databases MarketDB and SalesDB are associated to instance #1. Databases TestDB and ProdDB are associated to instance #2. Each instance has its own configuration files. In this example, both instances are pointing to the same DB2 binary code for Version 8.2 using soft links. On Linux and UNIX, a soft link behaves like an alias to another file. Soft links are also referred to as symbolic links or logical links.
When you install DB2 on the Windows platform, an instance called DB2 is created by default. On Linux and UNIX you can choose to create the default instance during the installation, change the instance owner's name, or not create an instance so that you can create one later. If you choose to create the default instance on these platforms, this instance is named db2inst1. DB2 will create an operating system user with the same name as the instance. This user is known as the instance owner.
You can also create new, additional instances on your server using the db2icrt command.
Figure 5.7 summarizes the db2icrt command and provides examples.
On Windows the db2icrt command can be run by a user with Local Administrator authority. The command creates a subdirectory under the SQLLIB directory with the name of the instance just created. In addition, a Windows service DB2 - instance_ name will be created.
On Linux and UNIX you must have root authority or else you need to have the system administrator run the db2icrt command for you. You can either use the fully qualified path name to the program or change into the directory to run this command as shown below:
Run the command /opt/IBM/db2/v8.1/instance/db2icrt (or /usr/opt/IBM/db2/v8.1/instance/db2icrt on AIX)
Change into the directory /opt/IBM/db2/v8.1/instance (or /usr/opt/IBM/db2/v8.1/instance on AIX) and then invoke the db2icrt command.
In addition, on Linux and UNIX, the instance name must match an existing operating system user ID, which becomes the instance owner. This operating system user must exist prior to executing the db2icrt command. The db2icrt command will create the subdirectory sqllib under the home directory of this user.
DB2 on Linux and UNIX also requires a fenced user to run stored procedures and user-defined functions (UDFs) as fenced resources, that is, in a separate address space other than the one used by the DB2 engine. This ensures that problems with these objects do not affect your database or instance. If you are not concerned about this type of problems, you can use the same ID for the fenced user and the instance owner.
NOTEThe terms instance and a DB2 instance are used interchangeably. On Windows, the default name of the DB2 instance is DB2. This sometimes confuses new DB2 users. |
You need to have the correct DB2 version and operating system to create 64-bit instances. At this time only AIX 5L, HP-UX, and the Solaris Operating Environment support this.
To create a 64-bit instance, include the -w option. For example:
db2icrt -w 64 -u db2fenc1 my64inst
creates a 64-bit instance called my64inst and uses a fenced id of db2fenc1.
In general, when we talk about instances in this book we are referring to server instances: fully functional instances created at the DB2 server where your database resides. There are other types of instances that can be created. One of them, the client instance, is a scaled down version of a server instance. A client instance cannot be started or stopped, and databases cannot be created in this type of instance.
You create a DB2 client instance using the -s option. For example:
db2icrt -s CLIENT myclinst
creates the client instance myclinst. On Linux/UNIX, the operating system user myclinst must exist before executing this command. On Windows, an instance does not map to a user ID, so this would not be a requirement.
On a Windows client, the entire machine is considered the DB2 client regardless of the user. On a Linux/UNIX client machine the DB2 client is associated to an operating system user.
You need to have a client instance if you have two physically separate Linux/UNIX machines, one containing the DB2 client code (assume it is an application server machine) and the other one containing the DB2 server code (the DB2 server machine). Although the client machine contains the DB2 client code, a client instance must be created that will associate it to a given operating system user. Logging on as this user lets you perform the commands required to set up connectivity to the DB2 server machine. In this example, if the client and server resided on the same machine, there would be no need to create a client instance, because the operating system user used as the client can “source” the instance owner profile in sqllib/db2profile as described in section 5.2.1, Environment Variables.
In a multi-partitioned environment, an instance is only created once: in the machine where the disks to be shared by the other partitions reside. The instance owner's home directory is then exported to all the servers participating in the multi-partitioned environment (see Chapter 2, DB2 at a Glance: The Big Picture).
NOTEYou can only create a multi-partitioned database if you have DB2 UDB Enterprise Server Edition (ESE) installed and you have purchased the database partitioning feature (DPF). The DPF is a paper-only license that you need to acquire; you do not need to install any additional products to use this feature. |
You can drop an instance if you no longer require it. Before you drop an instance, make sure that it is stopped, and that all memory and inter-process communications (IPCs) owned by the instance have been released. You can then run the db2idrop command to drop the DB2 instance. For example, to drop the instance myinst, use the command:
db2idrop myinst
NOTEOn Linux and UNIX, you can use the ipclean command to remove all IPCs associated with the ID that runs the command. |
You can list all instances on your server using the db2ilist command. On Windows you can run this command from any Command Window. On Linux and UNIX you need to change into the path where DB2 was installed to run this command.
Alternatively, you can list your instances using the DB2 Control Center. Figure 5.8 shows the steps that are needed.
1. | Right-click on the Instances folder. |
2. | Choose Add Instance. |
3. | Click on the Discover button. |
Clicking on Discover displays a list of all available instances. You can then select the desired instance(s) to add to the Control Center.
NOTEYou cannot create an instance from the Control Center. You can only add an existing instance to the Control Center so it can be displayed and managed more easily with this tool. |
The environment variable DB2INSTANCE determines the active instance. It is particularly important to have this variable set correctly when you have multiple instances in the same DB2 server. For example, if you have two instances, myinst1 and myinst2, and DB2INSTANCE is set to myinst2, any command you execute will be directed to the myinst2 instance.
Because DB2INSTANCE is an operating system environment variable, you set its value like any other environment variable for your operating system, as discussed in section 5.2.1, Environment Variables. Figure 5.9 illustrates setting the DB2INSTANCE environment variable temporarily in the Windows platform using the set operating system command. It also illustrates the methods used to determine its current value.
The get instance command works on any platform. The other methods were described in section 5.2.1, Environment Variables.
An instance must be started to work with it. You can choose to start the instance manually or automatically every time you reboot your machine. To start an instance manually, use the db2start command. On Windows, since DB2 instances are created as services, you can also start an instance manually using the NET START command. To start an instance automatically on Windows, look for the service corresponding to the DB2 instance by opening the Control Panel, choosing the Administration Tools folder, and then double-clicking on Services. A Services window similar to the one displayed in Figure 5.10 will appear.
Several DB2 services are listed in Figure 5.10. All of the DB2 services can be easily identified as they are prefixed with DB2. For example, the service DB2 - MYINST represents the instance MYINST. The service DB2 - DB2-0 represents the instance named DB2 (highlighted in the figure). The 0 in the service name represents the partition number. As you can see from the figure, this service is set up to be manually started, so you would need to execute a db2start command every time the system is restarted for the DB2 instance to be able to work with your databases.
You can set up the instance to be automatically started by right-clicking on the DB2 - DB2-0 service and choosing Properties from the drop-down menu. Once the Properties panel appears, you can change the Startup type from Manual to Automatic (see Figure 5.11).
On Linux and UNIX, to automatically start the DB2 instance every time the server is started, use the db2iauto command. To set up the db2inst1 instance to be started automatically, run the command:
db2iauto –on db2inst1
NOTEIf you are setting up your system for High Availability failover, you should not set up the instance to be started automatically. |
When you run the db2start command on your Linux or UNIX server, a number of processes start. If you run the ps –ef command you will notice a db2sysc process that is associated with the instance owner's ID. This is the DB2 main system controller for the instance. DB2 processes are discussed in more detail in Chapter 14, The DB2 Process Model.
In a multi-partitioned environment you only need to run the db2start command once, and it will start all of the partitions defined in the db2nodes.cfg file. Notice in the output of the db2start command below that there is one message returned for each partition, and each message has the partition number associated with it in the third column. Since the instances are started in parallel, they are not likely to complete in the order specified in the db2nodes.cfg file.
db2inst1@aries db2inst1]$ db2start
01-14-2005 14:42:26 1 0 SQL1063N DB2START processing was successful.
01-14-2005 14:42:26 0 0 SQL1063N DB2START processing was successful.
01-14-2005 14:42:26 2 0 SQL1063N DB2START processing was successful.
01-14-2005 14:42:26 3 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
There may be times when a database administrator needs to be the only user attached to an instance to perform maintenance tasks. In these situations, use the db2start option admin mode user userId so only one user has full control of the instance. You can also do this from the Control Center by right-clicking on the desired instance in the Object Tree and choosing Start Admin.
You can use the db2stop command to stop a DB2 instance that is currently running. Verify that the DB2INSTANCE environment variable is correctly set before issuing this command, as discussed in section 5.2.1, Environment Variables.
On Windows, since the DB2 instances are created as services, you can also stop the instances using the NET STOP command or stop the service from the Control Panel. To stop an instance from the Control Panel on Windows, right-click on the service and select Stop from the drop-down menu. Once the service is stopped the Status column will be blank, as the highlighted line shows in Figure 5.12.
You will not be able to stop the instance if there is a database that is active in the instance or if there are databases with one or more connections. You must first deactivate the database and/or reset the connections. In many cases you will have a large number of DB2 client machines running applications that connect to the database server, and you will not be able to go to each machine to close the application to terminate the connection. In this case you can use the force option with the db2stop command to force off all active connections and/or activations to stop the instance:
db2stop force
NOTEA db2stop force command has the same effect as issuing the force applications all command followed by the db2stop command. However, db2stop force prevents new connections from happening while the instance is being stopped. The force applications command is discussed in detail in Chapter 11, Understanding Concurrency and Locking. |
In a multi-partitioned environment you only need to run the db2stop command once, and it will stop all of the partitions defined in the db2nodes.cfg file.
NOTEIn many DB2 customer environments, the process of issuing a db2stop followed by a db2start command is called one or more of the following:
|
To perform instance-level maintenance tasks, you first need to attach to the instance with the attach command. Some instance-level operations are
Listing applications connected to your databases
Forcing off applications
Monitoring a database
Updating the Database Manager Configuration parameters
Users often confuse attaching to an instance and connecting to a database. When in doubt as to which one to use, determine if the operation is to affect the instance or a particular database. For example, the list applications command lists all the applications connected to all the databases in your active instance. This is not an operation that you would perform at the database level, since you want to list all connections to all databases, so an attachment is what is required in this case. (Chapter 6, Configuring Client and Server Connectivity, discusses setting up database connections in detail. In that chapter we describe the node directory, which is used to encapsulate connectivity information, such as the hostname of a remote DB2 database server and the port number of the instance.)
NOTEAttachments are only applicable at the instance level; connections are only applicable at the database level. |
When you attach to an instance, it can be a local instance or a remote one, and there will be corresponding entries for each in the node directory. A local instance resides on the same machine where you issue the attach command, while a remote instance resides on some other machine. Other than the active instance specified in the DB2INSTANCE variable, DB2 will look for connectivity information in the node directory for any other instance.
The syntax to attach to the active instance is:
attach to instance_name_as_indicated_in_DB2INSTANCE
For example:
attach to DB2
To attach to a local or remote instance that is not your active instance, use:
attach to node_name [user userId] [using password]
For example:
attach to mynode user peter using myudbpsw
where mynode is an entry in the node directory.
Attaching to the active instance (as specified in DB2INSTANCE) is normally done implicitly. However, there are special occasions where you do need to explicitly attach to the active instance, as you will see in following sections.
To detach from the current attached instance, issue the detach command:
attach to mynode detach
You can set DB2 configuration parameters at the instance level (also known as the database manager level) and at the database level. At the instance level, variables are stored in the Database Manager (DBM) Configuration file. Changes to these variables affect all databases associated to this instance. At the database level, variables are stored in the Database Configuration file. Changes to these variables only affect that specific database. In this section we discuss the DBM Configuration file in detail.
When you install DB2 and create an instance, the instance is assigned a default DBM configuration. You can view this configuration by running the get dbm cfg command. Figure 5.13 shows the output of this command on a Windows machine.
Note that the Node type entry field at the top of the output identifies the type of instance. For example, in Figure 5.13 this field has the value Database Server with local and remote clients. This means it is a server instance. For a client instance the value of this field would be Client.
In this book you will learn some of the more important parameters for the DBM Configuration file. For a full treatment of all DBM Configuration parameters, refer to the DB2 UDB Administration Guide: Performance.
To update one or more parameters in the DBM Configuration file, issue the command:
update dbm cfg using parameter_name value parameter_name value ...
For example, to update the INTRA_PARALLEL DBM Configuration parameter, issue the command:
update dbm cfg using INTRA_PARALLEL YES
Issuing the get dbm cfg command after the update dbm cfg command shows the newly updated values. However, this does not mean that the change will take effect right away. Several parameters in the DBM Configuration file require a db2stop followed by a db2start for the new values to be used. For other parameters, the update is dynamic, so a db2stop/db2start is not required as the new value takes effect immediately. These parameters are called configurable online parameters. If you are updating a configuration parameter of a DB2 client instance, the new value takes effect the next time you restart the client application or if the client application is the CLP, after you issue the terminate command.
NOTEConfigurable online parameters of the DBM Configuration file can be updated dynamically only if you first explicitly attach to the instance. This also applies to local instances. If you have not performed an attach, the parameter won't be changed until you perform a db2stop/db2start. |
Refer to the file ConfigurationParameters.pdf included on the CD-ROM accompanying this book for a list of DBM Configuration parameters that are configurable online. The Control Center provides this information as well. Refer to section 5.3.12, Working with an Instance from the Control Center, for details.
To get the current, effective setting for each configuration parameter and the value of the parameter the next time the instance is stopped and restarted, use the show detail option of the get dbm cfg command. This option requires an instance attachment. If you run this command after changing the INTRA_PARALLEL configuration parameter as above, you will see that the current value is NO, but the next effective or delayed value is YES. The related output from the get dbm cfg show detail command would look like the following:
C:Program FilesSQLLIBBIN>db2 get dbm cfg show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Enable intra-partition parallelism (INTRA_PARALLEL) = NO YES
The show detail option is also helpful for determining the actual value of parameters listed as AUTOMATIC. For example, when you issue the get dbm cfg command while attached to an instance, you may see output as follows for the INSTANCE_MEMORY parameter:
C:Program FilesSQLLIBBIN>db2 get dbm cfg ... Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
If you use the show detail option, the actual value is displayed:
C:Program FilesSQLLIBBIN>db2 get dbm cfg show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(8405) AUTOMATIC(8405)
To reset all the DBM Configuration parameters to their default value, use the command reset dbm cfg.
The instance operations described in the previous sections can also be performed from the Control Center. Figure 5.14 shows the Control Center with the instance MYINST selected. When you right-click on the instance, a menu with several options displays. Figure 5.14 highlights some of the menu items that map to the instance operations we have already described.
Figure 5.15 shows the DBM Configuration window that appears after selecting Configure Parameters from the menu shown in Figure 5.14. In Figure 5.15, the column Pending Value Effective indicates when the pending value for the parameter will take effect; for example, immediately or after the instance is restarted. The column Dynamic indicates whether the parameter is configurable online or not. The rest of the columns are self-explanatory.
Figure 5.15 also illustrates how you can update a Database Manager Configuration parameter from the Control Center. For example, after selecting the parameter FEDERATED and clicking on the three dots button (...), a pop-up window displays that lists the possible values this parameter can accept. Choose the desired option and click OK.
Table 5.2 summarizes the most common DB2 commands used at the instance level.