KB5972: Does MicroStrategy Intelligence Server 9.x use the 'Connection Pooling' feature of the ODBC driver manager?

What is the 'Connection Pooling' feature maintained by the ODBC Driver Manager?
Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a pool, an application can re-use that connection without performing the complete connection process.
An ODBC driver must be fully thread-safe and connections must not have thread affinity to support connection pooling. This means the driver is able to handle a call on any thread at any time and is able to connect on one thread, to use the connection on another thread, and to disconnect on a third thread.
When using connection pooling, the application must not execute SQL statements that change the database or the context of the database, such as changing the <database name>, that changes the catalog used by a data source. A driver must also implement connection pooling efficiently or it will impair the connection pooling performance.
Connection pooling feature is enabled via the ODBC Data Source Administrator under the 'Connection Pooling' tab.

 
Does Strategy Intelligence Server use 'Connection Pooling' feature of the ODBC driver manager?
Strategy Intelligence Server does not use Connection Pooling feature described above. There are a number of reasons behind this decision:
 

• Strategy Intelligence Server has its own 'Connection Pooling' (called 'Connection Caching' and explained in KB5598 ), that is specifically designed and optimized for its needs.
• Strategy Intelligence Server supports many database types (DB2, Informix, Red Brick, Teradata, Oracle, Sybase, SQL Server), for which the ODBC drivers are quite different from one another. To leverage these differences, Strategy Intelligence Server, for instance, employs 'Multi-process Mode', that enables to spawn separate processes whose only job is to talk to the database outside of the MSTRSVR process.
   
  

• Each process in Multi-process mode handles only one connection, thereby eliminating the issues of thread safety that may arise from an ODBC driver. Each process channels all calls, from multiple threads, to one single thread to talk to the database, thereby addressing thread affinity.
• In addition, Strategy Intelligence Server allows executing SQL that may change the database, in which case it is also possible to turn off the connection caching.

 
In summary, Strategy Intelligence Server connection caching (pooling) is quite versatile in handling the thread-safety and thread affinity constraint of ODBC Connection Pooling via its ability to work in Multi-process mode (in addition to the Multithreaded mode), without posing any constraints on the nature of the SQL, while offering the benefits of the ODBC Connection Pooling.
 
NOTE: Refer to ODBC Connection Pooling in the book 'ODBC Programmer's Reference' for the discussions about connection pooling.
 
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.