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.
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
hornet(hostname equals hornet with a port number), a database
mydbis created. Furthermore, the database
mydbis also created at the alternate server (hostname
monterowith port number 456). The alternate server for database
mydbat server
hornetneeds 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
hornet(hostname equals hornet with a port number), primary database
mydbis created. A standby database is also created at host
monterowith 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
mydbneeds 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).
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_sslat server
hornetas 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).