SLA Connection Manager configuration parameter

The SLA parameter defines service-level agreements that direct client requests to database servers.

Figure 1: Syntax

CLUSTER SLA syntax fragment

GRID or REPLSET SLA syntax fragment

SERVERSET SLA syntax fragment

Notes:

¹ You must use at least one cluster keyword or a group of values that are enclosed by parentheses if you specify a redirection policy.

SLA parameter attributes

Table 1. The attributes of the SLA Connection Manager configuration parameter
Attribute name	Description
`DBSERVERS`	Specifies servers, server aliases, server groups, or server types for directing connection requests. Use the ANY keyword, the SDS or RSS cluster keywords, or enclose a group of values in parentheses to enable a redirection policy for that group.
`HOST`	Specifies a database server's host. The value in the SLA is used, rather than the value in the Connection Manager's host sqlhosts file.
`MODE`	Specifies whether connection requests go through the Connection Manager or if the Connection Manager provides connection information to the source of a connection request. The default value is REDIRECT.
`NETTYPE`	Specifies the network protocol of a database server. The value in the SLA is used, rather than the value in the Connection Manager's host sqlhosts file.
`POLICY`	Specifies how the Connection Manager redirects client connection requests to the servers specified in the DBSERVER attribute. Redirection policy applies to the ANY keyword, the SDS and RSS cluster keywords, and to a group of values that are enclosed in parentheses. Parentheses within parentheses are ignored. The default value is WORKLOAD. Workload, apply-failure, and transaction-latency policies can be given relative weights.
`SERVICE`	Specifies a database server's port number or service name. The value in the SLA is used, rather than the value in the Connection Manager's host sqlhosts file.
`SQLHOSTSOPT`	Specifies connectivity options for a database server that is specified in a SLA. Enclose all connectivity options in a single pair of quotation marks. The value in the SLA is used, rather than the value in the Connection Manager's host sqlhosts file.
`USEALIASES`	Specifies whether the Connection Manager can redirect client connection requests to database server aliases specified by the DBSERVERALIASES configuration parameter. The default value is ON.
`WORKERS`	Specifies the number of worker threads that are allocated to the SLA. When a service-level agreement is specified, the Connection Manager creates an SLA listener process to intercept client connection requests. The SLA listener process can have one or more worker threads. The default value is 4.

DBSERVERS attribute values

Table 2. Values of the DBSERVERS attribute.
Attribute value	Value
`ANY`	Specifies that connection requests can be sent to any available database server in the specified cluster, grid, or replicate set. For a SERVERSET connection unit, `ANY` specifies that connection requests can be sent to any available database server specified by the SERVERSET connection-unit's ONEDB_SERVER parameter. You do not need to enclose ANY in parentheses to apply a redirection policy to it. If no redirection policy is specified, ANY uses the workload redirection policy.
`group`	Specifies a group entry in the Connection Manager's host sqlhosts file. Connection requests can be sent to the members of the group.
`HDR`	Is a cluster keyword that specifies that connection requests can be sent to the high-availability data replication server. HDR is supported only by CLUSTER connection units.
`PRI` or `PRIMARY`	Is a cluster keyword that specifies that connection requests can be sent to the primary database server. PRI and PRIMARY are supported only by CLUSTER connection units.
`RSS`	Is a cluster keyword that specifies that connection requests can be sent to remote standalone secondary servers. RSS is supported only by CLUSTER connection units. You do not need to enclose RSS in parentheses to apply a redirection policy to the servers it specifies. If no redirection policy is specified, RSS uses the workload redirection policy.
`SDS`	Is a cluster keyword that specifies that connection requests can be sent to shared-disk secondary servers. SDS is supported only by CLUSTER connection units. You do not need to enclose SDS in parentheses to apply a redirection policy to the servers it specifies. If no redirection policy is specified, SDS uses the workload redirection policy.
`server`	Specifies server or alias entry in the Connection Manager's host sqlhosts file. Connection requests can be sent to the server.

MODE attribute values

Table 3. Values of the MODE attribute.
Attribute value	Value
`PROXY`	Specifies that the Connection Manager acts as a proxy server for client connections. Use proxy mode for the following cases: A firewall is preventing a client application from connecting to database servers. You do not want to recompile applications are compiled with Data Server Driver for JDBC and SQLJ version 3.5.1 or before, or with HCL OneDB™ Client Software Development Kit (Client SDK) 3.00 or before. Because a proxy-server Connection Manager handles all client/server communication, configure multiple Connection Manager instances, to avoid a Connection Manager becoming a single point of failure. Note: For proxy mode, you must set your operating system to allow the maximum number of file descriptors. For example, use the ulimit command on UNIX™ operating systems.
`REDIRECT` (default)	Specifies that client connections use redirect mode, which configures the Connection Manager to return the appropriate database server name, IP address, and port number to the requesting client application. The client application then uses the returned IP address and port number to connect to the specified database server.

