Connection pooling

OneDB ODBC driver also supports Connection Pooling capabilities. One may decide to use either Connection Pooling capabilities provided by ODBC Driver Manager or newly added capabilities in OneDB ODBC Driver

The main advantage of connection pooling will be for the applications (including open source drivers) directly using the OneDB ODBC driver (not via ODBC Driver Manager) and wants to optimize the connection resource in highly OLTP nature of applications where number of connections opened and closed are in large numbers.

Note: Driver Manager and Driver's connection pooling feature cannot be used together. Application should use only one of the features.
You can find the header file infxcli.h,which is located under $INFORMIXDIR/incl/cli directory. The file has the following additional macros:
#define SQL_INFX_ATTR_CP_TIMEOUT                   2292
#define SQL_INFX_ATTR_CONNECTION_POOLING           2293
#define SQL_INFX_ATTR_CP_MATCH                     2294
#define SQL_INFX_ATTR_CP_TOTAL_CONNECTIONS         2295
#define SQL_INFX_ATTR_CP_TOTAL_ACTIVE              2296
#define SQL_INFX_ATTR_CP_TOTAL_IDLE                2297

/* Connection pooling value parameters */
#define SQL_INFX_CP_STRICT_MATCH     1
#define SQL_INFX_CP_RELAXED_MATCH    2
#define SQL_INFX_CP_OFF              1
#define SQL_INFX_CP_ON               2

Enabling/Disabling connection pooling

There are two ways to enable/disable the connection pooling in ODBC:
  • On Unix/Linux, user could use following attributes in odbc.ini file:
    • Set 0 to disable, 1 to enable. By default its disabled(0).
      InformixPooling=0
    • Timeout for the connection in seconds range 5 to 60000. Default 60. Beyond range will be reset to default value without any error/warning.
      InformixCPTimeout=10
    • Set 0 for strict match and 1 for relaxed match. By default, it is strict match(0). In strict match, more number of parameters are compared to find out the match from the available connection from the pool. It is recommended to use “strict match?.
      InformixCPMatch=0
  • On Unix/Linux or Windows, user can also set/get above values programatically using SQLSetEnvAttr/SQLGetEnvAttr APIs.

By default, its connection pooling is disabled, "InformixPooling=0" or SQL_INFX_ATTR_CONNECTION_POOLING set to SQL_INFX_CP_OFF.

Note: APIs used in program will have higher precendence over odbc.ini. For example: In odbc.ini file, pooling is disabled but in the application using SQLSetEnvAttr(SQL_INFX_ATTR_CONNECTION_POOLING), if user enables the pooling, then application will take higher precedence. Similarly other way around. Also for Timeout and Match parameters.

The connection timing will be guided by Timeout parameter value (5 to 60000 seconds) set in "InformixCPTimeout" parameter of odbc.ini file and/or SQLSetEnvAttr(SQL_INFX_ATTR_CP_TIMEOUT) value in the program. However this value is not guaranteed to be exact. Whenever the user calls SQLDisonnect() at that point of time, all the "not in use" connection will be iterated and whichever connections timeout has elapsed (difference between time of disconnect to current time), if this difference time is more than the timeout set by the user then such connections will be physically disconnected and all resources will be freed.

If connection pooling is enabled in odbc.ini, then it will be across all applications of ODBC. However, one can always change(enable/disable) at each ODBC Environment (SQLAllocHanle(ENV)) scope. For example:If it is enabled in the odbc.ini file and application has two Environments (SQLAllocHandle(ENV1 and ENV2)), then user can disable in one of the Environments by using SQLSetEnvAttr(SQL_INFX_ATTR_CONNECTION_POOLING = DISABLED), the other Environment will be enabled from the effect of odbc.ini file. Hence, pooling object is managed at each Environment level.

The simple parameter comparisons across parameters (STRICT vs RELAX mode) is used to decide the pooling strategy. Depending on the mode used, all parameters must exactly match in order to hand over the available connection to the newly asked connection request.

You can use SQLGetEnvAttr (SQL_INFX_ATTR_CONNECTION_POOLING / SQL_INFX_ATTR_CP_TIMEOUT / SQL_INFX_ATTR_CP_MATCH) to list the values being set and if pooling is not enabled, you will get default parameters values or values (MODE and TIMEOUT) mentioned in the odbc.ini file.

