Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\userguide\advanced.06.2.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Using Failover</title> <link rel="StyleSheet" href="css/advanced.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="advanced.06.1.html#106175">3 Advanced Features</a> : Using Failover</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106175">Using Failover</a></div> <div class="Body"><a name="123291">To ensure continuous, uninterrupted access to data, </a>the DataDirect Connect Series <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers provide the following levels of failover protection, listed from basic to more comprehensive: </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><span class="Emphasis"><a name="108151">Connection failover</a></span> provides failover protection for new connections only. The driver fails over new connections to an alternate, or backup, database server if the primary database server is unavailable, for example, because of a hardware failure or traffic overload. If a connection to the database is lost, or dropped, the driver does not fail over the connection. This failover method is the default. </div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><span class="Emphasis"><a name="108152">Extended connection failover</a></span> provides failover protection for new connections and lost database connections. If a connection to the database is lost, the driver fails over the connection to an alternate server, preserving the state of the connection at the time it was lost, but not any work in progress.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><span class="Emphasis"><a name="108153">Select Connection failover</a></span> provides failover protection for new connections and lost database connections. In addition, it provides protection for Select statements that have work in progress. If a connection to the database is lost, the driver fails over the connection to an alternate server, preserving the state of the connection at the time it was lost and preserving the state of any work being performed by Select statements.</div> </td> </tr> </table> </div> <div class="Body"><a name="124372">The method you choose depends on how failure tolerant your application is. For example, if </a>a communication failure occurs while processing, can your application handle the recovery of transactions and restart them? Your application needs the ability to recover and restart transactions when using either extended connection failover mode or select connection failover mode. The advantage of select mode is that it preserves the state of any work that was being performed by the Select statement at the time of connection loss. If your application had been iterating through results at the time of the failure, when the connection is reestablished the driver can reposition on the same row where it stopped so that the application does not have to undo all of its previous result processing. For example, if your application were paging through a list of items on a Web page when a failover occurred, the next page operation would be seamless instead of starting from the beginning. Performance, however, is a factor in selecting a failover mode. Select mode incurs additional overhead when tracking what rows the application has already processed. </div> <div class="Body"><a name="108155">You can specify which failover method you want to use by setting the </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190325', '');">Failover Mode</a></span> connection option. Read the following sections for details on each failover method:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#108798', '');" name="108159">â??Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#106809', '');" name="108163">â??Extended Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#106827', '');" name="108167">â??Select Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="Body"><a name="108168">Regardless of the failover method you choose, you must configure one or multiple alternate </a>servers using the <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190289', '');">Alternate Servers</a></span> connection option. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#108205', '');">â??Guidelines for Primary and Alternate Serversâ??</a></span> for information about primary and alternate servers.</div> <div class="head_2"><a name="108798">Connection Failover</a></div> <div class="Body"><a name="108826">Connection failover is available in the following DataDirect Connect</a> Series <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="175935">DB2 Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="194987">Greenplum Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175952">Informix Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175959">MySQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175966">Oracle Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195048">Oracle</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175973">PostgreSQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187604">Progress OpenEdge Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175980">SQL Server Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195034">SQL Server Legacy Wire Protocol (UNIX only)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="175987">Sybase Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="194999">Sybase IQ Wire Protocol</a></div> </td> </tr> </table> </div> <div class="Body"><a name="108821">Connection failover allows an application to connect to an alternate, or backup, database </a>server if the primary database server is unavailable, for example, because of a hardware failure or traffic overload. Connection failover provides failover protection for new connections only and does not provide protection for lost connections to the database, nor does it preserve states for transactions or queries.</div> <div class="Body"><a name="106178">You can customize the drivers for connection failover by configuring a list of alternate </a>database servers that are tried if the primary server is not accepting connections. Connection attempts continue until a connection is successfully established or until all the alternate database servers have been tried the specified number of times. </div> <div class="Body"><a name="106180">For example, suppose you have the environment shown in the following illustration with </a>multiple database servers: Database Server A, B, and C. Database Server A is designated as the primary database server, Database Server B is the first alternate server, and Database Server C is the second alternate server.</div> <div class="picture"><a name="191148"><img class="drawing" src="images/odbcfailover.gif" width="470" height="269" style="display: block; float: none; left: 0.0; top: 0.0;" alt="Connection Failover Diagram" /></a></div> <div class="Body"><a name="106224">First, the application attempts to connect to the primary database server, Database Server </a>A (<span class="Action_bold">1</span>). If connection failover is enabled and Database Server A fails to accept the connection, the application attempts to connect to Database Server B (<span class="Action_bold">2</span>). If that connection attempt also fails, the application attempts to connect to Database Server C (<span class="Action_bold">3</span>). </div> <div class="Body"><a name="106225">In this scenario, it is probable that at least one connection attempt would succeed, but if no </a>connection attempt succeeds, the driver can retry each alternate database server (primary and alternate) for a specified number of attempts. You can specify the number of attempts that are made through the <span class="Emphasis">connection retry</span> feature. You can also specify the number of seconds of delay, if any, between attempts through the <span class="Emphasis">connection delay</span> feature. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#106286', '');">â??Using Connection Retryâ??</a></span> for more information about connection retry.</div> <div class="Body"><a name="119507">A driver fails over to the next alternate database server only if a successful connection </a>cannot be established with the current alternate server. If the driver successfully establishes communication with a database server and the connection request is rejected by the database server because, for example, the login information is invalid, then the driver generates an error and does not try to connect to the next database server in the list. It is assumed that each alternate server is a mirror of the primary and that all authentication parameters and other related information are the same.</div> <div class="Body"><a name="106231">For details on configuring connection failover for your driver, see the appropriate driver </a>chapter in this book. </div> <div class="head_2"><a name="106809">Extended Connection Failover</a></div> <div class="Body"><a name="108838">Extended connection failover is available in the following DataDirect Connect Series</a> <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="176032">DB2 Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195078">Greenplum Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176052">MySQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176059">Oracle Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176066">PostgreSQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187657">Progress OpenEdge Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176077">SQL Server Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187669">Sybase Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176086">Sybase IQ Wire Protocol</a></div> </td> </tr> </table> </div> <div class="Body"><a name="106810">Extended connection failover provides failover protection for the following types of </a>connections:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106814">New connections, in the same way as described in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#108798', '');">â??Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="106815">Lost connections</a></div> </td> </tr> </table> </div> <div class="Body"><a name="106816">When a connection to the database is lost, the driver fails over the connection to an </a>alternate server, restoring the same state of the connection at the time it was lost. For example, when reestablishing a lost connection on the alternate database server, the driver performs the following actions:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106817">Restores the connection using the same connection options specified by the lost </a>connection</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106818">Reallocates statement handles and attributes</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106819">Logs in the user to the database with the same user credentials</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106820">Restores any prepared statements associated with the connection</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106821">Restores manual commit mode if the connection was in manual commit mode at the </a>time of the failover</div> </td> </tr> </table> </div> <div class="Body"><a name="124773">The driver does not preserve work in progress. For example, if the database server </a>experienced a hardware failure while processing a query, partial rows processed by the database and returned to the client would be lost. If the driver was in manual commit mode and one or more Inserts or Updates were performed in the current transaction before the failover occurred, then the transaction on the primary server is rolled back. The Inserts or Updates done before the failover are not committed to the primary server. Your application needs to rerun the transaction after the failover because the Inserts or Updates done before the failover are not repeated by the driver on the failover connection.</div> <div class="Body"><a name="124892">When a failover occurs, if a statement is in allocated or prepared state, the next operation </a>on the statement returns a SQL state of 01000 and a vendor code of 0. If a statement is in an executed or prepared state, the next operation returns a SQL state of 40001 and a vendor code of 0. Either condition returns an error message similar to:</div> <div class="syntax_first"><a name="124893">Your connection has been terminated. However, you have been successfully </a>connected to the next available AlternateServer: 'HOSTNAME=Server4:PORTNUMBER= 1521:SERVICENAME=test'. All active transactions have been rolled back.</div> <div class="Body"><a name="124138">The driver retains all connection settings made through ODBC API calls when a failover </a>connection is made. It does not, however, retain any session settings established through SQL statements. This can be done through the Initialization String connection option, described in the individual driver chapters.</div> <div class="Body"><a name="124139">The driver retains the contents of parameter buffers, which can be important when failing </a>over after a fetch. All Select statements are re-prepared at the time the failover connection is made. All other statements are placed in an allocated state.</div> <div class="Body"><a name="106823">If an error occurs while the driver is reestablishing a lost connection, the driver can fail the </a>entire failover process or proceed with the process as far as it can. For example, suppose an error occurred while reestablishing the connection because a table for which the driver had a prepared statement did not exist on the alternate connection. In this case, you may want the driver to notify your application of the error and proceed with the failover process. You can choose how you want the driver to behave if errors occur during failover by setting the <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190316', '');">Failover Granularity</a></span> connection option.</div> <div class="Body"><a name="125173">During the failover process, your application may experience a short pause while the driver </a>establishes a connection on an alternate server. If your application is time-sensitive (a real-time customer order application, for example) and cannot absorb this wait, you can set the <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190334', '');">Failover Preconnect</a></span> connection option to true. Setting the Failover Preconnect option to true instructs the driver to establish connections to the primary server and an alternate server at the same time. Your application uses the first connection that is successfully established. If this connection to the database is lost at a later time, the driver saves time in reestablishing the connection on the server to which it fails over because it can use the spare connection in its failover process.</div> <div class="Body"><a name="125295">This pre-established failover connection is not used by the driver until the driver determines </a>that it needs to fail over. If the server to which the driver is connected or the network equipment through which the connection is routed is configured with a timeout, the pre-configured failover connection could time out. The pre-configured failover connection can also be lost if the failover server is brought down and back up again. The driver tries to establish the connection to the failover server again if the connection is lost.</div> <div class="head_2"><a name="106827">Select Connection Failover</a></div> <div class="Body"><a name="108934">Select connection failover is available in the following DataDirect Connect Series</a> <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="176105">DB2 Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195405">Greenplum Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176122">MySQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176129">Oracle Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176136">PostgreSQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187716">Progress OpenEdge Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="176143">SQL Server Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187701">Sybase Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195491">Sybase IQ Wire Protocol</a></div> </td> </tr> </table> </div> <div class="Body"><a name="195495">Select connection failover provides failover protection for the following types of connections:</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106832">New connections, in the same way as described in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#108798', '');">â??Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106835">Lost connections, in the same way as described in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#106809', '');">â??Extended Connection Failoverâ??</a></span></div> </td> </tr> </table> </div> <div class="Body"><a name="119237">In addition, the driver can recover work in progress because it keeps track of the last Select </a>statement the application executed on each Statement handle, including how many rows were fetched to the client. For example, if the database had only processed 500 of 1,000 rows requested by a Select statement when the connection was lost, the driver would reestablish the connection to an alternate server, re-execute the Select statement, and position the cursor on the next row so that the driver can continue fetching the balance of rows as if nothing had happened.</div> <div class="Body"><a name="126161">Performance, however, is a factor when considering whether to use Select mode. Select </a>mode incurs additional overhead when tracking what rows the application has already processed.</div> <div class="Body"><a name="125487">NOTE: The driver only recovers work requested by Select statements. You must explicitly </a>restart the following types of statements after a failover occurs:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="125488">Insert, Update, or Delete statements</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="125489">Statements that modify the connection state, for example, SET or ALTER SESSION </a>statements</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106841">Objects stored in a temporary tablespace or global temporary table</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106842">Partially executed stored procedures and batch statements</a></div> </td> </tr> </table> </div> <div class="Body"><a name="106843">When in manual transaction mode, no statements are rerun if any of the operations in the </a>transaction were Insert, Update, or Delete. This is true even if the statement in process at the time of failover was a Select statement.</div> <div class="Body"><a name="125530">By default, the driver verifies that the rows that are restored match the rows that were </a>originally fetched and, if they do not match, generates an error warning your application that the Select statement must be reissued. By setting the <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190316', '');">Failover Granularity</a></span> connection option, you can customize the driver to ignore this check altogether or fail the entire failover process if the rows do not match.</div> <div class="Body"><a name="125860">When the row comparison does not agree, the default behavior of Failover Granularity </a>returns a SQL state of 40003 and an error message similar to:</div> <div class="syntax_first"><a name="125867">Unable to position to the correct row after a successful failover attempt to </a>AlternateServer: 'HOSTNAME=Server4:PORTNUMBER= 1521:SERVICENAME=test'. You must reissue the select statement.</div> <div class="Body"><a name="125909">If you have configured Failover Granularity to fail the entire failover process, the driver </a>returns a SQL state of 08S01 and an error message similar to:</div> <div class="syntax_first"><a name="125910">Your connection has been terminated and attempts to complete the failover </a>process to the following Alternate Servers have failed: AlternateServer: 'HOSTNAME=Server4:PORTNUMBER= 1521:SERVICENAME=test'. All active transactions have been rolled back.</div> <div class="head_2"><a name="108205">Guidelines for Primary and Alternate Servers</a></div> <div class="Body"><a name="108206">Many databases provide advanced database replication technologies such as DB2 High </a>Availability Disaster Recovery (HADR) and Oracle Real Application Clusters (RAC). The failover functionality provided by the drivers does not require any of these technologies, but can work with them to provide comprehensive failover protection. Use the following guidelines for primary and alternate servers to ensure that failover works correctly in your environment:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="108207">Alternate servers should mirror data on the primary server or be part of a configuration </a>where multiple database nodes share the same physical data.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="108209">If using failover with DB2 HADR, the primary server must be the primary server </a>configured in your HADR system and any alternate server must be a standby server configured in your HADR system.</div> </td> </tr> </table> </div> <div class="head_2"><a name="108361">Using Client Load Balancing</a><span style="font-size: 11.0pt; font-weight: normal;"> </span></div> <div class="Body"><a name="106236">Client load balancing is available in the following DataDirect Connect</a> Series <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="178422">DB2 Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195521">Greenplum Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178424">Informix Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178425">MySQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178426">Oracle Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195544">Oracle</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178427">PostgreSQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187772">Progress OpenEdge Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178428">SQL Server Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195573">SQL Server Legacy Wire Protocol (UNIX only)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178429">Sybase Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195557">Sybase IQ Wire Protocol</a></div> </td> </tr> </table> </div> <div class="Body"><a name="106237">Client load balancing helps distribute new connections in your environment so that no one </a>server is overwhelmed with connection requests. When client load balancing is enabled, the order in which primary and alternate database servers are tried is random. For example, suppose that client load balancing is enabled as shown in the following illustration:</div> <div class="picture"><a name="191250"><img class="drawing" src="images/odbcloadbal.gif" width="490" height="285" style="display: block; float: none; left: 0.0; top: 0.0;" alt="Client Load Balancing Diagram" /></a></div> <div class="Body"><a name="125797">First, Database Server B is tried (</a><span class="Action_bold">1</span>). Then, Database Server C may be tried (<span class="Action_bold">2</span>), followed by a connection attempt to Database Server A (<span class="Action_bold">3</span>). In contrast, if client load balancing were not enabled in this scenario, each database server would be tried in sequential order, primary server first, then each alternate server based on its entry order in the alternate servers list.</div> <div class="Body"><a name="125801">Client load balancing is controlled by the </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190343', '');">Load Balancing</a></span> connection option. For details on configuring client load balancing, see the appropriate driver chapter in this book.</div> <div class="head_2"><a name="106286">Using Connection Retry</a></div> <div class="Body"><a name="157353">Connection retry is available in the following DataDirect Connect Series</a> <span class="forbody">for</span> <span class="APIbody">ODBC</span> drivers:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="178569">DB2 Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195302">Greenplum Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178571">Informix Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178572">MySQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178573">Oracle Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195330">Oracle</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178574">PostgreSQL Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="187856">Progress OpenEdge Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178575">SQL Server Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195345">SQL Server Legacy Wire Protocol (UNIX only)</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="178576">Sybase Wire Protocol</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="195315">Sybase IQ Wire Protocol</a></div> </td> </tr> </table> </div> <div class="Body"><a name="157376">Connection retry defines the number of times the driver attempts to connect to the primary </a>server and, if configured, alternate database servers after the initial unsuccessful connection attempt. It can be used with connection failover, extended connection failover, and select failover. Connection retry can be an important strategy for system recovery. For example, suppose you have a power failure in which both the client and the server fails. When the power is restored and all computers are restarted, the client may be ready to attempt a connection before the server has completed its startup routines. If connection retry is enabled, the client application can continue to retry the connection until a connection is successfully accepted by the server.</div> <div class="Body"><a name="106290">Connection retry can be used in environments that have only one server or can be used as </a>a complementary feature with connection failover in environments with multiple servers. </div> <div class="Body"><a name="106291">Using the connection options </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190298', '');">Connection Retry Count</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190307', '');">Connection Retry Delay</a></span>, you can specify the number of times the driver attempts to connect and the time in seconds between connection attempts. For details on configuring connection retry, see the appropriate driver chapter in this book.</div> <div class="head_2"><a name="114331">Summary of Failover-Related Options</a></div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190279', '');" name="188235">Table 3-1</a></span> summarizes how failover-related connection options work with the drivers. See "Connection Option Descriptions" in each driver chapter for details about configuring the options. Not all options are available in every failover-enabled driver. The step numbers in the table refer the procedure that follows the table</div> <div class="Body_Wide"><a name="190350"> </a></div> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 3-1. <a name="190279">Summary: Failover and Related Connection Options</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="190284">Option</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="190286">Characteristic</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190289">Alternate Servers</a></div> <div class="table_text"><a name="190293">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#190354', '');">Step 1</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190295">One or multiple alternate database servers. An IP address or server name </a>identifying each server is required.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190298">Connection Retry Count</a></div> <div class="table_text"><a name="190302">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#118539', '');">Step 5</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190304">Number of times the driver retries the primary database server, and if specified, </a>alternate servers until a successful connection is established.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190307">Connection Retry Delay</a></div> <div class="table_text"><a name="190311">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#118540', '');">Step 6</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190313">Wait interval, in seconds, between connection retry attempts when the </a>Connection Retry Count option is set to a positive integer.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190316">Failover Granularity</a></div> <div class="table_text"><a name="190320">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#124659', '');">Step 3</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190322">The type of behavior that the driver exhibits when errors are detected during the </a>failover process.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190325">Failover Mode</a></div> <div class="table_text"><a name="190329">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#118536', '');">Step 2</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190331">The type of failover that the driver attempts.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190334">Failover Preconnect</a></div> <div class="table_text"><a name="190338">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#124661', '');">Step 4</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190340">Determines whether the driver makes a connection attempt to the next server in </a>the Alternate Servers list at the time of the initial connection.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190343">Load Balancing</a></div> <div class="table_text"><a name="190347">(see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'advanced.06.2.html#118541', '');">Step 7</a></span>)</div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="190349">Determines whether the driver uses client load balancing in its attempts to </a>connect to primary and alternate database servers. If enabled, the driver attempts to connect to the database servers in random order.</div> </td> </tr> </table> <div class="list_procedure_first_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_first_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">1 </span> </div> </td> <td width="100%"> <div class="list_procedure_first_inner"><a name="190354">To configure connection failover, you </a><span class="Action_bold">must</span> specify one or more alternate database servers that are tried at connection time if the primary server is not accepting connections. To do this, use the Alternate Servers connection option. Connection attempts continue until a connection is successfully established or until all the database servers in the list have been tried once (the default).</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">2 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="118536">Choose a failover method by setting the Failover Mode connection option. The default </a>method is Connection (FailoverMode=0).</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">3 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="124659">If Failover Mode is Extended Connection (FailoverMode=1) or Select (FailoverMode=</a>2), set the Failover Granularity connection option to specify how you want the driver to behave if errors occur while trying to reestablish a lost connection. The default behavior of the driver is Non-Atomic (FailoverGranularity=0), which continues with the failover process and posts any errors on the statement on which they occur. Other values are:</div> </td> </tr> </table> </div> <div class="Body_Indent"><a name="124672">Atomic (FailoverGranularity=1): the driver fails the entire failover process if an error is </a>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. </div> <div class="Body_Indent"><a name="124673">Atomic including Repositioning (FailoverGranularity=2): the driver fails the entire </a>failover process if any error is generated as the result of restoring the state of the connection or the state of work in progress.</div> <div class="Body_Indent"><a name="124660">Disable Integrity Check (FailoverGranularity=3: the driver does not verify that the rows </a>restored during the failover process match the original rows. This value applies only when Failover Mode is set to Select (FailoverMode=2).</div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">4 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="124661">Optionally, enable the Failover Preconnect connection option (FailoverPreconnect=1) if </a>you want the driver to establish a connection with the primary and an alternate server at the same time. This value applies only when Failover Mode is set to Extended Connection (FailoverMode=1) or Select (FailoverMode=2). 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).</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">5 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="118539">Optionally, specify the number of times the driver attempts to connect to the primary </a>and alternate database servers after the initial unsuccessful connection attempt. By default, the driver does not retry. To set this feature, use the Connection Retry Count connection option.</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">6 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="118540">Optionally, specify the wait interval, in seconds, between attempts to connect to the </a>primary and alternate database servers. The default interval is 3 seconds. To set this feature, use the Connection Retry Delay connection option.</div> </td> </tr> </table> </div> <div class="list_procedure_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_procedure_inner" style="width: 18pt; white-space: nowrap;"> <span class="Numbers">7 </span> </div> </td> <td width="100%"> <div class="list_procedure_inner"><a name="118541">Optionally, specify whether the driver will use client load balancing in its attempts to </a>connect to primary and alternate database servers. If load balancing is enabled, the driver uses a random pattern instead of a sequential pattern in its attempts to connect. The default value is not to use load balancing. To set this feature, use the Load Balancing connection option.</div> </td> </tr> </table> </div> <div class="head_3"><a name="118551">A Connection String Example</a></div> <div class="Body"><a name="118552">The following connection string configures the Oracle Wire Protocol driver to use </a>connection failover in conjunction with some of its optional features.</div> <div class="syntax_wide_first"><a name="151550">DSN=AcctOracleServer;AlternateServers=(HostName=AccountingOracleServer:PortNumber=1521:</a></div> <div class="syntax_wide"><a name="187879">SID=Accounting, HostName=255.201.11.24:PortNumber=1522:ServiceName=ABackup.NA.MyCompany);</a></div> <div class="syntax_wide"><a name="187880">ConnectionRetryCount=4;ConnectionRetryDelay=5;LoadBalancing=1;FailoverMode=0</a></div> <div class="Body"><a name="187881">Specifically, this connection string configures the driver to use two alternate servers as </a>connection failover servers, to attempt to connect four additional times if the initial attempt fails, to wait five seconds between attempts, to try the primary and alternate servers in a random order, and to attempt reconnecting on new connections only. The additional connection information required for the alternate servers is specified in the data source AcctOracleServer. </div> <div class="head_3"><a name="118558">An odbc.ini File Example</a></div> <div class="Body"><a name="118559">To configure the </a>32-bit Oracle Wire Protocol driver to use connection failover in conjunction with some of its optional features in your odbc.ini file, you could set the following connection string attributes:</div> <div class="syntax_wide_first"><a name="118560">Driver=ODBCHOME/lib/ivora</a><span class="EquationVariables">xx</span>.so</div> <div class="syntax_wide"><a name="118561">Description=DataDirect Oracle Wire Protocol driver</a></div> <div class="syntax_wide"><a name="118562">...</a></div> <div class="syntax_wide"><a name="170261">AlternateServers=(HostName=AccountingOracleServer:PortNumber=1521:SID=Accounting, </a></div> <div class="syntax_wide"><a name="170363">HostName=255.201.11.24:PortNumber=1522:ServiceName=ABackup.NA.MyCompany)</a></div> <div class="syntax_wide"><a name="170344">...</a></div> <div class="syntax_wide"><a name="118568">ConnectionRetryCount=4</a></div> <div class="syntax_wide"><a name="118569">ConnectionRetryDelay=5</a></div> <div class="syntax_wide"><a name="118570">...</a></div> <div class="syntax_wide"><a name="118571">LoadBalancing=0</a></div> <div class="syntax_wide"><a name="135857">...</a></div> <div class="syntax_wide"><a name="135858">FailoverMode=1</a></div> <div class="syntax_wide"><a name="118572">...</a></div> <div class="syntax_wide"><a name="118573">FailoverPreconnect=1</a></div> <div class="syntax_wide"><a name="118575">...</a></div> <div class="Body"><a name="118576">Specifically, this odbc.ini configuration tells the driver to use two alternate servers as </a>connection failover servers, to attempt to connect four additional times if the initial attempt fails, to wait five seconds between attempts, to try the primary and alternate servers in sequential order (do not use load balancing), to attempt reconnecting on new and lost connections, and to establish a connection with the primary and alternate servers at the same time.</div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de