POLICY attribute values

Table 4. Values of the POLICY attribute.
Attribute value	Value
`FAILURE`	Specifies that connection requests are directed or proxied to the replication server with the fewest apply failures. The apply-failure policy is supported by the following connection units: REPLSET GRID To use the apply-failure policy, you must enable quality of data (QOD) monitoring by running the cdr define qod and cdr start qod commands. To use the apply-failure policy for a grid, the grid must have a replication-enabled table.
`LATENCY`	Specifies that connection requests are directed or proxied to the replication server with the lowest transaction latency. The transaction-latency policy is supported by the following connection units: REPLSET GRID The attribute value does not indicate a specific transaction latency period; the Connection Manager uses a formula with relative values to decide where to redirect client connection requests. To use the transaction-latency policy, you must enable quality of data monitoring by running the cdr define qod and cdr start qod commands. To use the transaction-latency policy for a grid, the grid must have a replication-enabled table.
`ROUNDROBIN`	Specifies that connection requests are directed or proxied in a repeating, ordered fashion (round-robin) to a group of servers. If you use a round-robin policy, the DBSERVERS attribute values are used to create round-robin groups. Servers that are specified more than one time in a DBSERVERS-attribute group value are treated as single participants in a round-robin group. For example, if a SLA has the following definition: `SLA sla_1 DBSERVERS=(server_1,server_3,server_1,server_2) \ POLICY=ROUNDROBIN` The round-robin group participants are: server_1 server_2 server_3 The round-robin policy is supported by the following connection units: CLUSTER REPLSET GRID SERVERSET The round-robin policy is supported by the following software: All HCL OneDB server versions. Connection Managers from HCL OneDB Client Software Development Kit (Client SDK)
`SECAPPLYBACKLOG:number_of_pages`	Specifies that if a secondary server's apply backlog exceeds `number_of_pages`, the Connection Manager does not redirect or proxy new connections to server. For example: `SLA sla_1 DBSERVERS=(server_1,server_2,server_3) \ POLICY=SECAPPLYBACKLOG:500` The Connection Manager sends connection requests to whichever of server_1, server_2, or server_3 has an apply backlog below 500 pages and the lowest workload. To view the apply backlogs of all servers in a cluster, run the onstat -g cluster command. To view the apply backlog for a specific secondary server, run one of the following commands: onstat -g dri onstat -g sds onstat -g rss The apply-backlog policy is supported by CLUSTER connection units. The apply-backlog policy is supported by the following software: HCL OneDB server versions 11.70.xC8 and later, and 12.10.xC2 and later. Connection Managers from HCL OneDB Client Software Development Kit (Client SDK) 3.70.xC8 and later, and 4.10xC2 and later.
`WORKLOAD` (default)	Specifies that connection requests are directed or proxied to the database server with the lowest workload. Workload calculations are based on the number of virtual processors a server has and the number of threads in the server's ready queue. The WORKLOAD policy is supported by the following connection units: CLUSTER GRID REPLSET SERVERSET

USEALIASES attribute values

Table 5. Values of the USEALIASES attribute.
Attribute value	Value
`ON` (default)	Specifies that the Connection Manager can direct client connection requests to server aliases specified by a database server's DBSERVERALIASES configuration parameter.
`OFF`	Specifies that the Connection Manager cannot direct client connection requests to server aliases specified by a database server's DBSERVERALIASES configuration parameter.

HOST attribute values

Table 6. Values of the HOST attribute.
Attribute value	Value
`host_name`	Specifies a host name or host alias for a database server.
`ip_address`	Specifies a TCP/IP address for a database server.

NETTYPE attribute values

Table 7. Values of the NETTYPE attribute.
Attribute value	Value
`drsoctcp`	Specifies TCP/IP protocol for Distributed Relational Database Architecture™
`onsoctcp`	Specifies sockets with TCP/IP protocol.

SERVICE attribute values

Table 8. Values of the SERVICE attribute.
Attribute value	Value
`port_number`	Specifies a port number.
`service_name`	Specifies a service name.

Usage

The SLA parameter is optional, and is supported by the following connection units:

CLUSTER
GRID
REPLSET
SERVERSET

