Section 6.3. Supported Connectivity Scenarios

6.3. Supported Connectivity Scenarios

In this section, we discuss the following four connectivity scenarios in detail.

• Scenario 1: Local connection from a DB2 client to a DB2 server

• Scenario 2: Remote connection from a DB2 client to a DB2 server

• Scenario 3: Remote connection from a DB2 client to a DB2 host server

• Scenario 4: Remote connection from a DB2 client to a DB2 host server through a DB2 Connect gateway

A DB2 host server can be DB2 for z/OS, OS/390, or iSeries.

You can configure a database connection by either:

• Cataloging the DB2 directories using DB2 commands manually

• Using the Configuration Assistant (CA) GUI tool

The Configuration Assistant is explained in section 6.4, Configuring Database Connections Using the Configuration Assistant.

It is useful to understand how to manually populate the DB2 directories using DB2 commands, so these scenarios focus on using the commands. Once you know how to do this, it will be a breeze to perform the configuration with the Configuration Assistant.

6.3.1. Scenario 1: Local Connection from a DB2 Client to a DB2 Server

Figure 6.10 illustrates a local connection.

Figure 6.10. The process of connecting locally from a DB2 client to a DB2 Server

Even though Figure 6.10 shows two machines, Machine 1 and Machine 2, Machine 1 is used to connect to Machine 2 using operating system commands or utilities like the Windows Terminal Service or Remote Desktop Connection (on Windows) or telnet (on Linux/UNIX). Once you establish this connection, any command you issue from the keyboard at Machine 1 is equivalent to issuing the command locally at Machine 2. Under this setup, when the connect to database statement is issued from the keyboard at either Machine 1 or Machine 2, the connection is considered local.

In this configuration, the server must have one of the following installed:

• DB2 Personal Edition

• DB2 Workgroup Edition

• DB2 Express Edition

• DB2 Enterprise Server Edition

The database must exist in the server's system database directory with an entry type of Indirect.

When you create a database with the create database command, an entry is automatically created in the system database directory and the local database directory. You normally do not need to issue catalog commands for a local database. However, it is possible for a local database to get lost in the system database directory. For example, this can happen if someone issues the uncatalog database command to remove the database from the system database directory, or when the system database directory is reset when reinstalling DB2. In all cases, as long as the database was not dropped (either by the drop database command or using the Control Center), the database still physically exists on the system, and the entry in the system database directory is simply missing. To get the database back into the system database directory, use this command:

 catalog db database_name [as database_alias] [on drive/path] 

where:

drive (Windows)/path (UNIX) is the location where the database files are physically stored.

Once the database is cataloged, you can use it just like before.

NOTE

If you drop an instance, the databases that belong to this instance are not dropped, because the databases reside on different directories from that of the instance. To recover these databases, all you need to do is to create a new instance with the same name as the one dropped and catalog the databases back using the catalog db command.

6.3.2. Scenario 2: Remote Connection from a DB2 Client to a DB2 Server

In most cases you do not have the authority to log on to the database server to perform a local database connection. Database servers are set up so that connections are performed through DB2 clients. In this scenario, DB2 client code is installed on a different machine from the database server machine. The connect statement is issued from the DB2 client machine. Figure 6.11 shows a connection from the machine Libra to a remote DB2 server that resides on Aries.

Figure 6.11. The process of connecting remotely from a DB2 client to a DB2 server

In this configuration, the machine Libra is considered a client to database server Aries. The client must have one of the following installed:

• DB2 Client (Runtime, Administration, or Application Development Client)

• DB2 Personal Edition

• DB2 Workgroup Edition

• DB2 Express Edition

• DB2 Enterprise Server Edition

The server must have one of the following installed:

• DB2 Workgroup Edition

• DB2 Express Edition

• DB2 Enterprise Server Edition

The supported communication protocols are

• TCP/IP
• NetBIOS	(only if both the client and server are Windows)
• Named Pipes	(only if both the client and server are Windows)

To configure the connection shown in Figure 6.11, you need to:

1.	Enable the database server to accept client connections.
2.	Catalog the node directory and the system database directory on the client.

The following sections describe these steps.

6.3.2.1 Enabling the Database Server to Accept Client Connections

