With Transparent Connectivity (TC), the driver uses two methods to direct the transaction load; one to handle read-intensive load and the other to handle write-intensive load. For load balancing, the logical TC connection is mapped to a lower-level server connection called the workload connection. The workload connection can change over time and it is normally of no concern to the application.
Before you implement load balancing, be aware of the following limitations:
If the default (startup) isolation level for the server is set to a different value, enabling load balancing forces the isolation level of the session to READ COMMITTED. If the isolation level is dynamically reset to a higher level after startup (for example, REPEATABLE READ), load balancing is disabled, see Overriding load balancing configuration.
▪ To use load balancing, autocommit must be disabled for the session.
The Transparent Connectivity (TC) load balancing method is set by using the PREFERRED_ACCESS attribute for an ODBC connection, see Transparent Connectivity with ODBC, or with the non-standard property solid_preferred_access for a JDBC connection, see Transparent Connectivity with JDBC.
The following load balancing methods are available:
▪ WRITE_MOSTLY: No load balancing occurs and all transactions are executed at the primary server. This is the default option and corresponds to the typical HotStandby operation. The WRITE_MOSTLY method is useful with write-intensive loads.
▪ READ_MOSTLY: Read-only transactions can be executed on both the primary and secondary servers. Write transactions are always executed at the primary server.
For read-only transactions, the assigned workload server is selected on the basis of the Cluster.ReadMostlyLoadPercentAtPrimary parameter setting, which specifies the percentage of the total read-only load that is directed to the primary server.
The default value of the Cluster.ReadMostlyLoadPercentAtPrimary parameter is 50, which means that, by default, half of the connections use the primary server and half use the secondary server. This is usually the most appropriate value for mixed loads. If the value is set to zero, all the load is directed to the secondary server. This might be appropriate in cases where very read-intensive (or read-only) applications use READ_MOSTLY and (at the same time) write-intensive applications use WRITE_MOSTLY.
With READ_MOSTLY, the primary server tells the driver which server to connect to for the workload connection. If the load for a given connection is directed to the secondary server, and a write operation is executed, a handover to the primary server takes place and the transaction is executed in the primary server. After the transaction commit, the load is directed back to the secondary server. If the secondary server fails, the connection fails over from the secondary server to the primary server.
▪ LOCAL_READ: Read-only transactions are always directed to the local server, be it the primary or secondary server. Write transactions are always executed at the primary server.
The LOCAL_READ method is typically used with SMA setups. The application on the primary server uses the SMA connection for read and write transactions. The application on the secondary server uses SMA connection for reads and a network-based connection for writes on the primary server.
Important When using SMA with TC, if you set the load balancing method to READ_MOSTLY or WRITE_MOSTLY, 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.