Tuning the Connection Pool Manager
- MinPoolSize: The purpose of MinPoolSize is to open as many number of connections during first
request of connection.
For example, if MinPoolSize=5, this will open total 5 connections when the first connection request is made. In case, the connection count goes down due to connection Pool Time Out expiry, and if the same connection request comes again and there is no matching idle connection available, it will open MinPoolSize number of connections again.
- MaxConnLimit: The purpose of MaxConnLimit/MaxConnectionLimit is to restrict number of
connections to the value set by this parameter.
For example, if MaxConnLimit=20, before opening new connection, this value will be checked, if it exceeds the opened number of connections, then error will be reported. MaxConnLimit will ensure, applications higher limit to open physical connections to the database is restricted.
You can use the following methods to set MinPoolSize and MaxConnLimit parameters in ODBC applications:
- Use SQLSetEnvAttr/SQLSetConnectAttr( SQL_INFX_ATTR_MAX_CONN_LIMIT ).
This parameter allows you to specify the maximum number of simultaneous physical connections that the DataSource object can have with the server.
The range for SQL_INFX_ATTR_MAX_CONN_LIMIT is 5 to 2000. If you try to set beyond these values, it will be reset to 5 without any error or warning.
- On Unix, use odbc.ini file.
- In application, use connection string : "DSN=MyDSN; MaxConnLimit=20".
- Use SQLSetConnectAttr( SQL_INFX_ATTR_MIN_CONN_POOL_SIZE).
This parameter allows you to specify the minimum number of connections to maintain in the pool.
The range for SQL_INFX_ATTR_MAX_CONN_LIMIT is 2 to 1000. If you try to set beyond these values, it will be reset to 2 without any error or warning.
- On Unix, use odbc.ini file.
- In application, use connection string : "DSN=MyDSN; MinPoolSize=10".
Sample code for MinPoolSize and MaxConnLimit usage
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#ifndef NO_WIN32
#include <io.h>
#include <windows.h>
#include <conio.h>
#endif /*NO_WIN32*/
#define __REENTRANT
#include <signal.h>
#ifdef NO_WIN32
#include <sys/wait.h>
#include <pthread.h>
#endif
#include <time.h>
#include <infxcli.h>
#define ERRMSG_LEN 200
#define NAMELEN 300
SQLHDBC hdbc0;
SQLHDBC hdbc1;
SQLHDBC hdbc2;
SQLHDBC hdbc3;
SQLHDBC hdbc4;
SQLHENV henv;
SQLHSTMT hstmt;
SQLCHAR connStrIn[NAMELEN];
SQLCHAR connStrIn1[NAMELEN];
short totalConn=0, totalActive=0, totalIdle=0, mxConnPoolSize=0;
SQLCHAR connStrOut[NAMELEN];
SQLSMALLINT connStrOutLen;
SQLINTEGER checkError (SQLRETURN rc,
SQLSMALLINT handleType,
SQLHANDLE handle,
SQLCHAR* errmsg)
{
SQLRETURN retcode = SQL_SUCCESS;
SQLSMALLINT errNum = 1;
SQLCHAR sqlState[6];
SQLINTEGER nativeError;
SQLCHAR errMsg[ERRMSG_LEN];
SQLSMALLINT textLengthPtr;
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
{
while (retcode != SQL_NO_DATA)
{
retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState, &nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);
if (retcode == SQL_INVALID_HANDLE)
{
fprintf (stderr, "CheckError function was called with an invalid handle!!\n");
return 1;
}
if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
fprintf (stderr, "ERROR: %d: %s : %s \n", nativeError, sqlState, errMsg);
errNum++;
}
fprintf (stderr, "%s\n", errmsg);
return 1; /* all errors on this handle have been reported */
}
else
return 0; /* no errors to report */
}
int GetConnectionPoolingAttributes()
{
SQLRETURN rc = SQL_SUCCESS;
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_MAX_CONN_LIMIT , (void *) &mxConnPoolSize, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling Total Connections failed \nExiting!!");
exit (-1);
}
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_CONNECTIONS , (void *) &totalConn, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling Total Connections failed \nExiting!!");
exit (-1);
}
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_ACTIVE , (void *) &totalActive, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling Total Active failed \nExiting!!");
exit (-1);
}
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_IDLE , (void *) &totalIdle, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling Total Idle failed \nExiting!!");
exit (-1);
}
printf("\nMaxConnLimit = %d, Total = %d, active = %d, idle = %d",mxConnPoolSize, totalConn,totalActive, totalIdle );
return 0;
}
void SetConnectionString()
{
memset(connStrIn, 0, sizeof(connStrIn));
memset(connStrIn1, 0, sizeof(connStrIn1));
#ifdef NO_WIN32
//sprintf((char *) connStrIn, "DRIVER={IBM INFORMIX ODBC DRIVER};HOST=x.x.x.x;SERVER=ol_informix1210_2;SERVICE=8573;PROTOCOL=onsoctcp;DATABASE=sysadmin;MinPoolSize=8;MaxConnLimit=9;UID=informix;PWD=xxxx");
//sprintf((char *) connStrIn, "DSN=SmartTrigger");
//sprintf((char *) connStrIn1, "DSN=odbc_demo");
sprintf((char *) connStrIn, "DSN=SmartTrigger;MinPoolSize=4;MaxConnLimit=9");
sprintf((char *) connStrIn1, "DSN=odbc_demo;MinPoolSize=4;MaxConnLimit=9");
#else
//sprintf((char *) connStrIn, "DRIVER={IBM INFORMIX ODBC DRIVER};HOST=x.x.x.x;SERVER=ol_informix1210_2;SERVICE=8573;PROTOCOL=onsoctcp;DATABASE=sysadmin;MinPoolSize=8;MaxConnLimit=9;UID=informix;PWD=xxxx");
//sprintf((char *) connStrIn, "DSN=SmartTrigger");
//sprintf((char *)connStrIn1, "DSN=odbc_demo");
sprintf((char *) connStrIn, "DSN=SmartTrigger;MinPoolSize=4;MaxConnLimit=9");
sprintf((char *)connStrIn1, "DSN=odbc_demo;MinPoolSize=4;MaxConnLimit=9");
#endif
return;
}
int Connect(SQLHDBC *hdbc, SQLCHAR connStrIn[], SQLCHAR label[])
{
SQLRETURN rc = SQL_SUCCESS;
SQLHDBC tmpHdbc = NULL;
unsigned int *setPoolSize=100;
unsigned int getPoolSize=0;
unsigned int getMinPoolSize=0;
/* Allocate the connection handle */
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &tmpHdbc);
if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(Connect) in Step 1 -- Connection Handle Allocation failed\nExiting!!"))
exit (-1);
//*setPoolSize = 100;
//rc = SQLSetConnectAttr(tmpHdbc, SQL_INFX_ATTR_MAX_CONN_LIMIT, (unsigned int *)&setPoolSize, 2);
//rc = SQLSetConnectAttr(tmpHdbc, SQL_INFX_ATTR_MAX_CONN_LIMIT, (unsigned int *)setPoolSize, 2);
rc = SQLSetEnvAttr(henv, SQL_INFX_ATTR_MAX_CONN_LIMIT, (unsigned int *)setPoolSize, SQL_IS_UINTEGER);
if (checkError (rc, SQL_HANDLE_DBC, tmpHdbc, (SQLCHAR *) "Error(Connect) in Step 2 -- SQLSetConnectAttr failed\nExiting!!"))
exit (-1);
//printf("\nSet Max Pool Size using SQLSetConnectAttr() call = %d", setPoolSize);
//printf("\nConnection handle BEFORE connection = %p", tmpHdbc);
/* Establish the database connection */
rc = SQLDriverConnect (tmpHdbc, NULL, connStrIn, SQL_NTS, connStrOut, NAMELEN, &connStrOutLen, SQL_DRIVER_NOPROMPT);
if (checkError (rc, SQL_HANDLE_DBC, tmpHdbc, (SQLCHAR *) "Error(Connect) in Step 3 -- SQLDriverConnect failed\nExiting!!"))
exit (-1);
printf("\nConnected, label = %s", label);
rc = SQLGetConnectAttr(tmpHdbc, SQL_INFX_ATTR_MAX_CONN_LIMIT, (void *)&getPoolSize, 4, NULL);
if (checkError (rc, SQL_HANDLE_DBC, tmpHdbc, (SQLCHAR *) "Error(Connect) in Step 2 -- SQLGetConnectAttr(MAX) failed\nExiting!!"))
exit (-1);
//printf("\nGot Max Pool Size using SQLGetConnectAttr() call = %d", getPoolSize);
rc = SQLGetConnectAttr(tmpHdbc, SQL_INFX_ATTR_MIN_CONN_POOL_SIZE, (void *)&getMinPoolSize, 4, NULL);
if (checkError (rc, SQL_HANDLE_DBC, tmpHdbc, (SQLCHAR *) "Error(Connect) in Step 2 -- SQLGetConnectAttr(MIN) failed\nExiting!!"))
exit (-1);
//printf("\nGot Min Pool Size using SQLGetConnectAttr() call = %d", getMinPoolSize);
//printf("\nConnection handle AFTER connection = %p", tmpHdbc);
*hdbc = tmpHdbc;
return rc;
}
int DisconnectAndFree(SQLHDBC *hdbc, SQLCHAR label[])
{
SQLRETURN rc = SQL_SUCCESS;
SQLHDBC tmphdbc = *hdbc;
/* Disconnect from the data source */
rc = SQLDisconnect (tmphdbc);
printf("\nDisconnected, label = %s", label);
/* Free the environment handle and the database connection handle */
rc = SQLFreeHandle (SQL_HANDLE_DBC, tmphdbc);
//printf("\nDatabase handle freed successfully");
return rc;
}
int main (long argc,
char* argv[])
{
/* Miscellaneous variables */
SQLRETURN rc = 0;
BOOL poolEnabled = 0;
BOOL cpMode = 0;
SQLINTEGER timeOut = -1;
/* Allocate the Environment handle */
rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
exit (-1);
}
/* Set the ODBC version to 3.0 */
rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
exit (-1);
GetConnectionPoolingAttributes();
//rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (SQLPOINTER) SQL_INFX_CP_OFF, 0);
rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (SQLPOINTER) SQL_INFX_CP_ON, 0);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling call failed \nExiting!!");
exit (-1);
}
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (void *) &poolEnabled, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling call failed \nExiting!!");
exit (-1);
}
if(SQL_INFX_CP_OFF == poolEnabled)
printf("\n*** Connection pooling disabled ***");
else if(SQL_INFX_CP_ON == poolEnabled)
printf("\n*** Connection pooling enabled ***");
else
printf("\n*** What's going with Connection pooling!!!");
/*
rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH, (SQLPOINTER)SQL_INFX_CP_RELAXED_MATCH, 0);
//rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH, (SQLPOINTER)SQL_INFX_CP_STRICT_MATCH, 0);
if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
exit (-1);
*/
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH , (void *) &cpMode, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling call failed \nExiting!!");
exit (-1);
}
if(SQL_INFX_CP_RELAXED_MATCH == cpMode)
printf("\n*** Connection pooling set to RELAX mode ***");
else if(SQL_INFX_CP_STRICT_MATCH == cpMode)
printf("\n*** Connection pooling set to STRICT mode ***");
rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_TIMEOUT, (SQLPOINTER)5, 0);
if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
exit (-1);
rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TIMEOUT , (void *) &timeOut, SQL_NTS, NULL);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Connection pooling call failed \nExiting!!");
exit (-1);
}
printf("\n*** Connection pooling timeout set to %d seconds", timeOut);
SetConnectionString();
Connect(&hdbc0, connStrIn, "1. connStrIn");
GetConnectionPoolingAttributes();
DisconnectAndFree(&hdbc0, "1. connStrIn");
GetConnectionPoolingAttributes();
SetConnectionString();
Connect(&hdbc1, connStrIn, "2. connStrIn");
GetConnectionPoolingAttributes();
DisconnectAndFree(&hdbc1, "2. connStrIn");
GetConnectionPoolingAttributes();
SetConnectionString();
Connect(&hdbc2, connStrIn1, "3. connStrIn1");
GetConnectionPoolingAttributes();
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc2, &hstmt );
if (checkError (rc, SQL_HANDLE_DBC, hdbc2, (SQLCHAR *) "Error(main) in Step 4 -- Statement Handle Allocation failed\nExiting!!"))
exit (-1);
SetConnectionString();
Connect(&hdbc3, connStrIn, "4. connStrIn");
GetConnectionPoolingAttributes();
fflush(stdout);
DisconnectAndFree(&hdbc3, "4. connStrIn");
GetConnectionPoolingAttributes();
SetConnectionString();
Connect(&hdbc4, connStrIn1, "5. connStrIn1");
GetConnectionPoolingAttributes();
DisconnectAndFree(&hdbc4, "5. connStrIn1");
GetConnectionPoolingAttributes();
printf("\nPress enter to Exit, you may run 'userid informix onstat -g ses' to see number of connections still opened due to connection pooling effect : ");
char c = getchar();
Exit:
/* Close the statement handle */
SQLFreeStmt (hstmt, SQL_CLOSE);
/* Free the statement handle */
SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
printf("\nStatement handle freed successfully");
DisconnectAndFree(&hdbc2, "3. connStrIn1");
GetConnectionPoolingAttributes();
printf("\nBefore SQLFreeHandle(HENV)");
SQLFreeHandle (SQL_HANDLE_ENV, henv);
printf("\nEnvironment handle freed successfully");
printf("\nPress enter to Exit, you may run 'userid informix onstat -g ses' to see there should be no connection, all connections must have closed by now : ");
c = getchar();
return (rc);
}