DB2 10.5 for Linux, UNIX, and Windows

Automatic client reroute examples

Automatic client reroute (ACR) can reroute client applications away from a failed database server to a secondary database server previously identified and configured for this purpose. You can easily create client applications that test and demonstrate this DB2® server functionality.

Here is an automatic client reroute example for a client application (shown using pseudo-code only):

        int checkpoint = 0;

        check_sqlca(unsigned char *str, struct sqlca *sqlca)
        {
           if (sqlca->sqlcode == -30081)
           {
              // as communication is lost, terminate the application right away
              exit(1);
           }
           else
           
              // print out the error
              printf(...);  

        	if (sqlca->sqlcode == -30108)
        	{
        	   // connection is re-established, re-execute the failed transaction
                 if (checkpoint == 0)
                 {
                     goto checkpt0;
                 }
        	   else if (checkpoint == 1)
                 {
                    goto checkpt1;
                 }
                 else if (checkpoint == 2)
                 {
                     goto checkpt2;
                 }
                 ....
                 exit;
        	}
         }
        }

        main()
        {
           connect to mydb;
           check_sqlca("connect failed", &sqlca);

        checkpt0:
           EXEC SQL set current schema XXX;
           check_sqlca("set current schema XXX failed", &sqlca);

           EXEC SQL create table t1...;
           check_sqlca("create table t1 failed", &sqlca);

           EXEC SQL commit;
           check_sqlca("commit failed", &sqlca);
 
           if (sqlca.sqlcode == 0)
           {
              checkpoint = 1;
           }

        checkpt1:
           EXEC SQL set current schema YYY;
           check_sqlca("set current schema YYY failed", &sqlca);

           EXEC SQL create table t2...;
           check_sqlca("create table t2 failed", &sqlca);   

           EXEC SQL commit;
           check_sqlca("commit failed", &sqlca);
 
           if (sqlca.sqlcode == 0)
           {
              checkpoint = 2;
           }
        ...
        }

At the client machine, the database called "mydb" is cataloged which references a node "hornet" where "hornet" is also cataloged in the node directory (hostname "hornet" with port number 456).

Example involving a non-HADR database

At the server "hornet" (hostname equals hornet with a port number), a database "mydb" is created. Furthermore, the database "mydb" is also created at the alternate server (hostname "montero" with port number 456). The alternate server for database "mydb" at server "hornet" needs to be updated as follows:

   db2 update alternate server for database mydb using hostname montero port 456

In the sample application above, and without having the automatic client reroute feature set up, if there is a communication error in the create table t1 statement, the application is terminated. With the automatic client reroute feature set up, the DB2 database manager tries to establish the connection to host "hornet" (with port 456) again. If it is still not working, the DB2 database manager tries the alternate server location (host "montero" with port 456). Assuming there is no communication error on the connection to the alternate server location, the application can then continue to run subsequent statements (and to re-run the failed transaction).

Example involving an HADR database

At the server "hornet" (hostname equals hornet with a port number), primary database "mydb" is created. A standby database is also created at host "montero" with port 456. Information on how to set up HADR for both a primary and standby database is found in Initializing high availability disaster recovery (HADR). The alternate server for database "mydb" needs to be updated as follows:

   db2 update alternate server for database mydb using hostname montero port 456

In the sample application provided, and without having the automatic client reroute feature set up, if there is a communication error in the create table t1 statement, the application is terminated. With the automatic client reroute feature set up, the DB2 database system tries to establish the connection to host "hornet" (with port 456) again. If it is still not working, the DB2 database system tries the alternate server location (host "montero" with port 456). Assuming there is no communication error on the connection to the alternate server location, the application can then continue to run subsequent statements (and to re-run the failed transaction).

In a DB2 pureScale environment configured with HADR, the behavior is similar. The first time a client connects to the primary database, the server returns the addresses of all members on the primary, plus the alternate server address which specifies the hostname and port of a standby member. If a client cannot connect to one member on the primary, it tries another. If it cannot connect to any member on the primary, it tries the standby at the specified alternate server address.

Example involving SSL

You can also use client reroute while you are using SSL for your connections too. The setup is the similar to that shown for the previous example for an HADR database.

At the client machine, a database alias "mydb_ssl" for the database "mydb" is cataloged that references a node, "hornet_ssl". "hornet_ssl" is cataloged in the node directory (hostname is "hornet", SSL port number is 45678, and the security parameter is set to SSL).

A database alias is also cataloged at the alternate server (hostname is "montero", SSL port number is 45678, and the security parameter is set to SSL). You also need to update the alternate server for alias "mydb_ssl" at server "hornet" as shown:

db2 update alternate server for database mydb_ssl using hostname montero port 45678

In the sample application provided, change the connect statement to connect to mydb_ssl. Without having the automatic client reroute feature set up, if there is a communication error in the create table t1 statement, the application is terminated. With the automatic client reroute feature set up, the DB2 database manager tries to establish the connection to host "hornet" (with port 45678) using SSL again. If it is still does not work, the DB2 database manager tries the alternate server location (host "montero" at port 45678) using SSL. Assuming there is no communication error on the connection to the alternate server location, the application can then continue to run subsequent statements (and to re-run the failed transaction).