Excessive DB connections are opened by client via OpenSwitch

[ying...@gmail.com]

We encountered a problem when using Sybase OpenSwitch server between
our client application and ASE server. The problem is that the client
application uses excessive database connections. When the same client
application connects ASE server directly, it only uses up to 3
database connections. But when it connects to ASE via OpenSwitch, it
uses up to 28 database connections and no errors in the OpenSwitch
server log. When multiple clients use excessive connections, they
exceed the connection limits defined in the OpenSwitch configuration
file. Following errors are in the OpenSwitch log file:

Aug 14 16:30:12 2008: OpenSwitch: DEBUG: spid 275: CT-Lib Error:
number(6) origin(1) layer(1)
: ct_connect(): user api layer: external error: The maximum number of
connections have already been opened.
Aug 14 16:30:12 2008: OpenSwitch: ERROR: spid 275:
thrd__ct_connect:ct_connect failed.
Aug 14 16:30:12 2008: OpenSwitch: ERROR: spid 275: thrd_connect:
thrd__connect failed.

The client application is using JConnect 5.5 and java 1.4.2. It reuses
the connections it has opened. The OpenSwitch server is 12.5.1 and ASE
server is 12.5. They are both on Solaris 8. Since the system is at a
customer site, the OpenSwitch server is configured with no debug on
and FULL_PASSTHRU = 0, TCP_NODELAY = 1
TCP_KEEPALIVE = 0, USE_DONEINPROCS = 1. The same system
configuration running at the development lab has no such problem at
all.

Any idea what could be wrong ?

Thanks.

Ying

[SybaseNeal]

Hello,

Normally, OpenSwitch only opens 1 connection per client application
plus 1 connection to monitor the health of ASE. So if you had 20
clients connect to OpenSwitch, you should see 21 connections in ASE.

However, OpenSwitch has it's own connection caching feature. If you
look at your POOL definitions in your OpenSwitch.cfg file, what do
you have for the "CACHE" parameter? If it is set to zero, that means
OpenSwitch will not keep any connections open at ASE. Once a client
disconnects, OpenSwitch closes the equivalent connection on ASE.
[POOL=POOL1:STATUS=UP,MODE=CHAINED,CACHE=80]

If you are not using connection caching in OpenSwitch itself, are you
certain your client application is closing connections properly? It
isn't leaving zombie spids in OpenSwitch?

You might try enabling the debug flags DEBUG=eh. "e" will echo any
errors to the log, "h" will show connect/disconnect events.

You might also set SHOW_CONNECT_ERROR=1 in your OpenSwitch.cfg file.

Finally, when this is happening, you may want to log in as the
OpenSwitch administrator and gather "rp_dump thread" output a few
times with a minute or two of separation. This output would help us
see if there were any zombie spids.

Thanks,
Neal

[ying...@gmail.com]

Hello, Neal,

Thank you very much! I will try to enable debug “eh” at
the customer site.
I think I did not describe the problem very clearly. Our
client application needs more than one db connections from
time to time.
So the client application maintains its own database
connection pool. It reuses the connections in the pool. The
client application closes idle connection after 30 min.

Now the problem is that the same client application connects
ASE directly it only opens up to 3 connections in its pool.
But the same client application connects ASE via OpenSiwtch
it opens up to 28 connections in its pool. I use rp_who on
OpenSwitch and sp_who on ASE to check. The number of
connections for the client on ASE side equals the number of
connections on OpenSwitch side. So the answer to your
question is client closes connections properly and seems to
have no zombie spid in openswitch. My question is why same
client application behaves differently via OpenSwitch.

We do set CACHE = 0 and SHOW_CONNECT_ERROR=1.
Occasionally we see following errors in OpenSwitch log file:
Sep 3 15:03:05 2008: OpenSwitch: WARN: spid 61:
CS_DATA_NOTIME was set in the response capabilities of the
ASE that the original connection was made to. In the server
that is being failed over to CS_DATA_NOTIME is not set in
the response capbilities. This may cause problems with your
Application. If it does then disconnect and reconnect to
OpenSwitch.
Sep 8 16:38:24 2008: OpenSwitch: DEBUG: spid 275: CT-Lib
Error: number(44) origin(1) layer(4)
: ct_connect(): protocol specific layer: external error: The
attempt to connect to the server failed.
Sep 8 16:38:24 2008: OpenSwitch: ERROR: spid 275:
thrd__ct_connect:ct_connect failed.
Sep 8 16:38:24 2008: OpenSwitch: ERROR: spid 275:
thrd_connect: thrd__connect failed.

Does the error have anything to do with the connection
issue?

Thanks,

YL

[SybaseNeal]

Hello,

The CS_DATA_NOTIME message should not have anything to do with
this issue. It usually indicates that when OpenSwitch switched
a client from one ASE to another, the version of ASE was different.

Different versions of ASE support different capabilities. When a
client connects to a server, they negotiate what each end is able
to support. In the message below the original ASE indicated that
it could not support the CS_TIME data type by setting CS_DATA_NOTIME.
The secondary ASE indicated it COULD support CS_TIME because it
set CS_DATA_NOTIME to false.

You may want to post your question to the jConnect newsgroup as
they might have a better idea as to what you need to check on the
client side as far as pooling, debugging and why the jConnect
application starts more connections when it connects to OpenSwitch versus ASE.

p.s.
You can enable the OpenSwitch debug flags without restarting it
by logging in as the OpenSwitch admin user and issuing "rp_debug eh, on".

Thanks,
Neal

[SybaseNeal]

Forgot to provide a link to the jConnect newsgroup:
news://forums.sybase.com:119/sybase.public.jconnect