Automatic client reroute

[hummingbirdlj]

I have a couple questions about setting up automatic client reroute.
First, after setting the alternate server for a database, is there a
way to verify the settings later on? The next thing is I've added
these values for my HADR pair, but I'm not sure I'm testing the basic
functionality correctly. I connect to the primary, then issue a
switch role takeover, then attempt to continue running SQL against the
database (I've also tried a connect with the now deactivated
database). In all cases, I still get the SQL30081 error, so have
obviously done something wrong.

Thanks,
Lisa

[Mark A]

<hu***********@gmail.comwrote in message
news:85**********************************@e23g2000 prf.googlegroups.com...

>I have a couple questions about setting up automatic client reroute.
First, after setting the alternate server for a database, is there a
way to verify the settings later on? The next thing is I've added
these values for my HADR pair, but I'm not sure I'm testing the basic
functionality correctly. I connect to the primary, then issue a
switch role takeover, then attempt to continue running SQL against the
database (I've also tried a connect with the now deactivated
database). In all cases, I still get the SQL30081 error, so have
obviously done something wrong.

Thanks,
Lisa

How are you submitting the SQL statements? Using type 2 driver or type 4
driver?

[hummingbirdlj]

I am using type-2 right now (although later on, will need to use
type-4). Thanks.

On Dec 10, 11:07 pm, "Mark A" <[e-mail]:

<[e-mail] in message

news:85**********************************@e23g2000 prf.googlegroups.com...

I have a couple questions about setting up automatic client reroute.
First, after setting the alternate server for a database, is there a
way to verify the settings later on? The next thing is I've added
these values for my HADR pair, but I'm not sure I'm testing the basic
functionality correctly. I connect to the primary, then issue a
switch role takeover, then attempt to continue running SQL against the
database (I've also tried a connect with the now deactivated
database). In all cases, I still get the SQL30081 error, so have
obviously done something wrong.

Thanks,
Lisa

How are you submitting the SQL statements? Using type 2 driver or type 4
driver?

[Mark A]

<hu***********@gmail.comwrote in message
news:34**********************************@y5g2000h sf.googlegroups.com...

>I am using type-2 right now (although later on, will need to use
type-4). Thanks.

If you do a "db2 list db directory" on your client, it will show the
alternate server info from the last time the client successfully connected
to the primary database. If the information is not correct, then something
is wrong.

When you start using type 4, then things get a bit more complicated since,
although the alternate server information is returned to the client upon
connection, it is not persisted anywhere for use by the client, unless the
client saves it somewhere. You will have to do some research on how to use
Automatic Client Reroute with a type 4 connection. The version 9 driver may
offer some improvements over version 8, but I am not 100 sure about that.

[hummingbirdlj]

Thanks for the information to list the alternate server information.
I see that it is set properly. I've found that a connection is
automatically re-established to the same primary server, if the
connection is lost. However, if I try the following, I still get the
SQL30081 error:
1) connect to hadr primary database
2) execute a hadr switch roles
3) execute a query, which should result in a connection to the new
primary

Is there anything else I need to set-up in this case? I've set the
client registry settings for the number of connection retry attempts
and the interval. Thanks.

[Mark A]

<hu***********@gmail.comwrote in message
news:44**********************************@d61g2000 hsa.googlegroups.com...

Thanks for the information to list the alternate server information.
I see that it is set properly. I've found that a connection is
automatically re-established to the same primary server, if the
connection is lost. However, if I try the following, I still get the
SQL30081 error:
1) connect to hadr primary database
2) execute a hadr switch roles
3) execute a query, which should result in a connection to the new
primary

Is there anything else I need to set-up in this case? I've set the
client registry settings for the number of connection retry attempts
and the interval. Thanks.

[Mark A]

<hu***********@gmail.comwrote in message
news:44**********************************@d61g2000 hsa.googlegroups.com...

Thanks for the information to list the alternate server information.
I see that it is set properly. I've found that a connection is
automatically re-established to the same primary server, if the
connection is lost. However, if I try the following, I still get the
SQL30081 error:
1) connect to hadr primary database
2) execute a hadr switch roles
3) execute a query, which should result in a connection to the new
primary

