Usage of pooled connections

[former_SQLA_member1694896]

Hello, all How do I know that connection from the client is being taken from the connection pool and does not create a new one? Is there something in the ODBC log for that?

Thank you Arcady

[VolkerBarth]

I don't know whether this can be checked via a connection property for a specific connection but the following database properties tell about the connection pooling state (at least for server-wise connection pooling via CPOOL=YES):

select db_property('ConnPoolCachedCount'),
       db_property('ConnPoolHits'),
       db_property('ConnPoolMisses');

so you could check with these counters both whether connection pooling is in effect at all and whether a new connection increases the ConnPoolHits counter.

Note, cached connections are listed with an empty connection name and the ReqType = CONNECT_POOL_CACHE.

[former_SQLA_member1694896]

ok, this is weird I have 2 DBs, with the same schema. 2 ODBC connections, who look exactly the same. But one of the DBs has pooled connections and the other does not.

This is the connection log for the DB with pooled connections:

//------------------------------------------------------

10:44:28 Attempting to connect using:
UID=g4_api;PWD=********;DBN=G4_center6;ServerName=G4_center6;CON=G4_WebService_API(G4_GetT068_package);INT=NO;LOG=C:\\Logger\\ODBC\\center6\\odbc.txt;CPOOL='YES(MaxCached=100)';Host=10.10.100.64
10:44:28 Attempting to connect to a running server...
10:44:28 Attempting TCPIP connection (address 10.10.100.64:49159 found in sasrv.ini cache)
10:44:28 Looking for server with name g4_center6
10:44:28 Trying to find server at cached address 10.10.100.64:49159 without broadcasting
10:44:28 Found database server g4_center6 on TCPIP link
10:44:28 Connected using client address 10.10.100.42:52508
10:44:28 Connected to server over TCPIP
10:44:28 Connected to SQL Anywhere Server version 17.0.9.4803
10:44:28 Application information:
10:44:28 IP=10.10.100.42;HOST=vm-g4-rsv-05;OSUSER=Silverbyte-G4-Central-Center6;OS='Windows 2012R2 Build 9600 ';EXE=c:\\windows\\system32\\inetsrv\\w3wp.exe;PID=0x65d0;THREAD=0x4968;VERSION=16.0.0.2798;API=ODBC;TIMEZONEADJUSTMENT=180
10:44:28 Connected to the server, attempting to connect to a running database...
10:44:28 [26284269] Connected to database successfully
10:44:28 [26284166] Disconnected and cached connection in pool
//--------------------------------------------------------

And this one for the one without

//-----------------------------

09:44:51 Attempting to connect using:
UID=dba;PWD=********;DBN=g4_center9;ServerName=g4_center9;CON=G4_WebService_API(g4_getMultiPrices);INT=NO;ENC=NONE;LOG=C:\\Logger\\ODBC\\center9\\odbc.txt;LINKS=TCPIP{};CPOOL='YES(MaxCached=100)'
09:44:51 Attempting to connect to a running server...
09:44:51 Attempting TCPIP connection (address 10.10.100.74:2639 found in sasrv.ini cache)
09:44:51 Looking for server with name g4_center9
09:44:51 Trying to find server at cached address 10.10.100.74:2639 without broadcasting
09:44:51 Found database server g4_center9 on TCPIP link
09:44:51 Connected using client address 10.10.100.58:50670
09:44:51 Connected to server over TCPIP
09:44:51 Connected to SQL Anywhere Server version 17.0.9.4803
09:44:51 Language 'hebrew' is not supported, 'us_english' will be used
09:44:51 Application information:
09:44:51 IP=10.10.100.58;HOST=vm-g4-rsv-02;OSUSER=Silverbyte-G4-Central-Center9;OS='Windows 2012R2 Build 9600 ';EXE=c:\\windows\\system32\\inetsrv\\w3wp.exe;PID=0xc10;THREAD=0x16a4;VERSION=16.0.0.1948;API=ODBC;TIMEZONEADJUSTMENT=180
09:44:51 Connected to the server, attempting to connect to a running database...
09:44:51 [7799612] Connected to database with a warning
09:44:57 [7799547] Client disconnected
09:44:57 [7799547] Disconnected from server

//-----------------------------

What am I supposed to be looking for?

Arcady

[VolkerBarth]

Well, AFAIK, connection pooling once starts when at least 5 connections with the same connection parameters have been made, so the log snippet does not tell that. FWIW, the client version seems different, but I don't know whether this might explain different behaviour.

[former_SQLA_member1694896]

OK, so which parameters count in order to pool the connection? I hope it is not AppInfo, because the thread ID will almost always be different Does connection name have to be the same?

[VolkerBarth]

See that FAQ (though dealing with .NET pooling) and further questions tagged with "connection-pooling"...

[former_SQLA_member1694896]

OK, so AppInfo is safe. Unfortunately, this still does not explain why I do not see a single pooled cache connection on the other DB... On the other hand, on this DB I do not have any connection errors, so - not that bad.

[VolkerBarth]

As stated, the different client version might make a difference here, if everything else is identical.