Clients connect to the database server across the network using TCP/IP, NetBIOS (Windows only), or Named Pipes (Windows only). The server must have a process that is constantly up and running to receive these connect requests. We call this process a listener because it "listens" to any request that comes in from the network and tells the database manager to serve it.

6.3.2.1.1 TCP/IP connection

You need to perform the following steps on the database server to set up the listener if you are using the TCP/IP communication protocol.

1.	Update the services file to reserve a TCP/IP port for the DB2 instance. On Linux/UNIX, the services file is located in /etc/services. On Windows, the services file is located in C:\Windows\System32\drivers\etc\services. The entry in the services file must look like this: service_name port_number/tcp where: service_name is an arbitrary name to associate with the port number. port_number is the TCP/IP port number you are going to reserve for this DB2 instance. The port number must not already exist in the services file, and it must have a value of 1024 or higher.
2.	Update the SVCENAME parameter in the Database Manager Configuration file. Log on as the local administrator (Windows) or the instance owner (Linux/UNIX) and issue the following command from the Command Line Processor: update dbm cfg using svcename port_number/service_name You need to specify either the port number or the service name you defined in step 1.
3.	Enable TCP/IP support for the instance. Issue the following command: db2set DB2COMM=TCPIP
4.	Restart the instance to make the changes you made in the previous steps effective. Issue db2stop and db2start.

NOTE

If you are working with the default instance created and configured by the DB2 Setup Wizard, the services file, SVCENAME, and the DB2COMM parameters are already correctly configured.

6.3.2.1.2 NetBIOS or Named Pipes Connection

NetBIOS and Named Pipes are supported when the client and the server are on Windows platforms only. When NetBIOS or Named Pipes are used, you need to execute the following steps to set up the server.

1.	Make sure the server is available on the network. For NetBIOS, set NNAME in both the client and server's Database Manager Configuration file. NNAME identifies a workstation in a NetBIOS network, so it must be unique within the network. You do this by issuing the following command on both the client and the server: update dbm cfg using nname value For example, if server1 is the NetBIOS name of the server, issue update dbm cfg using nname server1
2.	Enable the instance for NetBIOS or Named Pipes support. Issue db2set DB2COMM=NETBIOS or db2set DB2COMM=NPIPE
3.	Restart the instance to make the changes effective.

6.3.2.2 Cataloging the Node Directory and Database Directory on the Client

After enabling the server to accept client connections, you need to tell the client how to connect to the server. You do this by cataloging the node directory and the system database directory at the client.

6.3.2.2.1 TCP/IP Connection

Use the information in Table 6.2 for completing the procedure in this section.

1.	Catalog a TCP/IP node on the client: catalog tcpip node nodename remote hostname_or_IP_address_of_server server port_number_of_server
2.	Catalog a database directory on the client: catalog db database_name [as database_alias] at node nodename
3.	Issue a terminate command to refresh the cache: terminate

Table 6.2. TCP/IP Connectivity Worksheet

Parameter	Description	Sample Values
Host name or IP address	The host name or IP address of the remote server. If you are working on a DPF system, you can use any of the participating server's host names or IP addresses.	aries.myacme.com 9.82.24.88
Port number	The TCP/IP port number where the instance is listening for incoming connections on the server.	50000
Node name	An arbitrary name used to identify the remote server. It must be unique in the client's node directory.	mynode1
Database name	The database on the server. It is the database to which you want to connect.	RMTDB
Database alias (optional)	An alias for the database name. If specified, all connections must use this alias. If not specified, the database alias will be the same as the database name.	MYRMTDB

Table 6.3 demonstrates how to use these commands based on the example shown in Figure 6.11. The information in this table applies to Linux, UNIX, and Windows.

Table 6.3. Example of Configuring a Remote Connection to a DB2 Server Using TCP/IP

