The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

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

asked 17 Jun, 02:43

Arcady%20Abramov's gravatar image

Arcady Abramov
11681118
accept rate: 0%

edited 17 Jun, 05:23

Justin%20Willey's gravatar image

Justin Willey
7.3k128165238

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.

(17 Jun, 03:20) Volker Barth

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

(17 Jun, 03:49) Arcady Abramov
Replies hidden

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.

(17 Jun, 04:39) Volker Barth

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?

(17 Jun, 04:50) Arcady Abramov
Replies hidden

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

(17 Jun, 05:05) Volker Barth

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.

(17 Jun, 05:20) Arcady Abramov
Replies hidden

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

(17 Jun, 05:31) Volker Barth
showing 4 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×146
×14

question asked: 17 Jun, 02:43

question was seen: 137 times

last updated: 17 Jun, 05:31