Automatic client reroute examples

        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 1 (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). You will also need to update the alternate server for database "mydb" at server "hornet" as follows:

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 will be terminated. With the automatic client reroute feature set up, the DB2 database manager will try to establish the connection to host "hornet" (with port 456) again. If it is still not working, the DB2 database manager will try 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 2 (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 setup HADR for both a primary and standby database is found in Data Recovery and High Availability Guide and Reference. You will also need to update the alternate server for database "mydb" as follows:

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 will be terminated. With the automatic client reroute feature set up, the DB2 database system will try to establish the connection to host "hornet" (with port 456) again. If it is still not working, the DB2 database system will try 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 3 (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 Example2.

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

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

In the sample application, above, 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 will be 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).