In this chapter, we will cover:
Creating and configuring instances for non-partitioned environments
Creating and configuring a client instance
Creating and configuring instances for multipartitioned environments
Starting and stopping instances
Configuring SSL for client-server instance communication
Attaching to instances
A DB2 instance can be defined as a logical container or as a logical context for databases. It can also be described as a layer between DB2 software binaries, a database, and its objects. Also it provides a level of isolation between databases; for example, it is possible to have two or more databases on the same environment, with the same name, but under different instances. It also provides and ensures the communication layer between clients and databases.
For this recipe (and almost all recipes in this book), we will use two servers running Red Hat Enterprise Linux Server x64 release 5.5 (Tikanga), named
nodedb22. The hostnames are optional, but our recommendation is to set up an identical environment to avoid confusion during reading and applying the recipes.
As install location for the IBM DB2 9.7 Enterprise Server Enterprise software product, we will use the directory
nodedb22, we will install DB2 Client software to location
/opt/ibm/db2/V9.7_clnt. The instance owner will be
db2clnt1 as client instance owner on
nodedb22. Also, on
nodedb21, we will create a second instance owner user named
db2inst2, to demonstrate how to create an instance manually.
The default method to create an instance is during the IBM DB2 9.7 Enterprise Server Edition software installation. The other possible option is to use the
In Linux and Unix, every instance is created under a dedicated user, called the instance owner. To create an instance in Linux and UNIX you have to be the
root user; on these platforms, we are limited to one instance per user. On Microsoft Windows platforms, you may have more than one instance created under the same user.
Usually, if you set up the software in graphical mode you do not have to create the users manually—you can do this using the wizard. In our recipes, we want to reuse the same groups (
db2fadm1) for the non-partitioned and the multipartitioned instance and database setup. For the multipartitioned setup we will have the same groups defined on both servers; because we have to deal with security regarding permissions, here, we should create the groups with the same group ID (GID):
Create primary groups with the same GID on both servers:
[root@nodedb21 ~]# groupadd -g 1103 db2iadm1 [root@nodedb21 ~]# groupadd -g 1102 db2fadm1 [root@nodedb21 ~]# [root@nodedb22 ~]# groupadd -g 1103 db2iadm1 [root@nodedb22 ~]# groupadd -g 1102 db2fadm1 [root@nodedb22 ~]#
db2setupfrom the IBM DB2 9.7 Enterprise Server Edition software installation kit.
To create a new instance during the installation with
db2setupin graphical mode, navigate through configuration steps 1 to 6 and, at step 7 you will find Create a DB2 instance option checked; this is the default option.and let as it is.Click Next.
At step 8—Partitioning options—you will find Single partition instance option checked ; this is the default option and let as it is. Click Next and finalize installation. If installation was successful, we have a new instance named
Another way to create an instance is to use the
db2icrtcommand. This method is suitable in the case that you install the DB2 software with
db2_install(manual installation), or that you do not check the Create a DB2 instance option during installation with
db2setup. Other scenarios would be if you drop an instance and want to create a new one, or if you want to create an additional instance.
As mentioned previously, in Linux and Unix, every instance has to be created under an instance owner user. As a
rootuser, we will create the user
db2inst2as instance owner and
db2fenc2as fenced user; set passwords identical to the individual usernames:
[root@nodedb21 ~]# useradd -g db2iadm1 db2inst2 [root@nodedb21 ~]# useradd -g db2fadm1 db2fenc2 [root@nodedb21 ~]# passwd db2inst2 Changing password for user db2inst2. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]# passwd db2fenc2 Changing password for user db2fenc2. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]#
At this step, set the communication protocol to TCP/IP. The instance communication protocol is set up using the
DB2COMMvariable. We can set this variable no protocol managers will be started and will lead to communication errors at the client side.
[db2inst2@nodedb21 ~]$ db2set DB2COMM=TCPIP [db2inst2@nodedb21 ~]$
Next, as user
db2c_db2inst2 50002/tcpentry (highlighted in bold in the listing bellow). Port
50002will be assigned to
50001corresponds to the
db2c_db2inst1service name and was added at
db2inst1instance creation. Port names prefixed with DB2 are reserved for inter-partition communication, a subject that we're going to discuss later on.
db2c_db2inst1 50001/tcp DB2_db2inst1 60000/tcp DB2_db2inst1_1 60001/tcp DB2_db2inst1_2 60002/tcp DB2_db2inst1_END 60003/tcp db2c_db2inst2 50002/tcp
rootuser, create instance
db2inst2, using the previously created users as instance owner and fenced user:
[root@nodedb21.~]# /opt/ibm/db2/V9.7/instance/db2icrt -a SERVER ENCRYPT -p db2c_db2inst2 -u db2fenc2 db2inst2 DBI1070I Program db2icrt completed successfully. [root@nodedb21 ~]#
We need to explain a little bit about the options used for creating instance
–aoption indicates the authentication type; the default is
SERVER. Using the
–aoption, the following authentication modes are available:
SERVER ENCRYPT. We may change it later by modifying the
AUTHENTICATIONor the SRVCONN_AUTH instance parameter.
–uswitch is used to set the fenced user.
–poption is used to specify the port or its corresponding service name used for client communication, as defined in
/etc/services. The port or service name may be changed later by modifying the
SVCENAMEdatabase manager parameter
For MS Windows platforms, we don't have the
–aoption to specify the authentication mode. The
–poption in Windows has a different meaning; it is used to specify the instance profile. The
–uoption is for specifying the account name and password used that will be included in the Windows service definition associated with the instance.
To use the Control Center for managing an instance locally or remotely, you need to have DB2 Administration Server (DAS) up and running, on the server.
To check the status of DAS, execute the following command, as DAS owner user, which is in our case
[dasusr1@nodedb21 ~]$ db2dascfg get dasstatus
Usually, it is installed and created during IBM DB2 software installation. If there is no DAS created, you should create it using the
dascrt command. The steps are similar to those for creating an instance—create a group and a user. It has to be created by specifying the owner.
/opt/ibm/db2/V9.7/instance/dascrt –u dasusr1.
In Linux or Unix, when an instance is created, the
db2icrt command builds up under the instance owner home directory, the
sqllib directory, as a collection of symbolic links pointing to the IBM DB2 software installation home directory. If you want to see what is executing
db2icrt in the background, you need to include the
–d option to enable debug mode. This explains what happens behind the scenes for the steps mentioned earlier. Usually, this switch is used for detailed diagnostics, and should be activated at the request of IBM support.
Almost all files and directories from sqllib directory are symbolic links to the corresponding installation path (DB2HOME). A short listing inside
sqllib directory looks like this:
[db2inst1@nodedb21]/home/db2inst1/sqllib>symlinks -v . other_fs: /home/db2inst1/sqllib/map -> /opt/ibm/db2/V9.7/map other_fs: /home/db2inst1/sqllib/bin -> /opt/ibm/db2/V9.7/bin other_fs: /home/db2inst1/sqllib/ruby64 -> /opt/ibm/db2/V9.7/dsdriver/ruby64
On MS Windows platforms, the
db2icrt command creates a service. The binaries are actually copied and a service associated with the instance is created.
On a generic Windows machine we'll create an instance named
db2win. Initially, the associated service has the status set to stopped and the startup type set to manually. If you want the service to start automatically at system boot, you have to change its startup type to automatic.
To create instance db2win, execute the following command under a privileged user:
C:\Windows\system32>db2icrt db2win DB20000I The DB2ICRT command completed successfully.
To find the associated Windows service with db2win instance, execute the following command:
C:\Windows\system32>sc query state= all | findstr "DB2WIN" SERVICE_NAME: DB2WIN DISPLAY_NAME: DB2 - DB2COPY1 - DB2WIN C:\Windows\system32>
db2isetup graphical tool might be used also for creating instances; this tool is available only on the Linux and Unix platforms.
On Linux and Unix you have the possibility to create a non-root type instance using the installer. You are limited to only one non-root instance per server.
Usually this command is used to update an instance after an upgrade to a higher version, or migrate an instance from a lower product level such as Workgroup Edition to Enterprise Edition. Also it might be used for instance debug using the
–d option. Like
db2icrt, this command has its own particularities on MS Windows operating systems. To find the available options and related descriptions of this command issue
db2iuptd –h. For non-root type instances exists a variant of this command named
Usually, this special type of instance is used for cataloging nodes and databases to which you want to connect using this client. Compared to server instances there are some limitations, as it cannot be started or stopped, and you cannot create databases under it. Mainly, it is used by the DB2 Client and DB2 Connect products.
nodedb22 we will create the instance owner
db2clnt1 and fenced user named
db2fenc1. For creating a client instance, we'll use the
–s option of the
Install DB2 Client in the
nodedb22, without creating an instance; to do this during installation, check at step 6—Instance setup—Defer this task until after installation is complete.
Next, create users on
db2clnt1as the client instance owner and
db2fenc1as fenced user—and set passwords identical to the usernames:
[root@nodedb22 ~]# useradd -g db2iadm1 db2clnt1 [root@nodedb22 ~]# useradd -g db2fadm1 db2fenc1 [root@nodedb22 ~]# passwd db2clnt1 Changing password for user db2clnt1. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb22 ~]# passwd db2fenc1 Changing password for user db2fenc1. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb22 ~]#
root, create the client instance
[root@nodedb22 ~]# /opt/ibm/db2/V9.7/instance/db2icrt -s client -u db2fenc1 db2iclnt1 DBI1070I Program db2icrt completed successfully. [root@nodedb22 ~]#
Mainly you need to setup a client instance when you have plans to administer DB2 servers remotely with tools that are using non-Java based connections such as Control Center or Toad for DB2. The same scenario is applicable when you are using CLI for remote administration or command execution and also in this category are non-java based application clients.
In the previous section we used the term non-java clients. However, this not totally exact for older type JDBC or JDBC-ODBC bridge connections using type 1 and 2 drivers. Type 3 and 4 JDBC drivers have implemented internally the entire network communication stack; this is the main reason for their independence from client instances and external network libraries. A good example for a tool that is relying only on JDBC type connections is the new Optim Database Administrator recommended by IBM to be used in future for database administration.
The Communication with DRDA servers (z/OS and i/OS) recipe in Chapter 11, Connectivity and Networking
The IBM DB2 database multipartitioned feature offers the ability to distribute a large database onto different physical servers or the same SMP server, balancing the workload onto multiple databases that are working as one, offering a very scalable way of data processing. We may have all the database partitions reside on the same server, this method of database partitioning is called logical partitioning. There is another scenario when the database partitions are spanned on different physical servers; this partitioning method is called physical partitioning.
An instance in a multipartitioned configuration is not very different by a non-partitioned instance, if it is running on a logical partitioning scheme. To use only physical partitioning, or physical partitioning combined with logical partitioning, an instance must be configured as shared across all the database partitions. In this recipe, we will use the last scenario.
The instance is created once on one node; on the other participant nodes, you have to create just the instance owner user with the same user ID (UID) and GIDs and the same home directory as on the instance owner node. In the following recipe, we will configure
servers for the purpose of multipartitioning and will create a new instance named
Notice that in this recipe we will use node and partition termsinterchangeably
To install a multipartitioned instance, we need to prepare a suitable environment. For this recipe, we will use the two Linux servers named
nodedb22, mentioned before.
nodedb21 will contain the instance home and will export it through NFS to the
nodedb22 system. We will also use a new disk partition, defined on
nodedb21, for instance home
/db2partinst, which, in our case, is a Linux LVM partition. We will create users on both servers with the same UID, and will install IBM DB2 ESE in a new location or DB2HOME—
nodedb21 with the create a response file option. On
nodedb22, we'll also install IBM DB2 ESE, in the location
/opt/ibm/db2/V9.7_part, using the response file created during installation on
Because this is not a Linux book, we do not cover how to install NFS or how to create a new Linux partition. As a preliminary task, you should check if you have NFS and portmap installed and running on both servers.
root, execute the following commands on both servers:
To check if we have NFS and portmap on
[root@nodedb21 ~]# rpm -qa | grep nfs nfs-utils-lib-1.0.8-7.6.el5 nfs-utils-1.0.9-44.el5 [root@nodedb21 ~]# rpm -qa | grep portmap portmap-4.0-220.127.116.11 [root@nodedb21 ~]#
To check their current status on
[root@nodedb21 ~]# service nfs status rpc.mountd (pid 3667) is running... nfsd (pid 3664 3663 3662 3661 3660 3659 3658 3657) is running... rpc.rquotad (pid 3635) is running... [root@nodedb21 ~]# [root@nodedb21 ~]# service portmap status portmap (pid 3428) is running... [root@nodedb21 ~]#
To automatically export
/db2partinston system boot, add your hostnames or the corresponding IP numbers to the
nodedb21, add the following line in
/db2partinst 10.231.56.117(rw,no_root_squash,sync) 10.231.56.118(rw,no_root_squash,sync)
To export the partition immediately, execute the following command:
[root@nodedb22 ~]# exportfs –ra [root@nodedb22 ~]#
nodedb22, as user
root, create a directory
/db2partinst, used as mount point for
/db2partinst, exported from
[root@nodedb22 ~]# mkdir /db2partinst [root@nodedb22 ~]#
nodedb22, to mount
/db2partinston system boot, add the following line:
nodedb21:/db2partinst /db2partinst nfs rw,timeo=300,retrans=5,hard,intr,bg,suid
To mount the partition immediately on
nodedb22, issue the following command:
[root@nodedb22 ~]# mount nodedb21:/db2partinst /db2partinst [root@nodedb22 ~]#
nodedb21, create the instance owner
db2instpand the fenced user
db2fencp. Instance home will be located in
[root@nodedb22 ~]# useradd -u 1316 -g db2iadm1 -m -d /db2partinst/db2instp db2instp [root@nodedb22 ~]# useradd -u 1315 -g db2fadm1 -m -d /db2partinst/db2fencp db2fencp [root@nodedb22 ~]# passwd db2instp Changing password for user db2instp. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]# passwd db2fencp Changing password for user db2fencp. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@nodedb21 ~]#
Repeat step 1 on
nodedb22and ignore any warnings.
In a physical multipartitioned environment, any instance owner user has to be able to execute commands on any participant node. To ensure this, we need to establish user equivalence or host equivalence between nodes. Actually, we have two methods: one is with RSH, which is less secure and the other is using SSH, which is secure. With SSH, there are two methods: one is host-based authentication and the other is client-based authentication. Next, we will implement client-based authentication; this method fits better with a small number of partitions, as in our example.
nodedb21, execute the following commands:
[db2instp@nodedb21 ~]$ cd ~ [db2instp@nodedb21 ~]$ mkdir .ssh [db2instp@nodedb21 ~]$ chmod 700 .ssh [db2instp@nodedb21 ~]$ cd .ssh [db2instp@nodedb21 .ssh]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/db2partinst/db2instp/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /db2partinst/db2instp/.ssh/id_rsa. Your public key has been saved in /db2partinst/db2instp/.ssh/id_rsa.pub. The key fingerprint is: 2b:90:ee:3b:e6:28:11:b1:63:93:ba:88:d7:d5:b1:14 db2instp@nodedb21 [db2instp@nodedb21 .ssh]$ cat id_rsa.pub >> authorized_keys [db2instp@nodedb21 .ssh]$ chmod 640 authorized_keys
nodedb22, execute the following commands:
[db2instp@nodedb22 .ssh]$ cd ~/.ssh [db2instp@nodedb22 .ssh]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/db2partinst/db2instp/.ssh/id_rsa): /db2partinst/db2instp/.ssh/id_rsa already exists. Overwrite (y/n)? y Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /db2partinst/db2instp/.ssh/id_rsa. Your public key has been saved in /db2partinst/db2instp/.ssh/id_rsa.pub. The key fingerprint is: 87:36:b4:47:5a:5c:e5:3e:4e:e9:ce:5b:47:2c:ce:6b db2instp@nodedb22 [db2instp@nodedb22 .ssh]$ cat id_rsa.pub >> authorized_keys [db2instp@nodedb22 .ssh]$
Go back on
nodedb21and issue the following commands to set up a host trust relationship:
[db2instp@nodedb21 ~]$ cd ~/.ssh [db2instp@nodedb21 .ssh]$ ssh-keyscan -t rsa nodedb21,10.231.56.117 >> known_hosts # nodedb21 SSH-2.0-OpenSSH_4.3 [db2instp@nodedb21 .ssh]$ ssh-keyscan -t rsa nodedb22,10.231.56.118 >> known_hosts # nodedb22 SSH-2.0-OpenSSH_4.3 [db2instp@nodedb21 .ssh]$
Verify that the client authentication is working; on
ssh nodedb22 date(do it the other way around—now it should work without asking for a password):
[db2instp@nodedb21 .ssh]$ ssh nodedb22 date Thu Jun 9 16:42:33 EEST 2011 [db2instp@nodedb21 .ssh]$ ssh nodedb22 [db2instp@nodedb22 ~]$ ssh nodedb21 date Thu Jun 9 16:42:48 EEST 2011 [db2instp@nodedb22 ~]$ ssh nodedb22 date Thu Jun 9 16:42:55 EEST 2011 [db2instp@nodedb22 ~]$ ssh nodedb21 [db2instp@nodedb21 ~]$ ssh nodedb21 date Thu Jun 9 16:43:07 EEST 2011 [db2instp@nodedb21 ~]$
A response file is a text file containing installation and configuration information such as paths, installation options etc. It can be created and recorded using interactive installation and replayed by other installations to perform the same steps.
db2setup, and, at step 4 of the installation wizard (Install action), check the Install DB2 Enterprise Server Edition on this computer and save my setting in a response file option. Provide the complete path to the response file.
At step 5, specify
/opt/ibm/db2/V9.7_partfor Installation directory.
At step 7 (Partitioning option), check Multiple partition instance.
Next, for DB2 instance owner, choose
db2instpand, for fenced user, choose
db2fencp. On the next screen, choose Do not create tools catalog. At the end of installation, we will find (in the directory chosen at step 4 of installation wizard) two files with
.rspextension; you need to copy just
nodedb22and issue on
nodedb22, from the installation directory:
./db2setup -r <your path>db2ese_addpart.rsp DBI1191I db2setup is installing and configuring DB2 according to the response file provided. Please wait.
The communication method of inter-partition command execution is controlled by DB2RSCHCM registry variable. Because our choice is SSH for inter-partition command execution, you must next set the
DB2RSHCMDvariable to point to SSH executable
DB2RSHCMD=/usr/bin/ssh. If this variable is not set, the
rshmethod is used by default:
[db2instp@nodedb21 ~]$ db2set DB2RSHCMD=/usr/bin/ssh -i
To verify the current DB2 registry variables, issue the following command:
[db2instp@nodedb21 ~]$ db2set -all [i] DB2RSHCMD=/usr/bin/ssh [i] DB2COMM=tcpip [i] DB2AUTOSTART=YES [g] DB2FCMCOMM=TCPIP4 [g] DB2SYSTEM=nodedb21 [g] DB2INSTDEF=db2instp
db2nodes.cfg file, database partition configuration file, located in
$INSTANCEHOME/sqllib, set the participant nodes. Define three nodes—two on
nodedb21, partion number
0 with logical port
0 and partition number
2 with logical port
1 and one on
1 with logical port
0. After adding the nodes we should have the following structure:
0 nodedb21 0 1 nodedb22 0 2 nodedb21 1
db2instp knows about the current nodes by reading their definition from
db2nodes.cfg database partition configuration file. The logical ports and number of maximum partitions per server are limited by the range defined within
/etc/services file as follows:
DB2_db2inst1 60000/tcp DB2_db2inst1_1 60001/tcp DB2_db2inst1_2 60002/tcp DB2_db2inst1_END 60003/tcp
The structure of
db2nodes.cfg, in some cases, can be further elaborated with optional information such as
netnames; in our case being a simple setup used for demonstration purpose we have defined only the nodes, hostnames, and the logical ports.
Under Unix and Linux,
db2nodes has the following complete format:
dbpartitionnum hostname logicalport netname resourcesetname
Under MS Windows,
db2nodes has the following complete format:
dbpartitionnum hostname computername logicalport netname resourcesetname
DB2 has two utilities to verify that communication between nodes is working:
db2_all and rah. You can also issue practically any administrative command (backup, restore, setting parameters, and so on) across the database partitions with these utilities.
An example of using
db2_all for verification:
[db2instp@nodedb21 ~]$ db2_all uptime 11:54:02 up 17:11, 1 user, load average: 0.07, 0.03, 0.00 nodedb21: uptime completed ok 11:54:03 up 17:11, 0 users, load average: 0.10, 0.03, 0.01 nodedb22: uptime completed ok 11:54:03 up 17:11, 1 user, load average: 0.07, 0.03, 0.00 nodedb21: uptime completed ok
The same using
[db2instp@nodedb21 ~]$ rah uptime 14:56:19 up 35 days, 18:09, 1 user, load average: 0.08, 0.02, 0.01 nodedb21: uptime completed ok 14:56:20 up 35 days, 18:09, 0 users, load average: 0.00, 0.00, 0.00 nodedb22: uptime completed ok 14:56:20 up 35 days, 18:09, 1 user, load average: 0.08, 0.02, 0.01 nodedb21: uptime completed ok
Obviously, there is also a possibility of using a shared disk, formatted with a concurrent file system, such as, IBM's GPFS or Red Hat GFS, for instance home, and used for sharing across the nodes instead of using NFS exports.
On Windows, it is not recommended to edit the db2nodes.cfg file manually; use the
The following commands instead:
db2nlist—to list database partitions
db2ncrt—to add a database partition server to an instance
db2ndrop—to drop a database partition server to an instance
db2nchg—to modify a database partition server configuration
The Converting a non-partitioned database to a multipartitioned database on MS Windows recipe in Chapter 3, DB2 Multipartitioned Databases—Administration and Configuration
There are several situations in which an instance must be stopped and started, for example, after you change some parameters that are not dynamic, or after applying a fixpack.
We have, at disposal, a couple of different ways to start or stop an instance. We can use, say,
db2start for starting and
db2stop for stopping; these commands are available for execution in the command line or from DB2 CLI. We can also start or stop an instance from the Control Center. In Windows, you can also start and stop an instance by starting and stopping the service associated with it.
The current instance is set by the environment variable
DB2INSTANCEor the global registry variable
DB2INSTDEF, in case
DB2INSTANCEis not set. This is applicable mostly for Microsoft Windows platforms where there could be more than one instance per user.
On Microsoft Windows:
C:\Documents and Settings>db2ilist DB2_02 DB2WIN C:\Documents and Settings>set DB2INSTANCE DB2INSTANCE=DB2_02
Now, if we issue
db2start, only instance
DB2_02will be affected.
On our Linux server
[db2inst1@nodedb21 ~]$ echo $DB2INSTANCE db2inst1
db2inst1instance owner, stop instance
db2stopcommand, and start it with
[db2inst1@nodedb21 ~]$ db2stop 06/09/2011 17:55:21 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@nodedb21 ~]$ db2start 06/09/2011 17:55:29 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
As the multipartitioned instance owner
db2instp, stop instance
db2stopcommand, and start it with
[db2instp@nodedb21 sqllib]$ db2stop 06/09/2011 19:03:47 1 0 SQL1064N DB2STOP processing was successful. 06/09/2011 19:03:48 0 0 SQL1064N DB2STOP processing was successful. 06/09/2011 19:03:49 2 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2instp@nodedb21 sqllib]$ db2start 06/09/2011 19:04:02 1 0 SQL1063N DB2START processing was successful. 06/09/2011 19:04:06 2 0 SQL1063N DB2START processing was successful. 06/09/2011 19:04:06 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
Using the Control Center, right-click on
db2inst1and issue stop and start.
In the process of starting an instance, memory structures are allocated and the instance starts listening for connections on the ports assigned by the
SVCENAME database manager configuration parameter. At stop, existing connections are disconnected and memory is deallocated.
Other options that can be used to start and stop an instance are the DB2 CLI commands,
START DATABASE MANAGER and
STOP DATABASE MANAGER. For Windows, we have as alternate option to start or stop the service associated with the instance. To set the instance for automatic start on Linux or Unix, at system boot, you can use the instance-level registry variable
DB2AUTOSTART=YES or the
db2iauto –on <instance name> command.
Databases can contain sensitive information; these days, the main concern is related to the security of data stored in tables as well as those sent over the network. One method of securing network communication between server and client is SSL, which is actually an abbreviation for Secure Socket Layer. We do not delve further into too much theory. Mainly, SSL addresses the following important security considerations: authentication, confidentiality, and integrity. Mainly SSL encryption and other network communication or also named data in transit encryption methods protects against unauthorized packet interception and analysis performed by an interposed person between a client and a server, also known as eavesdropping.
The DB2 instance has built-in support for SSL. DB2 relies on Global Security Kit for implementing SSL. GSKit is included in the IBM DB2 ESE software installation kit or is downloadable for free from IBM's website. Next, we'll show how to implement a secure connection between a DB2 server and a DB2 client.
For the next recipe, we will use
db2inst1 instance) as server and
db2clnt1 instance) as client, where we have installed DB2 Client in previous recipes. You need to ensure that you have GSKit libraries in
LD_LIBRARY_PATH. In our case, the libraries that are located in
/home/db2inst1/sqllib/lib64 are pointing to the
The first step is to add the
gsk8capicmd_64executable in our
Include the following in
source .bash_profileto reinitialize the user environment.
To create a key database on the server, execute the following (for more information about
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -stash [db2inst1@nodedb21 ~]$
Create a self-signature and self-sign the key database on the server:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -label "db2cookbooksignature" -dn "CN=www.packtpub.com,O=Packt Publishing,OU=Packt Publishing" [db2inst1@nodedb21 ~]$
Extract the signature for signing the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -extract -db "/home/db2inst1/keystoredb2inst1.kdb" -label "db2cookbooksignature" -target "/home/db2inst1/db2cookbook.arm" -format ascii -fips -pw "db2cookbook" [db2inst1@nodedb21 ~]$
Next, create the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoreclientdb2inst1.kdb" -pw "db2ckbk" –stash [db2inst1@nodedb21 ~]$
Import the self-signed certificate into the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -add -db "/home/db2inst1/keystoreclientdb2inst.kdb" -pw "db2ckbk" -label "db2cookbooksignature" -file "/home/db2inst1/db2cookbook.arm" -format ascii –fips [db2inst1@nodedb21 ~]$
To enable SSL as communication protocol on
nodedb21, execute the following:
[db2inst1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i [db2inst1@nodedb21 ~]$
Enable SSL as communication protocol also on the client side:
[db2clnt1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i [db2clnt1@nodedb21 ~]$
nodedb21, set SSL-related parameters on the server instance; then, stop and start the instance:
[db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_keydb /home/db2inst/keystoredb2inst1.kdb" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_stash /home/db2inst/keystoredb2inst1.sth" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_label db2cookbooksignature" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svcename 50004" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2stop 06/09/2011 19:08:39 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@nodedb21 ~]$ db2start 06/09/2011 19:08:45 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
Description of SSL-related parameters used on the server side:
SSL_SVR_KEYDBspecifies a fully qualified filepath of the key file to be used for SSL setup at server side
SSL_SVR_STASH—specifies a fully qualified filepath of the stash file to be used for SSL setup at server side
SSL_SVR_LABEL—specifies a label of the personal certificate of the server in the key database
SSL_SVCENAME—specifies the name of the port that a database server uses to await communications from remote client nodes using SSL protocol
Be careful to set the correct paths, otherwise SSL won't work.
nodedb22, set SSL DB2 client instance-related parameters:
[db2clnt1@nodedb22 ~]$ db2 "update dbm cfg using SSL_CLNT_KEYDB /home/db2clnt1/keystoreclientdb2inst.kdb" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2clnt1@nodedb22 ~]$ db2 "update dbm cfg using SSL_CLNT_STASH /home/db2clnt1/keystoreclientdb2inst.sth" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
Description of SSL-related parameters on the client side:
SSL_CLNT_KEYDBspecifies the fully qualified filepath of the key file to be used for SSL connection at the client side
SSL_CLNT_STASHspecifies the fully qualified filepath of the stash file to be used for SSL connections at the client side
Next, copy GSKit libraries to the client's
[root@nodedb22 ~]# cp /opt/ibm/db2/V9.7_part/lib64/libgsk8* /opt/ibm/db2/V9.7/lib64/ [root@nodedb22 ~]#
SSL establishes the connection between client and server using a mechanism called handshake. There is a lot of information on the Internet about SSL and its working. Briefly, these are the steps for SSL handshake:
The client requests an SSL connection, listing its SSL version and supported cipher suites.
The server responds with a selected cipher suite.
The server sends its digital certificate to the client.
The client verifies the validity of the server's certificate (server authentication).
Client and server securely negotiate a session key.
In this recipe, we used a self signed certificate, which is fine for testing or internal use. For production environments, you should use trusted certificates signed by a third-party certification authority.
Other methods for encrypting data in transit can be implemented by using
DATA_ENCRYPT_CMP as authentication methods. Also using port forwarding with SSH tunnels is a good option.
Chapter 10, DB2 Security
On a server environment, you may have many instances belonging to one DB2 installation or DB2HOME; obviously, you need to know about them and their name. For this purpose, you have the ability to use some specific commands to list them.
You also need to connect to these instances from remote locations to perform administration tasks; this, in the DB2 world, is called attaching.
In this recipe, we'll show how to list instances and attach to local and remote instances. Again, we'll use
nodedb21 as server and
nodedb22 as client.
Commands related to creating an instance are performed by the
root user; listing is no exception and must be performed as
The command to list current instances is
db2ilist. It lists the instances that belong to one DB2 copy. List instances created in DBCOPY1:
[root@nodedb21 ~]# /opt/ibm/db2/V9.7/instance/db2ilist db2inst1 db2inst2
The same command from multipartitioned DB2HOME or DBCOPY2:
[root@nodedb21 ~]# /opt/ibm//db2/V9.7_part/instance/db2ilist db2instp
db2inst1both as TCPIP and SSL, on our client instance
db2clnt1, created before. Because we set up SSL as a separate communication method for the
db2inst1instance, we have to specify it as the security method when cataloging the node (security SSL) with the SSL dedicated port. Catalog the nodes, as follows:
[db2clnt1@nodedb22 db2dump]$ db2 "CATALOG TCPIP NODE NODE21_S REMOTE nodedb21 SERVER 50004 SECURITY SSL REMOTE_INSTANCE db2inst1 SYSTEM nodedb21 OSTYPE LINUXX8664" DB20000I The CATALOG TCPIP NODE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [db2clnt1@nodedb22 db2dump]$ db2 "CATALOG TCPIP NODE NODE21_1 REMOTE nodedb21 SERVER 50001 REMOTE_INSTANCE db2inst1 SYSTEM nodedb21 OSTYPE LINUXX8664" DB20000I The CATALOG TCPIP NODE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed.
List the cataloged nodes:
[db2clnt1@nodedb22 ~]$ db2 "list node directory" Node Directory Number of entries in the directory = 2 Node 1 entry: Node name = NODE21_S Comment = Directory entry type = LOCAL Protocol = TCPIP Hostname = nodedb21 Service name = 50004 Security type = SSL Remote instance name = db2inst1 System = nodedb21 Operating system type = LINUXX8664 Node 2 entry: Node name = NODE21_1 Comment = Directory entry type = LOCAL Protocol = TCPIP Hostname = nodedb21 Service name = 50001 Remote instance name = db2inst1 System = nodedb21 Operating system type = LINUXX8664
Attach to instance
db2inst1, using first the SSL port, and next the TCP/IP port:
[db2clnt1@nodedb22 ~]$ db2 "attach to NODE21_S user db2inst1 using db2inst1" Instance Attachment Information Instance server = DB2/LINUXX8664 9.7.4 Authorization ID = DB2INST1 Local instance alias = NODE21_S [db2clnt1@nodedb22 ~]$ db2 " attach to node21_1 user db2inst1 using db2inst1" Instance Attachment Information Instance server = DB2/LINUXX8664 9.7.4 Authorization ID = DB2INST1 Local instance alias = NODE21_1
Attaching to an instance with the Control Center:
Instances are registered in a file named global register. This file is always updated when an instance is created or dropped.
When you attach to an instance from a client, you can see that the port on the server is changing its status from listening to established:
[root@nodedb21 ~]# netstat -nlpta | grep 5000* tcp 0 0 0.0.0.0:50001 0.0.0.0:* LISTEN 19974/db2sysc 0 tcp 0 0 0.0.0.0:50003 0.0.0.0:* LISTEN 26082/db2sysc 0 tcp 0 0 0.0.0.0:50004 0.0.0.0:* LISTEN 19974/db2sysc 0 tcp 0 0 10.231.56.117:50001 10.231.56.118:49321 TIME_WAIT - tcp 0 0 10.231.56.117:50004 10.231.56.118:48187 ESTABLISHED 19974/db2sysc 0
This appears on
nodedb21, after attaching to instance
db2inst1, using the SSL port 50004.
There is a straightforward method to verify that one instance is listening on its assigned port from a client. For this purpose, you can try to connect with telnet on that port:
[db2inst1@nodedb22 ~]$ telnet nodedb21 50004 Trying 10.231.56.117... Connected to nodedb21. Escape character is ‘^]'.
This means that our port assigned to SSL is listening. To detach from an instance, simply issue the
Another indirect method to list instances on a server is to use the discovery process provided by Configuration Assistant or Control Center locally or remotely.
Chapter 11, Using DB2 Discovery
There could be situations when it is necessary to drop an instance. An instance might be dropped by using the db2idrop command.
The command for dropping an instance is
db2idrop. You have to be user
rootto drop an instance. First, we need to ensure that the instance is not active. If the instance has active connections and it is active, the
Stop the instance by force:
[db2inst2@nodedb21 ~]$ db2stop force 07/12/2011 16:38:27 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst2@nodedb21 ~]$
As the user
root, issue the following command to drop
[root@nodedb21 ~]# /opt/ibm/db2/V9.7/instance/db2idrop db2inst2 DBI1070I Program db2idrop completed successfully.
On Linux and Unix,
db2idrop actually deletes the
sqllib directory from the instance owner home. Therefore, it is recommended to save anything you have placed in this directory such as UDFs or external programs.
db2idrop removes the service associated with the instance.
As a best practice, before the instance is dropped, it is recommended to save the information related to that instance in a server profile file. In case you plan to recreate the instance and configure it as before, you can simply import the server profile after the instance is created again.
To export the instance profile, use Control Center | Tools | Configuration assistant | Export profile | Customize.
In the Export tab, you have plenty of options to export; choose anything you consider worth being saved.