The default value of SQL_INFX_ATTR_CP_TIMEOUT is 60 seconds, range is 5 to 60000 seconds, beyond this range, it will be reset to 60 seconds.

To track the number of connections using ODBC API, use SQLGetEnvAttr() API with below parameters:
SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_CONNECTIONS) => This will return total number of connections. 
                                                     At any point of time, this will be equal to "Active + Idle" number of connections.
SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_ACTIVE) => This will return connection count which are currently in use.
SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_IDLE) => This will return connection count which are currently idle (not in use)and
                                              ready to be assigned for matching incoming connection requests.The idle number of connections
                                              are actually connected to the database server.
Note: If connection pooling is not enabled and calling these APIs would return default/unknown value -1 for each of the parameters.

Sample Output

Total connections = -1, active = -1, idle = -1
*** Connection pooling enabled ***
*** Connection pooling set to STRICT mode ***
*** Connection pooling timeout set to 10 seconds
Connected, label = connStrIn
Total connections = 1, active = 1, idle = 0
Disconnected, label = connStrIn
Total connections = 1, active = 0, idle = 1
Connected, label = connStrIn
Total connections = 1, active = 1, idle = 0
Disconnected, label = connStrIn
Total connections = 1, active = 0, idle = 1
Connected, label = connStrIn1
Total connections = 2, active = 1, idle = 1
Connected, label = connStrIn
Total connections = 2, active = 2, idle = 0
Disconnected, label = connStrIn
Total connections = 2, active = 1, idle = 1
Connected, label = connStrIn1
Total connections = 3, active = 2, idle = 1
Disconnected, label = connStrIn1
Total connections = 3, active = 1, idle = 2
Press enter to Exit, you may run 'userid informix onstat -g ses' to 
see number of connections still opened due to connection pooling effect :

Statement handle freed successfully
Disconnected, label = connStrIn1
Total connections = 3, active = 0, idle = 3
Environment handle freed successfully
Press 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 :
sh-3.2$


/***************************************************************************
*       Licensed Materials - Property of HCL Technologies
*
*       "Restricted Materials of HCL"
*
*       HCL OneDB ODBC Application
*
*       Copyright HCL 2019 All rights reserved.
*
*  Title:          ConnectionPooling.c
*
*  Description:    Connection Pooling Sample ODBC Program
*
*  Author  : User 1
*
* Compile/link options on Linux/Unix :
* gcc -g -c -fsigned-char -DNO_WIN32 -O -I$INFORMIXDIR/incl/cli ConnectionPooling.c
* gcc -g -o ConnectionPooling ConnectionPooling.o -L$INFORMIXDIR/lib/cli -L$INFORMIXDIR/lib/esql -lthcli -lifdmr -lifgls -lifglx -lm -lnsl
***************************************************************************
*/

#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
#define NUM_OF_INSTANCE    2

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;
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_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("\nTotal connections = %d, active = %d, idle = %d",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;UID=informix;PWD=xxxxx");
   sprintf((char *) connStrIn, "DSN=SmartTrigger");
   sprintf((char *) connStrIn1, "DSN=odbc_demo");
#else
   //sprintf((char *) connStrIn, "DRIVER={IBM INFORMIX ODBC DRIVER};HOST=x.x.x.x;SERVER=ol_informix1210_1;
    SERVICE=20195;PROTOCOL=onsoctcp;DATABASE=sysadmin;UID=informix;PWD=xxxxx");
   sprintf((char *) connStrIn, "DSN=SmartTrigger");
   sprintf((char *)connStrIn1, "DSN=odbc_demo");
#endif
   return;
}

int Connect(SQLHDBC *hdbc, SQLCHAR connStrIn[], SQLCHAR label[])
{
    SQLRETURN rc = SQL_SUCCESS;
    SQLHDBC tmpHdbc = NULL;
    /* Allocate the connection handle */
    rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &tmpHdbc);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 2 -- Connection Handle Allocation failed\nExiting!!"))
        exit (-1);

    //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(main) in Step 3 -- SQLDriverConnect failed\nExiting!!"))
        exit (-1);
    printf("\nConnected, label = %s", label);
    //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, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc0, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc1, connStrIn, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc1, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc2, connStrIn1, "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, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc3, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc4, connStrIn1, "connStrIn1");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc4, "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, "connStrIn1");
    GetConnectionPoolingAttributes();

    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);
}