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).