Example of configuring connection management for a grid or replicate set

You can use a Connection Manager to route client connections for the replication servers of a grid or replicate set.

About this task

For this example, you have a replicate set that consists of four replication servers:

server_1
server_2
server_3
server_4

The replication set supports reporting services, which can run on any of the replication servers.

Your system has the following needs

Client requests are directed to the replication server with the fewest apply failures.
The system can withstand the failure of a Connection Manager.
The system can withstand a network-interface card (NIC) failure on each host.

Procedure

To configure connection management:

Install at least two network interface cards on each host.
This prevents the failure of a network interface card from causing Connection Manager or database server failure.
Install two Connection Managers. Install each Connection Manager onto a different host, and do not install the Connection Managers onto the hosts that database servers are installed on.
This installation strategy prevents a Connection Manager from becoming a single point of failure, and prevents the simultaneous failure of database servers and Connection Managers if a host fails.
On each Connection Manager host, set the ONEDB_HOME environment to the directory the Connection Manager was installed into.
Run the following command:
```
setenv ONEDB_HOME path
```
Create a configuration file in each Connection Manager installation's $ONEDB_HOME/etc directory.
The first Connection Manager's configuration file is named cm_1.cfg and has the following entries:
```
NAME connection_manger_1
LOG 1
LOGFILE $ONEDB_HOME/tmp/my_cm1_log.log

REPLSET replicate_set_1
{
	ONEDB_SERVER	g_server_1,g_server_2,g_server_3,g_server_4
	SLA report_1	DBSERVERS=ANY \
             POLICY=FAILURE
}
```
The second Connection Manager's configuration file is named cm_2.cfg and has the following entries:
```
NAME connection_manger_2
LOG 1
LOGFILE $ONEDB_HOME/tmp/my_cm2_log_.log

REPLSET replicate_set_1
{
	ONEDB_SERVER	g_server_1,g_server_2,g_server_3,g_server_4
	SLA report_2	DBSERVERS=ANY \
             POLICY=FAILURE
}
```
The configuration file specifies the following information and behavior:
- Logging is enabled, and the log files are $ONEDB_HOME/tmp/my_cm1_log.log and $ONEDB_HOME/tmp/my_cm2_log.log.
- When the Connection Managers start, they each search their sqlhosts files for g_server_1, g_server_2, g_server_3, and g_server_4 entries, and then connect to the servers server_1, server_2, server_3, and server_4 that are in those groups.
- CONNECT TO @report_1 and CONNECT TO @report_2 connection requests are directed to the replication server that has the fewest apply failures.
Certain parameters and attributes are not included in this configuration file, so the Connection Manager has the following default behavior:
- The MODE attributes of the SLA parameters are not set, so the Connection Managers return connection information for server_1, server_2, server_3, and server_4 to client applications, rather than acting as proxy servers.
- The HOST, NETTYPE, SERVICE, and SQLHOSTSOPT attributes of the SLA parameters are not set, so each Connection Manager uses connection information in local and remote sqlhosts files.
- The SQLHOSTS parameter is not set, so each Connection Manager first searches its local sqlhosts file, and then remote database server sqlhosts files for connectivity information related to server_1, server_2, server_3, and server_4.
- The WORKERS attributes of the SLA parameters are not set, so four worker threads are allocated to each of the SLAs.

Add entries to thesqlhosts files on the hosts of each database server, connection_manger_1, and connection_manger_2.

#dbservername  nettype   hostname   servicename  options
 g_server_1    group     -          -            i=1,e=server_1
 server_1      onsoctcp  host_1     port_1       g=g_server_1

 g_server_2    group     -          -            i=2,e=server_2
 server_2      onsoctcp  host_2     port_2       g=g_server_2

 g_server_3    group     -          -            i=3,e=server_3
 server_3      onsoctcp  host_3     port_3       g=g_server_3

 g_server_4    group     -          -            i=4,e=server_4
 server_4      onsoctcp  host_4     port_4       g=g_server_4

Create a sqlhosts file on each client host.
```
#dbservername  nettype    hostname   servicename   options
 report        group      -          -             c=1,e=report_2
 report_1      onsoctcp   cm_host_1  cm_port_3     g=report
 report_2      onsoctcp   cm_host_2  cm_port_4     g=report
```
If a Connection Manager fails, client applications can still connect to the other Connection Manager because the report group is defined.
CONNECT TO @report connection requests are directed through one of the Connection Managers to the replication server that has the fewest apply failures.
Set each ONEDB_ SQLHOSTS environment variable to the sqlhosts file location by running the setenv command on each Connection Manager and client host.
```
setenv ONEDB_ SQLHOSTS path_and_file_name
```
Turn on quality of data (QOD) monitoring by running the cdr define qod command.
```
cdr define qod -c server_1 --start
```
The command connects to server_1, defines server_1 as a master server for monitoring data, and then turns on quality of data monitoring.
server_1 maintains a failed-transaction count for the servers in the replicate set. The failed-transaction count determines which replication server the Connection Managers send a client connection requests to.
Run the oncmsm utility on each Connection Manager host, to start each Connection Manager.
On the host of connection_manager_1:
```
oncmsm -c cm_1.cfg
```
On the host of connection_manager_2:
```
oncmsm -c cm_2.cfg
```
Check each Connection Manager's log file to verify that the Connection Manager started correctly.