Client applications use the SLA name to connect to the database servers or database-server types that are specified by the value of the DBSERVERS attribute. For each SLA, a listener thread is installed at the specified port on the server to detect incoming client requests. The SLA parameter can be specified multiple times in the same configuration file; however, each SLA name must be unique.

Example 1: Connection request redirection from a service-level agreement

The following example shows a simple Connection Manager configuration. The configuration specifies a single SLA.

NAME my_connection_manager_1

CLUSTER my_cluster_1
{
   ONEDB_SERVER my_server_group_1
   SLA sla_1 DBSERVERS=SDS,HDR,PRI
   FOC ORDER=ENABLED PRIORITY=1
}

CONNECT TO @sla_1 connection requests as are directed in the following way:

Connect to any available SD secondary servers.
If SD secondary servers are unavailable, connect to the HDR secondary server.
If the HDR secondary server is unavailable, connect to the primary server.

Example 2: Defining multiple service-level agreements

The following example shows a simple Connection Manager configuration. The configuration specifies two SLAs.

NAME my_connection_manager_2

CLUSTER my_cluster_2
{
   ONEDB_SERVER my_server_group_2
   SLA sla_1 DBSERVERS=server_1
   SLA sla_2 DBSERVERS=server_2,server_3
   FOC ORDER=ENABLED PRIORITY=1
}

This example configures the Connection Manager for a high-availability cluster and defines two SLAs:

CONNECT TO @sla_1 connection requests are directed to server_1.
CONNECT TO @sla_2 connection requests are directed to server_2. If server_2 is not available, connection requests are directed to server_3.

Example 3: Redirection policies in service-level agreements

The following example shows a Connection Manager configuration that uses a workload-balancing redirection policy. The configuration specifies a single SLA.

NAME my_connection_manager_3

CLUSTER my_cluster_3
{
   ONEDB_SERVER my_server_group_3
   SLA sla_1 DBSERVERS=(server_1,server_2) \
             POLICY=WORKLOAD
   SLA sla_2 DBSERVERS=(server_3,server_4,server_5) \
             POLICY=ROUNDROBIN
   SLA sla_3 DBSERVERS=(server_6,server_7,server_8) \
             POLICY=ROUNDROBIN+SECAPPLYBACKLOG:400
   FOC ORDER=ENABLED PRIORITY=1
}

CONNECT TO @sla_1 connection requests are directed to whichever of server_1 and server_2 has the lowest workload. WORKLOAD is the default redirection policy, so specifying POLICY=WORKLOAD in the SLA is not required.
CONNECT TO @sla_2 connection requests are directed round-robin to server_3, server_4 and server_5.
CONNECT TO @sla_3 connection requests are directed round-robin to server_6, server_7 and server_8. If a server's apply backlog is 400 pages or greater, that server is ignored in the round-robin order and does not receive connection requests until its apply backlog falls below 400 pages.

Example 4: Proxy and redirect mode in service-level agreements

In redirect mode, the Connection Manager responds to client redirection requests by returning a specified database server's IP address and port number to the client application. The client application then uses the IP address and port number to connect to the database server. In proxy mode, the Connection Manager acts as a proxy server, and client requests are routed through the Connection Manager. Redirect mode is the default if no SLA mode is specified.

NAME my_connection_manager_4

CLUSTER my_cluster_4
{
   ONEDB_SERVER my_server_group_4
   SLA sla_1 DBSERVERS=ANY \
             MODE=REDIRECT #Default value, so is not required for the SLA definition
   SLA sla_2 DBSERVERS=ANY \
             MODE=PROXY
   FOC ORDER=ENABLED PRIORITY=1
}

CONNECT TO @sla_1 connection requests result in the Connection Manager returning the IP address and port number for a cluster server to the client application.
CONNECT TO @sla_2 connection requests are directed through the Connection Manager to a cluster server.

Example 5: Adjusting timeout values for the Connection Manager and cluster servers

The following example shows a Connection Manager configuration where default timeout values are changed:

NAME my_connection_manager_5
CM_TIMEOUT 300
EVENT_TIMEOUT 45
SECONDARY_EVENT_TIMEOUT 50

CLUSTER my_cluster_5
{
   ONEDB_SERVER my_server_group_5
   SLA sla_1 DBSERVERS=ANY
   FOC ORDER=ENABLED PRIORITY=1
}

If a cluster does not receive any events from the Connection Manager within 300 seconds, the primary server of the cluster promotes the next available Connection Manager to the role of failover arbitrator.
If the Connection Manager does not receive any events from the primary server within 45 seconds, the Connection Manager begins failover processing, and attempts to promote a secondary server to the primary server.
If the Connection Manager does not receive any events from a secondary server within 50 seconds, the Connection Manager disconnects from the secondary server.

