Connection to DB2 gets redirected to disabled HADR database

[JoonaH]

I'm connecting to a DB2 database using following code and this works just fine. After this I run the same code expect I assign schema MYSCHEMA2 to library MYLIB2. This second connection to the database runs into a problem however. Instead of connecting to the server 11.111.111.111 it instead connects to that servers backup server 11.111.111.110 which has a disabled HADR database. Also the connection doesn't fail right away but SAS tries to connect to that database for 10 minutes before finally giving up. After the 10 minutes gives "Error: [IBM][CLI Driver] SQL1776N The command cannot be issued on an HADR database. Reason code = "1"." Trying to connect directly to HADR by replacing the IP in the connection string gives the previously mentioned error right away.

Does any have ideas what could be causing this? Any ideas on what places should be checked are welcome.

LIBNAME MYLIB1 ODBC SCHEMA="MYSCHEMA1" 
noprompt="Driver={IBM DB2 ODBC DRIVER - DB2COPY1};
Hostname=11.111.111.111;
Protocol=TCPIP;
Port=50000;
Database=mydb;
READ_LOCK_TYPE=NOLOCK;
READ_ISOLATION_LEVEL=RU;
Uid=userid;
Pwd=password";

[Patrick]

Just having some thoughts here:

What could change the IP address? It's not SAS, it can't be the ODBC driver afaik so it must be DB2. I believe your best bet to understand what's happening here is talking to a DB2 admin that's responsible for this database/schema.

If you have access to a database client like DBeaver that allows you to access DB2 directly then you could further test this theory by taking SAS out of the picture.

[JoonaH]

Thank you for the ideas. So far nothing "wrong" has been found on DB2 side. The alternate HADR server is configured there but everything there seems to be as they should be. By logging network traffic we were able to confirm that only the first connection attempt (that is successful also) goes to primary IP 111. The second attempt and each attempt after that go towards IP 110 already from the SAS server. Thus the redirection happens at the SAS server.

We have DB2 client on SAS server and that can connect to the primary 111 just fine. Though I don't even know how to actually replicate this situation on anything else than SAS as it is unclear to me how this "second connection" works technically compared to other clients.

On DB2 client connecting the first time to primary server will return the address of HADR server and that is stored in DB2 client. One theory I have is that when using SAS this same happens, but SAS doesn't know how to interpret this newly received address and instead of storing it as a backup option it stores is a primary option. So in a sense SAS tells it self that from now on anytime the user tries to connect to 111 I will redirect it to this new 110.

[Patrick]

@JoonaH  Sounds like you should contact SAS Tech Support directly.