Information You Need to Obtain from Server Machine 2 (Aries) to Perform the Commands on Client Machine 1	Command to Run on Client Machine 1 (Libra)
Host name = aries.myacme.com TCP/IP port in services file = 50000	db2 catalog tcpip node mynode1 remote aries.myacme.com server 50000
Database alias on Machine 2 = RMTDB Note: The database must exist in the system database directory of Machine 2. If the database alias and the database name are different, then the database alias should be used.	db2 catalog db rmtdb as myrmtdb at node mynode1 Note: MYRMTDB is an alias to the database RMTDB. It is optional; if specified, the alias is what you should use in the connect command. Otherwise, use the database name.
No information needed.	db2 terminate Note: This command is needed to make the previous catalog commands effective.
A valid user ID and password that has CONNECT privileges to database RMTDB. This user ID will be used from Machine 1 to connect to RMTDB.	db2 CONNECT TO myrmtdb USER userid USING password

After completing the two catalog commands in Table 6.3, the client machine's database directory and node directory will look like Figure 6.12.

Figure 6.12. Sample client's system database directory and node directory for remote connection to a DB2 database

6.3.2.2.2 NetBIOS Connection

If you are using NetBIOS, use the information in Table 6.4 to catalog a NetBIOS node.

1.	Issue the command: catalog netbios node nodename remote nname_of_server adapter adapter_number where: nodename is an arbitrary name and it must be unique in the client's node directory.
2.	Continue with step 2 in section 6.3.2.2.1, TCP/IP Connection, to finish cataloging the database.

Table 6.4. NetBIOS Connectivity Worksheet

Parameter	Description	Sample Value
NNAME of server	The NetBIOS name of the server workstation. You can get this from the server's Database Manager Configuration file using the get dbm cfg command.	server1
NNAME of client	The NetBIOS name of the client workstation. NNAME must be unique among all NetBIOS nodes in the network. You need to choose a name and update the client's Database Manager Configuration file using the update dbm cfg command.	client1
Adapter number	The client's logical adapter number used to connect to the DB2 server. To determine the adapter number: From a command prompt, enter the regedit command to start the Registry Editor. Locate the NetBIOS adapter assignments by expanding the HKEY_LOCAL_MACHINE folder and locating the Software/Microsoft/Rpc/NetBIOS folder. Double-click on the entry that begins with ncacn_nb_nbx, where x can be 0, 1, 2... (usually you want to select the nb0 adapter), to see the adapter number that is associated with the NetBIOS connection. Record the setting from the Data value field in the Edit DWORD Value window. Note: Ensure that both ends of the connection are using the same emulation.	2

6.3.2.2.3 Named Pipes Connection

If you are using Named Pipes as the communication protocol, use the worksheet in Table 6.5 to catalog an NPIPE node.

1.	Issue the command: catalog npipe node nodename remote computer_name_of_server instance instance_name where: nodename is an arbitrary name. It must be unique in the client's node directory.
2.	Continue with step 2 in section 6.3.2.2.1, TCP/IP Connection, to finish cataloging the database.

Table 6.5. Named Pipes Connectivity Worksheet

Parameter	Description	Sample Value
Computer name	The computer name of the server. On the server machine, click on Start > Settings > Control Panel. Double-click on the Network folder and select the Identification tab. Record the computer name.	db2server1
Instance name	The name of the DB2 instance on the server.	DB2

6.3.3. Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server

Figure 6.13 illustrates the configuration used for this scenario. The machine aries is considered a client to the database server mpower.

Figure 6.13. The process of connecting remotely from a DB2 client to a DB2 host server

The client must have one of the following installed:

• DB2 Connect Personal Edition

• DB2 Connect Enterprise Edition

• DB2 Enterprise Server Edition

The communication protocols supported are

• TCPIP

• APPC

Setting up a remote connection to a host DB2 database follows the same principle as setting up a connection to a DB2 for Linux, UNIX, and Windows database. You need to configure both the client and the server.

3.	Enable the database server to accept client connections.
4.	Catalog the node directory, system database directory, and DCS directory on the client.

6.3.3.1 Enabling the Database Server to Accept Client Connections

For DB2 for z/OS and OS/390, make sure that the distributed data facility (DDF) is running on the mainframe. DDF is the facility in DB2 for z/OS and OS/390 that allows for remote communication support. You can verify this by issuing the -display ddf command from the mainframe. To start DDF, issue the -start ddf command.

For DB2 for iSeries, make sure the distributed data management (DDM) is started. DDM is the facility in DB2 for iSeries that allows for remote communication support. To start DDM from the iSeries server or to verify that DDM is already started issue

 STRTTCPSVR SERVER(*DDM) 