Is there anything else I need to set-up in this case? I've set the
client registry settings for the number of connection retry attempts
and the interval. Thanks.

That's not exactly how it works. Try this from a DB2 command window:

1) Connect to the primary HADR db.
2) Issue a select statement.
3) Switch roles (issue takeover command on standby)
4) Issue another select statement. You will get an error message saying that
a takeover has occurred and asking you to retry the last SQL statement (I
don't recall the error number or exact text).
5) Retry the last query executed (it should work this time, getting data
from the standby).

If the above does not work, then you do have some sort of problem. Try
cataloging the database on the standby server (which is now primary) as a
type 2 connection and try to connect from DB2 command window. If that does
not work, make sure you can connect to the standby (now primary) as a local
user on the standby database server.

[hummingbirdlj]

Well, my explanation wasn't clear, but I tried exactly what you had
suggested from the DB2 command window. After the role switch, the
connection is not established to the new primary. The alternate
server and port information is correct in the list db information,
however.

I also tried the second test you suggested, and that also fails with
the sql30081 error. One thought I had is if both servers require the
same user id and password for the connection. I can't update the
passwords right now to match, or I'd try.

Thanks again.

>
1) Connect to the primary HADR db.
2) Issue a select statement.
3) Switch roles (issue takeover command on standby)
4) Issue another select statement. You will get an error message saying that
a takeover has occurred and asking you to retry the last SQL statement (I
don't recall the error number or exact text).
5) Retry the last query executed (it should work this time, getting data
from the standby).

If the above does not work, then you do have some sort of problem. Try
cataloging the database on the standby server (which is now primary) as a
type 2 connection and try to connect from DB2 command window. If that does
not work, make sure you can connect to the standby (now primary) as a local
user on the standby database server.

[Mark A]

<hu***********@gmail.comwrote in message
news:62**********************************@q3g2000h sg.googlegroups.com...

Well, my explanation wasn't clear, but I tried exactly what you had
suggested from the DB2 command window. After the role switch, the
connection is not established to the new primary. The alternate
server and port information is correct in the list db information,
however.

I also tried the second test you suggested, and that also fails with
the sql30081 error. One thought I had is if both servers require the
same user id and password for the connection. I can't update the
passwords right now to match, or I'd try.

Thanks again.

Yes, the user ids and passwords must match. Everything about the two
databases should be the same including all the tablesapce container paths
(or else certain DDL statements will not work).

It is not absolutely necessary for the instance names to be the same, but it
is recommended (especially now that TSA allows them to be the same).
Obviously, you can only do this if you have 2 different host names for
primary and standby.

[Mike Springgay]

Is your original type 2 connection using TCP/IP ? Automatic Client
Reroute support is only provided for TCP/IP connections. If you are
using the type 2 via local (IPC) connectivity no re-routing will be
done. Please make sure you've cataloged a database alias that connects
using TCP/IP to the database.

Please provide your connectivity information (i.e db2 list db directory
if not using connection strings).

hu***********@gmail.com wrote:

Well, my explanation wasn't clear, but I tried exactly what you had
suggested from the DB2 command window. After the role switch, the
connection is not established to the new primary. The alternate
server and port information is correct in the list db information,
however.

I also tried the second test you suggested, and that also fails with
the sql30081 error. One thought I had is if both servers require the
same user id and password for the connection. I can't update the
passwords right now to match, or I'd try.

Thanks again.

>1) Connect to the primary HADR db.
2) Issue a select statement.
3) Switch roles (issue takeover command on standby)
4) Issue another select statement. You will get an error message saying that
a takeover has occurred and asking you to retry the last SQL statement (I
don't recall the error number or exact text).
5) Retry the last query executed (it should work this time, getting data
from the standby).

If the above does not work, then you do have some sort of problem. Try
cataloging the database on the standby server (which is now primary) as a
type 2 connection and try to connect from DB2 command window. If that does
not work, make sure you can connect to the standby (now primary) as a local
user on the standby database server.

[hummingbirdlj]

I got a window to change the password, and now I'm all set. Thanks
very much for all of your help!

Yes, the user ids and passwords must match. Everything about the two
databases should be the same including all the tablesapce container paths
(or else certain DDL statements will not work).