Example 6: Macros and workload balancing in service-level agreements

The following example shows a Connection Manager configuration that uses defined macros in SLAs.

NAME my_connection_manager_6
MACRO CA=ca_server_1,ca_server_2,ca_server_3
MACRO NY=ny_server_1,ny_server_2,ny_server_3

REPLSET my_replicate_set_1
{
   ONEDB_SERVER my_er_group_1,my_er_group_2
   SLA sla_1 DBSERVERS=${CA}
   SLA sla_2 DBSERVERS=(${NY}) \
             POLICY=ROUNDROBIN
}

In this example, two macros are defined:

CA, which is composed of ca_server_1, ca_server_2, and ca_server_3.
NY, which is composed of ny_server_1, ny_server_2, and ny_server_3.

The Connection Manager redirects client connection requests as follows:

CONNECT TO @sla_1 connection requests are directed to ca_server_1. If ca_server_1 is unavailable, connection requests are directed to ca_server_2. If ca_server_2 is also unavailable, connection requests are directed to ca_server_3.
CONNECT TO @sla_2 connection requests are directed round-robin to ny_server_1, ny_server_2, and ny_server_3.

Example 7: Quality of data redirection policies in service-level agreements

The following example shows a Connection Manager configuration that uses transaction-latency and apply-failure redirection policies to direct connection requests in a grid. Quality-of-data (QOD) monitoring is turned on with the cdr define qod and cdr start qod commands.

NAME my_connection_manager_7

GRID my_grid_1
{
   ONEDB_SERVER my_server_group_1,my_server_group_2,my_server_group_3
   SLA sla_1 DBSERVERS=ANY \
             POLICY=LATENCY
   SLA sla_2 DBSERVERS=ANY \
             POLICY=FAILURE
   SLA sla_3 DBSERVERS=ANY \
             POLICY=2*Failure+LATENCY
}

CONNECT TO @sla_1 connection requests are directed to the server with the lowest transaction latency.
CONNECT TO @sla_2 connection requests are directed to the server with the lowest number of apply failures.
CONNECT TO @sla_3 connection requests are directed to the server with the lowest number of apply failures and lowest transaction latency. The smallest apply-failure count is twice as important as low transaction latency in the Connection Manager's calculations.

Example 8: sqlhosts connectivity information in service-level agreements

The following example shows a Connection Manager configuration that uses attribute values instead of the values in its host sqlhosts file for directing connection requests.

NAME my_connection_manager_8

SERVERSET my_server_set
{
   ONEDB_SERVER server_1,server_2,server_3
   SLA sla_1 DBSERVERS=server_1 \
             NETTYPE=onsoctcp
             HOST=host_1 \
             SERVICE=port_1 \
   SLA sla_2 DBSERVERS=server_2 \
             NETTYPE=onsoctcp
             HOST=host_2 \
             SERVICE=port_2 \
}

The Connection Manager uses the values of the HOST, SERVICE, and NETTYPE attributes, rather than the values in its host sqlhosts file for directing connection requests.

Example 9: Controlling connection-requests with aliases

The following example shows a Connection Manager configuration that specifies which database-server aliases SLAs can use.

The onconfig file for server_1 has the following parameter setting:

DBSERVERALIASES server_1_alias_1,server_1_alias_2

The sqlhosts file that server_1 uses has the following entries:

#dbservername      nettype   hostname   servicename  options
my_group_9         group     -          -            e=server_1_alias_2
server_1           onsoctcp  my_host_1  my_port_1    g=my_group_9
server_1_alias_1   onsoctcp  my_host_1  my_port_2    g=my_group_9
server_1_alias_2   onsoctcp  my_host_1  my_port_3    g=my_group_9

The Connection Manager configuration file for server_1 has the following entries:

NAME my_connection_manager_9

SERVERSET my_server_set_2
{
   ONEDB_SERVER my_group_9
   SLA sla_1 DBSERVERS=server_1
   SLA sla_2 DBSERVERS=server_1 \
             USEALIASES=OFF
   SLA sla_3 DBSERVERS=server_1_alias_1
   SLA sla_4 DBSERVERS=server_1_alias_1 \
             USEALIASES=OFF
}

The Connection Manager directs client requests in the following ways:

CONNECT TO @sla_1 and CONNECT TO @sla_3 requests can be directed to server_1, through my_port_1, my_port_2, or my_port_3.
CONNECT TO @sla_2 requests are directed to server_1 through my_port_1 only.
CONNECT TO @sla_4 requests are directed to server_1 through my_port_2 only.