The TCPIP port 446 is usually the default value. APPC can also be used instead of TCP/IP. Contact your host database administrator for specific connection information.

6.3.3.2 Cataloging the Node Directory, Database Directory, and DCS Directory on the Client

After you have enabled the server to accept client connections, you need to tell the client how to connect to the server. You do this by cataloging the node directory, system database directory, and DCS directory on the client.

6.3.3.2.1 TCP/IP Connection

Use the information in Table 6.6 for completing the procedure in this section.

1.	Catalog a TCP/IP node on the client. catalog tcpip node nodename remote hostname_or_IP_address_of_server server port_number_of_server
2.	Catalog a database directory on the client. catalog db database_name [as database_alias] at node nodename
3.	Catalog a DCS database directory on the client by issuing the following command from the client's command window: catalog dcs db database_name as target_database_name The database_name field must match the database_name in the catalog db command in step 2.
4.	Issue the terminate command to refresh the cache. terminate

Table 6.6. TCP/IP Connectivity Worksheet for DB2 Client to DB2 Host Connection

Parameter	Description	Sample Values
Host name or IP address	The host name or IP address of the remote server.	mpower.youracme.com 158.228.10.3
Port number	The TCP/IP port number on which DB2 is listening for incoming connections on the server.	446
Node name	This is an arbitrary name and is used to identify the remote server. It must be unique in the client's node directory.	mynode2
Target database name	The database on the host server. For DB2 for z/OS and OS/390 servers, this is the Location name. For DB2 for iSeries servers, this is the RDB name.	hostprod
Database name	An arbitrary name you would like to associate with the target database name.	myhostdb
Database alias (optional)	You can optionally specify a database alias for the database name. If specified, all connections must use this alias name; if not specified, the database alias will be the same as the database name.	myhostdb

Table 6.7 demonstrates how to use these commands based on the example shown in Figure 6.15.

Table 6.7. Example of Configuring a Remote Connection to DB2 for z/OS and OS/390 or DB2 for iSeries Database

Information You Need to Obtain from Host Server Machine 2 (mpower) to Perform the Commands on Client Machine 1 (aries)	Command to Run on Client Machine 1 (aries)
Host name of Machine 2 = mpower.youracme.com The TCP/IP port DB2 uses = 446	db2 catalog tcpip node mynode2 remote mpower.youracme.com server 44
No information needed.	db2 catalog db myhostdb at node mynode2 Note: myhostdb is an arbitrary database name, but it must match the entry for the DCS directory below.
hostprod = The Location name if the server is DB2 for z/OS and OS/390 or RDB name if the server is DB2 for iSeries.	db2 catalog dcs db myhostdb as hostprod
No information needed.	db2 terminate Note: This command is needed to make the previous catalog commands effective.
A valid user ID and password that has connect privilege to the host database.	db2 connect to myhostdb user userid using password

Figure 6.15. The process of connecting from a DB2 client to a DB2 server via a DB2 Connect gateway

After completing the three catalog commands in Table 6.7, the client machine's system database directory and node directory will look as shown in Figure 6.14.

Figure 6.14. Sample client's system database directory, node directory, and DCS directory for remote connection to a DB2 host database

6.3.3.2.2 APPC Connection

If you use APPC instead of TCP/IP, you need to do more work on the client. In addition to cataloging the node, database, and DCS directories, you also need to configure the APPC software installed on the client. Table 6.8 lists the supported APPC software. Please refer to the APPC software manual for instructions on how to configure this software.

Table 6.8. Supported APPC Products

Platform	Software Needed to Support APPCConnection
Windows NT/2000	IBM Communications Server Version 6.1.1 or later IBM Personal Communications for Windows Version 6.0 with CSD 3 Microsoft SNA Server Version 3 with Service Pack 3 or later
Windows XP	IBM Personal Communications for Windows Version 6.5 with APAR IC23490
Windows Server 2003	APPC is not supported
AIX	IBM Communications Server for AIX Version 6.1 or later
HP-UX	SNAplus2 Link R6.11.00.00 SNAplus2 API R.6.11.00.00
Linux	APPC is not supported
Solaris	SNAP-IX for Solaris V7.02

