To configure failover:

  • Configure the basic connection options used to establish a connection:
    • Set the Host Name (HostName) option to specify the name or the IP address of the server to which you want to connect.
    • Set the Database Name (Database) option to specify the name of the database to which you want to connect by default.
    • Set the Port Number (PortNumber) option to specify the port number of your server listener. For example, 5432.
  • Set the Alternate Servers connection option to specify one or more alternate database servers that are tried at connection time if the primary server is not accepting connections. Connection attempts continue until a connection is successfully established or until all the database servers in the list have been tried once (the default).
  • Set the Failover Mode connection option to one of the following values.
    • If set to 0 (Connection), the driver provides failover protection for new connections only.

    • If set to 1 (Extended Connection), the driver provides failover protection for new and lost connections, but not any work in progress.

    • If set to 2 (Select), the driver provides failover protection for new and lost connections. In addition, it preserves the state of work performed by the last Select statement executed.

  • If Extended Connection Failover (FailoverMode=1) or Select Connection Failover (FailoverMode=2) is enabled, set the Failover Granularity (FailoverGranularity) connection option to specify how you want the driver to behave if errors occur while trying to reestablish a lost connection:

    • If set to 0 (Non-Atomic), the driver continues with the failover process and posts any errors on the statement on which they occur. This is the default driver behavior.

    • If set to 1 (Atomic) the driver fails the entire failover process if an error is generated as the result of anything other than executing and repositioning a Select statement. If an error is generated as a result of repositioning a result set to the last row position, the driver continues with the failover process, but generates a warning that the Select statement must be reissued.

    • If set to 2 (Atomic Including Repositioning), the driver fails the entire failover process if any error is generated as the result of restoring the state of the connection or the state of work in progress.
    • If set to 3 (Disable Integrity Check), the driver does not verify that the rows that were restored during the failover process match the original rows. This value applies only when Failover Mode is set to 2 (Select).
  • Optionally, enable the Failover Preconnect connection option (FailoverPreconnect=1) if you want the driver to establish a connection with the primary and an alternate server at the same time. This can be useful if your application is time-sensitive and cannot absorb the wait for the failover connection to succeed. Note that this value applies only when Extended Connection (FailoverMode=1) or Select (FailoverMode=2) is enabled. The default behavior is to connect to an alternate server only when failover is caused by an unsuccessful connection attempt or a lost connection (FailoverPreconnect=0).
  • Optionally, set the Connection Retry Count (ConnectionRetryCount) connection option to specify the number of times the driver attempts to connect to the primary and alternate database servers after the initial unsuccessful connection attempt. The default is 0 retry attempts.
  • Optionally, set the Connection Retry Delay (ConnectionRetryDelay) connection option to specify the wait interval, in seconds, between attempts to connect to the primary and alternate database servers. The default interval is 3 seconds.
  • Optionally, enable the Load Balancing (LoadBalancing=1) connection option to configure the driver to use client load balancing and attempt to connect to the database servers (primary and alternate servers) in random order.
The following examples show how to configure the driver to use two alternate servers for connection failover in conjunction with the following optional features:
  • The driver attempts to retry connection up to four times if the initial attempt fails.
  • The driver waits five seconds between connection attempts.
  • The driver attempts primary and alternate servers in random order.

Connection string example

The following DSN-less connection string configures the driver to use two alternate servers as connection failover servers.

DRIVER=DataDirect 8.0 Greenplum Wire Protocol;HostName=MyServer;PortNumber=5432;
Database=MyDB;AlternateServers=(HostName=greenplum:PortNumber=5432:
Database=Accounting,HostName=255.201.11.24:PortNumber=5431:Database=Accounting);
ConnectionRetryCount=4;ConnectionRetryDelay=5;LoadBalancing=1;FailoverMode=0;
LogonID=John;Password=secret;

odbc.ini file example

The following odbc.ini configuration tells the driver to use two alternate servers as connection failover servers. Note that this example demonstrates a configuration for the 32-bit version of the driver. 

Driver=ODBCHOME/lib/ivgplmxx.so
Description=DataDirect Greenplum Wire Protocol
...
AlternateServers=(HostName=greenplum:PortNumber=5432:Database=Accounting, 
HostName=255.201.11.24:PortNumber=5431:Database=Accounting)
...
ConnectionRetryCount=4
...
ConnectionRetryDelay=5
...
Database=MyDB
...
HostName=MyServer
...
LoadBalancing=1
...
FailoverMode=0
...
FailoverPreconnect=1
...
LogonID=John
...
Password=secret
...
PortNumber=5432
...
Note: The LogonID and Password options are not required to be stored in the data source. They can also be sent separately by the application using the SQLConnect ODBC API. For SQLDriverConnect and SQLBrowseConnect, they will need to be specified in the data source or connection string.