ODBC CLI Connection Pooling
ODBC connection pooling is a method of sharing active database connections with similar or identical connection characteristics. When a connection is released through a call to SQLDisconnect(), the connection is left open and added to a pool of active connections.
When an ODBC application opens a new connection, the application searches the pool for a connection with matching connection characteristics. If a match is found, the connection from the pool is used "under the covers" instead of creating a new connection. If a match is not found, a new connection is opened.
ODBC connection pooling can improve performance significantly because an ODBC application can take a long time to connect relative to the time it takes to process data.
ODBC pooled connections can be shared with single or multi-threaded ODBC applications, but are not shared between separate ODBC applications.
Note: In Windows environments, connection pooling is provided by the Windows Driver Manager rather than the Ingres ODBC CLI.
ODBC Connection Pools: Per Driver and Per Environment
ODBC connection pooling is activated by invoking SQLSetEnvAttr() with the attribute SQL_ATTR_CONNECTION_POOLING, and with the directives SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV.
Example: SQL_ATTR_CONNECTION_POOLING Attribute
rc = SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,")
(SQLPOINTER)SQL_CP_ONE_PER_DRIVER, SQL_IS_INTEGER);
SQL_CP_ONE_PER_DRIVER means that there is only one connection pool for the entire ODBC application, regardless of the number of connections. If only one environment handle is allocated, SQL_CP_ONE_PER_DRIVER is essentially the same as SQL_CP_ONE_PER_HENV.
If multiple environment handles are allocated, it may make more sense to specify SQL_CP_ONE_PER_HENV, especially if the connections associated with each environment have similar characteristics. This directive will create multiple pools, each with a smaller number of connections to search through.
ODBC Connection Pool Match Criteria: Strict and Relaxed
The SQLSetEnvAttr() function allows the ODBC application to specify match criteria for objects in the connection pool.
Connection objects with "strict" criteria must have identical connection specifiers for connection attributes specified in the connection string from SQLDriverConnect(). "Strict" is the only option supported, and other specifications are ignored.
Example: Strict Match Criterion
rc = SQLSetEnvAttr(henv, SQL_ATTR_CP_MATCH, (SQLPOINTER)
SQL_CP_STRICT_MATCH, SQL_IS_INTEGER);
"Relaxed" match criteria do not apply to SQLDriverConnect() in the Ingres ODBC CLI. If a relaxed match criterion is specified, only key connection attributes must match. For the ODBC CLI, the following minimum attributes must match, if specified:
• DATABASE
• SERVER_TYPE
• SERVER
• DRIVER
• GROUP
• ROLENAME
• ROLEPWD
• DBMS_PWD
The following connection string attributes are ignored for relaxed match criteria:
• BLANKDATE
• DATE1582
• CATCONNECT
• SELECTLOOPS
• NUMERIC_OVERFLOW
• CATSCHEMANULL
ODBC Connection Pool Timeout
ODBC pooled connections can be configured to time out to prevent an unmanageable number of connections in the pool.
By default, the Ingres ODBC CLI allows connections to remain in the pool as long as the ODBC application is active. You can override the default connection pool timeout value using the Ingres ODBC Administrator (iiodbcadmin) on Linux.
To change the connection pool timeout value on Linux
1. Start the Ingres ODBC Administrator by issuing the iiodbcadmin command.
2. At the utility menu, choose Drivers, Configuration Options.
The Configuration screen appears.
3. Enter a value in the edit box from 1 to 2,147,483,647, which represents the number of seconds that unused connections remain in the pool.
The default value is -1, which means that pooled connection objects never time out. When a connection object times out, it is disconnected from the database and deleted from the pool.
Note: A thread in the ODBC CLI manages connection pool timeouts. If the time out value is left at -1, the thread is never started. For performance reasons, it is better to leave the timeout value at -1 rather than specifying a large value.