After you have configured the APPC software, make sure the client can reach the host machine. This is similar to the ping test in TCP/IP. Then catalog the node, database, and DCS directories. You catalog the database and DCS directories the same way as TCP/IP (see section 6.3.3.2.1, TCP/IP Connection). However, the node directory is cataloged differently. Use the following command to catalog the node directory on the client:

 catalog appc node     nodename              remote   symbolic_destination_name                       security security_type 

where:

• nodename is an arbitrary name. It must be unique in the client's node directory.

• symbolic_destination_name can be found in the CPI-C Side Information profile. You should have created this during the APPC software configuration.

• security_type is SAME, SECURITY, or PROGRAM.

6.3.4. Scenario 4: Remote Connection from a DB2 Client to a DB2 Host Server via a DB2 Connect Gateway

Imagine you have 1,000 clients who need to connect to a host database. If you set up the connections using DB2 Connect Personal Edition, you will need to purchase and install DB2 Connect Personal Edition on each of the 1,000 clients. This would be very costly. Wouldn't it be nice if you could only install DB2 Connect once on one machine, and use it as a gateway to service all connections from clients to the host database? Of course! For that scenario you need to use the DB2 Connect Enterprise Edition. Figure 6.15 illustrates this scenario.

In this configuration, Machine 1 is the client, Machine 2 is referred to as the DB2 Connect gateway, and Machine 3 is the host database server.

The DB2 Connect gateway is the only machine that needs to have DB2 Connect Enterprise Edition installed. Its task is to serve as a middleman between the clients and the host database server, since the clients do not have the ability to connect directly to the server. The gateway machine can serve hundreds of clients.

When configuring this type of connection, you can break the three-tier connection into two parts.

• Part one is the gateway-to-host server connection. This is identical to what we discussed in Scenario 3. Follow the same steps as in section 6.3.3, Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server, to configure the gateway. Make sure you can connect from the gateway to the host database before proceeding to the next step.

• Part two is the client-to-gateway connection. From the client's perspective, the gateway machine is the database server. (The client does not know anything about the host server mpower.) Thus, when cofiguring this part of the connection, treat the gateway as the server, and follow the same steps described in section 6.3.2, Scenario 2: Remote Connection from a DB2 Client to a DB2 Server.

6.3.5. Binding Utilities

After a client establishes a connection to a database server, it should be able to access the data in the database. However, if you issue the import/export commands or try to run a CLI/ODBC application, you will get SQL0805N "Package not found" error. This is because the client has not bound these utilities to the database server.

Utilities are database programs with embedded SQL; their packages must reside on the database server. Packages are version and FixPak level specific; therefore, a package created at the Version 8, FixPak 1 level cannot be used by a client running at Version 8, FixPak 2. If this client needs to use these utilities, it must create packages at its own DB2 level. (Refer to Chapter 7, Working with Database Objects, for a more detailed explanation of packages.)

To create all of these packages at once, run the following commands from a DB2 Administration Client or the Application Development Client's CLP window:

 connect to database_alias user userid using password bind @db2ubind.lst blocking all grant public bind @db2cli.lst   blocking all grant public 

If the database server is a host database, you must run one of the following commands on the DB2 Connect machine.

• If the host is DB2 for z/OS or OS/390:

   bind @ddcsmvs.lst blocking all grant public 

  
  

• If the host is DB2 for iSeries:

 bind @ddcs400.lst blocking all grant public 

You need to use the symbol @ when you specify a file that contains a list of bind files (with the .lst file extension), rather than a bind file (with the .bnd file extension) itself. The .lst files are in the install_directory\bnd directory on Windows and in the instance_home/sqllib/bnd directory on Linux/UNIX. Both contain a list of bind files the bind command will run against. A package is created for each of these bind files.

NOTE

The DB2 Runtime Client does not include the required bind files, so you cannot run the bind command from a Runtime Client.

You must bind the utilities for each database you want to access. Binding only needs to be done once by a client. Once a package is successfully bound to the database, all DB2 clients of the same DB2 version and FixPak level can use it. If you have different versions and FixPaks of clients, you must bind the utilities for each client version and FixPak level.

NOTE

You must have BINDADD authority to create a new package in a database or BIND privilege if the package already exists.