Defining the Transparent Connectivity connection

High Availability Guide : Using HotStandby with applications : Transparent Connectivity : Defining the Transparent Connectivity connection

Transparent Connectivity is specified using non-standard ODBC connection string settings or JDBC connection properties.

Syntax of Transparent Connectivity Info – ODBC

When using solidDB^® Transparent Connectivity, the client enacts only one logical connection called the TC Connection. With ODBC, the TC connection is specified in the TC Info. TC Info enacts transparent failover and load balancing in both HSB configurations.

With ODBC applications, the TC connection can be specified in the following ways:

▪SQLConnect function:

rc = SQLConnect(comHandle, "<solidDB^®_TC_Info>", ...

▪Client-side solid.ini file:

[Com]
Connect = <solidDB^®_TC_Info>

The syntax of the solidDB^® TC Info is:

<solidDB^®_TC_Info>::= {[<failure_transparency_level_attribute>] [<preferred_access_attribute>] [<encryption_attribute>] <connect_target_list>} | <cluster_info>

failure_transparency_level_attribute ::= TF_LEVEL={NONE | CONNECTION | SESSION}

preferred_access_attribute::= PREFERRED_ACCESS={WRITE_MOSTLY | READ_MOSTLY | LOCAL_READ}

encryption_attribute::=USE_ENCRYPTION={YES|NO}

connect_target_list::= [SERVERS=]<connect_string>[, <connect_string > ...]

cluster_info::= CLUSTER= <connect_string>[, <connect_string>...]

The following abbreviations can be used.

Abbreviation	Corresponding syntax
TF	TF_LEVEL
CO	CONNECTION
SES	SESSION
PA	PREFERRED_ACCESS
RM	READ_MOSTLY
WM	WRITE_MOSTLY
LR	LOCAL_READ
S	SERVERS

Failure transparency attribute

Failure transparency handles the masking of failures. The failure transparency level is set with the TF_LEVEL attribute of the TC Info. Three levels are available:

1 NONE – failure transparency is disabled. This is the default value.

2 CONNECTION – the server connection is preserved, that is, it is unnecessary to reconnect in the case of failover or switchover.

3 SESSION – certain session attributes that have non-default values are preserved. Additionally, prepared statements are preserved. However, open cursors are closed, and ongoing transactions are aborted.

Preferred access attribute: Load balancing

The preferred access attribute (PREFERRED_ACCESS) indicates whether the load balancing of read-only loads is applied or not. The following levels are available:

▪WRITE_MOSTLY – no load balancing (default). All transactions are executed on the Primary.

▪READ_MOSTLY – load balancing by distributing read-only transactions between Primary and Secondary, as defined by Cluster.ReadMostlyLoadPercentAtPrimary

▪LOCAL_READ – load balancing by executing read-only transactions locally when possible. Read-only transactions are always directed to the local server, be it Primary or Secondary. If local server cannot be found, the Primary server’s assigned workload connection is used (first network-based connection defined in the connect string). Write transactions are always executed at the Primary server.

LOCAL_READ is typical with SMA setups where there is at least one SMA application on each node.

Important: When using SMA with Transparent Connectivity (TC), if you set the load balancing method to READ_MOSTLY or WRITE_MOSTLY (default), a network connection is used instead of the SMA connection. Thus, when using SMA with TC, always set the load balancing method to LOCAL_READ.

Connect target list and cluster info: Server addresses

The solidDB^® TC Info includes a list of server addresses, called connect target list. If the HotStandby configuration includes SMA, a SMA-specific connect target list must be used.

The syntax of the connect target list for network-based applications is:

[SERVERS:] <network_connect_string>, <network_connect_string>

The syntax of the connect target list for SMA applications is:

[SERVERS:] <sma_connect_string>, <network_connect_string>

network_connect_string

The format of a connect string for network-based connections is the following:

protocol_name [options] [host_computer_name] server_name

where options can be any combination of the following:

Option	Description	Protocol
-4	Specifies that client connects using IPv4 protocol only.	TCP/IP
-6	Specifies that client connects using IPv6 protocol only. In Windows environments, this option is mandatory if IPv6 protocol is used.	TCP/IP
-isource_address	Specifies an explicit connecting socket source address for cases where the system default source IP address binding does not meet application needs. source_address can be an IP address or a host name.	TCP/IP
-z	Enables data compression for the connection Important: ▪Data compression is not available for HotStandby connections (HotStandby.Connect) and NetBackup connections (ADMIN COMMAND 'netbackup'). ▪Data compression for netcopy connections cannot be enabled with the -z option. Instead, use the HotStandby.NetcopyRpcCompress=yes parameter setting.	All
-c milliseconds	Specifies the login timeout (the default is operating-system-specific). A login request fails after the specified time has elapsed.	TCP/IP
-r milliseconds	Specifies the connection (or read) timeout. A network request fails when no response is received during the time specified. The value 0 (default) sets the timeout to infinite (operating system default timeout applies).	TCP/IP
-ofilename	Turns on the Network trace facility and defines the name of the trace output file See Network trace facility in the solidDB^® Administrator Guide for details.	All
-plevel	Pings the server at the given level (0-5). Clients can always use the solidDB^® Ping facility at level 1 (0 is no operation/default). Levels 2, 3, 4 or 5 may only be used if the server is set to use the Ping facility at least at the same level. See “Ping facility” in the solidDB^® Administrator Guide for details.	All
-t	Turns on the Network trace facility See “Network trace facility” in the solidDB^® Administrator Guide for details.	All

▪host_computer_name is needed with TCP/IP and Named Pipes protocols, if the client and server are running on different machines.

▪server_name depends on the communication protocol:

– In TCP/IP protocol, server_name is a service port number, such as 2315.

– In other protocols, server_name is a name, such as solidDB or chicago_office.

For details on the syntax in different communication protocols, see Communication protocols in the solidDB^® Administrator Guide.

Note

▪The protocol_name and the server_name must match the ones that the server is using in its network listening name.

▪If given at the connection time, the connect string must be enclosed in double quotation marks.

▪All components of the connect string are case insensitive.

sma_connect_string

The format of a connect string for SMA-based connections is the following: sma protocol_name port_number | pipe_name

When SMA is used, applications on each node must be able to connect to the local server with a SMA connection and to the remote server with a network-based connection. This means that the list of server addresses takes the following format:

connect_target_list::= [SERVERS=]<sma_connect_string>, <network_connect_string>

cluster_info::= CLUSTER <sma_connect_string>, <network_connect_string>

For example:

sma tcp 2315, tcp 192.168.255.1 1315

The driver will scan the list from left to right and try to find the Primary and Secondary servers. Therefore, the preferable configuration must be put at the beginning of the list. The rest of the list may contain some spare addresses that might be activated at some point during the system lifetime. Keep the list short; in error situations, it can take a long time before the error is returned to the application. The addresses are tried one by one, involving the login timeouts specified (network-based connections). The number of addresses in the list is unlimited.

If none of the attributes TF_LEVEL nor PREFERRED_ACCESS is specified (or TF_LEVEL=NONE), the connection behavior falls back to Basic Connectivity. If more than one connect string is given, the connection is established to the first server on the list that accepts the connection request.

Configuring server addresses with multi-home servers

If your setup uses multi-home servers to deploy different networks for the connections between the application and the servers and the servers themselves, you need to define the server addresses for the TC connection with the HotStandby.TCConnect parameter.

From the application connection perspective, the address specified with the HotStandby.TCConnect parameter precedes the address defined with the HotStandby.Connect parameter. The TC connection will thus use the server addresses specified with HotStandby.TCConnect parameter, while the HotStandby connection between the servers uses the server addresses defined with the HotStandby.Connect parameter.

For an example of a multi-home server configuration, see Example: TC connection with multi-home servers.

CLUSTER

The CLUSTER keyword sets TF_LEVEL to SESSION and PREFERRED_ACCESS to READ_MOSTLY automatically.

For example, the following TC Info and the CLUSTER string are interchangeable:

TF_LEVEL=SESSION PREFERRED_ACCESS=READ_MOSTLY SERVERS=tcp srv1.acme.com 1315, tcp srv2.acme.com 1315

CLUSTER=tcp srv1.acme.com 1315, tcp srv2.acme.com 1315

Tip: The cluster configuration can also be defined in the client-side solid.ini file, in which case the connect string in SQLConnect can use the logical name. For example:

rc = SQLConnect(comHandle, "Cluster1", ...

[Data Sources]
Cluster1=
  TF_LEVEL=SESSIONPREFERRED_ACCESS=READ_MOSTLY
  SERVERS=
    tcp -c 1000 srv1.dom.acme.com 1315,
    tcp srv2.dom.acme.com     1315,
    tcp srv3.dom.acme.com 1316

Encryption attributes

The encryption attributes control whether the password for the connection is encrypted. The USE_ENCRYPTION keyword controls whether encryption is used. If USE_ENCRYPTION=NO, encryption is disabled.

▪If USE_ENCRYPTION=YES the solidDB^® built-in DES algorithm is used for encryption.

SQLConnect examples

rc = SQLConnect(comHandle, "TF=CONNECTION
      USE_ENCRYPTION=YES PA=READ_MOSTLY
      SERVERS=
      tcp -c 1000 srv1.dom.acme.com 1315,
      tcp srv2.dom.acme.com 1315,
      tcp srv3.dom.acme.com 1316", ...

rc = SQLConnect(comHandle, "CLUSTER=
      tcp -c 1000 srv1.dom.acme.com 1315,
      tcp srv2.dom.acme.com 1315,
      tcp srv3.dom.acme.com 1316", ...

SMA setup: SQLConnect on the application on Node1 (srv1.dom.acme.com)

rc = SQLConnect(comHandle, "TF=CONNECTION
      PA=LOCAL_READ
      SERVERS=
      sma tcp 1315,
      tcp srv2.dom.acme.com 2315", ...

SMA setup: SQLConnect on the application on Node2 (srv2.dom.acme.com)

rc = SQLConnect(comHandle, "TF=CONNECTION
      PA=LOCAL_READ
      SERVERS=
      sma tcp 2315,
      tcp srv1.dom.acme.com 1315", ...

Client-side solid.ini examples

For layout reasons, the Com.Connect parameter values in the following examples are split on several lines. In your solid.ini file, the entire parameter entry must be on one line.

[Com]
Connect = TF=CONNECTION USE_ENCRYPTION=YES
      PA=READ_MOSTLY
      SERVERS=
         tcp -c 1000 srv1.dom.acme.com 1315,
         tcp srv2.dom.acme.com 1315,
         tcp srv3.dom.acme.com 1316

SMA setup: solid.ini file on Node1 (srv1.dom.acme.com)

[Com]
Connect = TF=CONNECTION USE_ENCRYPTION=YES
      PA=LOCAL_READ
      SERVERS=
         sma tcp 1315,
         tcp srv2.dom.acme.com 2315

SMA setup: solid.ini file on Node2 (srv2.dom.acme.com)

[Com]
Connect = TF=CONNECTION USE_ENCRYPTION=YES
      PA=LOCAL_READ
      SERVERS=
         sma tcp 2315,
         tcp srv1.dom.acme.com 1315

See also

Transparent connectivity with JDBC

Transparent Connectivity

Transparent connectivity with JDBC

With JDBC, transparent connectivity is enabled with non-standard connection properties. The list of server addresses is given as a part of the JDBC connect string.

Note When using transparent connectivity in JDBC, you have to take care of dropping the statement objects explicitly. The garbage collector will not detect unreferenced statement objects.

Failure transparency level (solid_tf_level)

Failure transparency is enabled with the solid_tf_level connection property. The value is a string; you can specify it as a mnemonic (for example, NONE) or as a number (0 for NONE). For clarity, use of mnemonics is preferable.

Three levels are available:

1 NONE | 0 – failure transparency is disabled. This is the default value.

2 CONNECTION | 1 – the server connection is preserved, that is, it is unnecessary to reconnect in the case of failover or switchover.

3 SESSION | 3 – certain session attributes that have non-default values are preserved. Additionally, prepared statements are preserved. However, open cursors are closed, and ongoing transactions are aborted.

Preferred access attribute (solid_preferred_access)

The preferred access attribute indicates whether a read-only load is distributed or not. The preferred access attribute is enabled with the solid_preferred_access connection property. The value is a string; you can specify it as a mnemonic or as a number. For clarity, use of mnemonics is preferable.

The following levels are available:

▪WRITE_MOSTLY | 0 – the read workload is directed to Primary. This is the default value. WRITE_MOSTLY also sets the connection to the WRITE MOSTLY mode. It is not possible to do that by specifying a numeric value.

▪READ_MOSTLY | 1 – the read workload is directed to Secondary and Primary as defined by the Cluster.ReadMostlyLoadPercentAtPrimary parameter. The write transactions are handed over to the Primary.

By default, the Cluster.ReadMostlyLoadPercentAtPrimary parameter is set to 50, which means that 50 percent of the read loads are directed to the Primary.

Reconnect timeout (solid_tf1_reconnect_timeout)

The solid_tf1_reconnect_timeout property specifies the connection reconnect timeout in milliseconds. The default value is 10 000 milliseconds (10 seconds).

Server addresses

The list of server addresses is given as a part of the extended JDBC connect string:

conStr= "jdbc:solid://host_name:port [,host_name:port].../user_name/password";

The number of addresses in the address list is limited to 20.

Configuring server addresses with multi-home servers

SMA connection (solid_shared_memory)

With SMA configurations, the non-standard property solid_shared_memory must be set to yes. You must also define that you are using a local server at a given port.

Example: TC connection with failure transparency level of CONNECTION

...
String conStr = "jdbc:solid://srv1.acme.com:1323,srv2-acme.com:1423/dba/dba";
Properties prop = new Properties();
prop.setProperty("solid_tf_level", "CONNECTION");
...
Connection c = DriverManager.getConnection(conStr, prop);
...

Example: TC connection with SMA

...
String conStr = "jdbc:solid://localhost:1323,srv2-acme.com:1423/dba/
dba?solid_shared_memory=yes";
Properties prop = new Properties();
prop.setProperty("solid_tf_level", "CONNECTION");
Connection c = DriverManager.getConnection(conStr, prop);
...

Example: TC connection with load balancing

...
String conStr = "jdbc:solid://12.345.67.88:1323,12.345.67.89:1423/dba/dba";
Properties prop = new Properties();
prop.setProperty("solid_tf_level", "CONNECTION");
prop.setProperty("solid_preferred_access", "READ_MOSTLY");
...
Connection c = DriverManager.getConnection(conStr, prop);
...

Example: JDBC URL defined TC connection with load balancing

jdbc:solid://12.345.67.88 1323, 12.345.67.89 1423/ dba/dba?solid_tf_level=1?solid_preferred_access=READ_MOSTLY

TC attribute combinations

The following table summarizes the possible combinations of the TC attributes and presents the resulting connection capabilities:

PREFERRED_ ACCESS:	TF_LEVEL: Not specified or NONE	TF_LEVEL: CONNECTION	TF_LEVEL: SESSION
Not specified	▪No failover or switchover support ▪No load balancing (Basic connectivity)	▪Transparent failover (session state not preserved) ▪Transparent switchover ▪Workload in Primary only ▪No load balancing	▪Transparent failover (session state preserved) ▪Transparent switchover ▪Workload in Primary only ▪No load balancing
WRITE_MOSTLY (default)	▪No transparent failover support ▪Transparent switchover ▪Workload in Primary only ▪No load balancing	▪Transparent failover (session state not preserved) ▪Transparent switchover ▪Workload in Primary only ▪No load balancing	▪Transparent failover (session state preserved) ▪Transparent switchover ▪Workload in Primary only ▪No load balancing
READ_MOSTLY	▪No transparent failover support ▪Transparent switchover ▪Workload in Secondary and Primary ▪Load balancing	▪Transparent failover (session state not preserved) ▪Transparent switchover ▪Workload in Secondary and Primary ▪Load balancing	▪Transparent failover (session state preserved) ▪Transparent switchover ▪Workload in Secondary and Primary ▪Load balancing
LOCAL_READ	▪No transparent failover support ▪Transparent switchover ▪Primary reads and writes executed locally using SMA connection ▪Secondary reads executed locally using SMA connection, writes on Primary using network connection	▪Transparent failover (session state not preserved ▪Transparent switchover ▪Primary reads and writes executed locally using SMA connection ▪Secondary reads executed locally using SMA connection, writes on Primary using network connection	▪Transparent failover (session state preserved ▪Transparent switchover ▪Primary reads and writes executed locally using SMA connection ▪Secondary reads executed locally using SMA connection, writes on Primary using network connection

Connect error processing

When a connect request is issued for a TC Connection, it is considered successful if at least one applicable server is found and connected to.

The server may be in one of the states: PRIMARY ACTIVE, PRIMARY ALONE, or STANDALONE. Otherwise, the connect effort is considered failed. The address list is scanned once.

There may be various reasons for the connect request to fail. Most of them are masked by the following error cases:

SQLSTATE	Native code	Message text and description
08001	25217	Client unable to establish a connection Description: The driver has used the TC connect info to find an applicable server and connect to it. The effort has failed due to one of he following reasons: ▪No host listed in the address list was found ▪A host was found but the login timed out ▪A host was found but the login was rejected ▪Hosts found but not in the PRIMARY/ STANDALONE state
HY000	21307	Invalid connect info... Description: a syntax error is found in an elementary connect string or in the TC connect info (data source info).
HY000	21300	Protocol ... not supported. Description: the string “TC” in the beginning of the TC connect info is misspelled (or, an incorrect protocol name is given in the elementary connect string).

There are cases when the connection is accepted with a warning.

SQLSTATE	Native code	Message text and description
0100	25218	Connected to Standalone or Primary Alone server. An effort has been made to set any non-default value of TF_LEVEL or PREFERRED_ACCESS, and there is only one server available. On this case, neither failure transparency nor load balancing is available.

Example: TC connection

This example shows a Transparent Connectivity configuration for a typical HotStandby setup where the application and the servers are within the same network.

Example: TC connection with multi-home servers

This example shows a Transparent Connectivity configuration for a HotStandby setup where the Primary and Secondary are multi-home servers. It is typical in setups where the application and the servers are in different networks or the application cannot or should not connect to the servers using the same network that is used for the HotStandby connection between the servers.

To differentiate between the TC connection and the HotStandby connection, you need to use two parameters that define the server addresses:

The HotStandby.Connect parameters specify the server addresses that are used for the HotStandby connection between the primary and secondary servers.

The HotStandby.TCConnect parameters specify the server addresses that are used with the TC connection.