Tuning the Connection Pool Manager

Starting CSDK 4.50.xC4 onwards, Informix ODBC driver supports new Connection Pool Manager properties:

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:

Setting up MaxConnLimit:

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".

Setting up MinPoolSize

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".

Note: SQL_INFX_ATTR_MIN_CONN_POOL_SIZE and SQL_INFX_ATTR_MAX_CONN_LIMIT definition is located in $INFORMIXDIR/incl/cli/infxcli.h file.

Note: These parameters are used only when Pooling is enabled. As there is no default value, you need to specify the value in your application.

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={HCL 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={HCL 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);
}