Topic
  • 5 replies
  • Latest Post - ‏2010-11-09T07:53:19Z by neerajsathe
neerajsathe
neerajsathe
7 Posts

Pinned topic DB2 ODBC timeouts not working when used with UnixODBC

‏2010-11-03T10:30:20Z |
I am writing an application for fetching data from DB2 tables, I have opted for ODBC over CLI because I need to handle CONNECTION & SQL TIMEOUTS. In my initial investigation I have found out that the timeouts are not working properly.

The UnixODBC DSN for DB2 is configured correctly and tested with UnixODBC isql command line utility. I am using following code to test DB2 LOGIN & CONNECTION TIMEOUTS.

Code Snippet



/* odbc.c testing unixODBC */ #include <stdlib.h> #include <stdio.h> #include <sql.h> #include <sqlext.h> #include <sqltypes.h>   SQLHENV                  V_OD_Env;     
// Handle ODBC environment 

long                     V_OD_erg;     
// result of functions SQLHDBC                  V_OD_hdbc;    
// Handle connection   

char                     V_OD_stat[10]; 
// Status SQL SQLINTEGER               V_OD_err,V_OD_rowanz,V_OD_id; SQLSMALLINT              V_OD_mlen; 

char                                   V_OD_msg[200],V_OD_buffer[200];     

int main(

int argc,

char *argv[]) 
{ 

int i = 1; SQLCHAR outstr[1024]; SQLSMALLINT outstrlen;   
// 1. allocate Environment handle and register version V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ printf(
"Error AllocHandle\n"); exit(0); 
} V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ printf(
"Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
} 
// 2. allocate connection handle, set timeout V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ printf(
"Error AllocHDB %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
}   V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER *)1, 0); 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
"Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
}   V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)i, 0); 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
"Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
}     
// 3. Connect to the datasource "web" V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) 
"dbnode2", SQL_NTS, (SQLCHAR*) 
"db2node2", SQL_NTS, (SQLCHAR*) 
"redhat", SQL_NTS); 
/*V_OD_erg = SQLDriverConnect(V_OD_hdbc,NULL,"DSN=dbnode2;UID=db2node2;PWD=redhat",SQL_NTS,outstr,sizeof(outstr), &outstrlen,SQL_DRIVER_COMPLETE);*/ 

if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
{ printf(
"Error SQLConnect %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
"%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
} printf(
"Connected !\n");   SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);     SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); 

return 0; 
}


According to the replies I have got from UnixODBC contributors, the UnixODBC driver only forwards DB2 calls to the underlying DB2 driver (libdb2.so).

I am not very sure if I have missed to set an important attribute here. I would really appreciate a quick reply as this has become a showstopper for the application.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 ODBC timeouts not working when used with UnixODBC

    ‏2010-11-03T12:40:18Z  
    Hi,

    You can check these attributes and see if it works for you.

    SQL_ATTR_LOGIN_TIMEOUT
    SQL_ATTR_CONNECTION_TIMEOUT
    SQL_ATTR_RECEIVE_TIMEOUT
    Check out this link for more details.
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0006816.html
    Thanks
    Vineet
  • neerajsathe
    neerajsathe
    7 Posts

    Re: DB2 ODBC timeouts not working when used with UnixODBC

    ‏2010-11-04T05:43:29Z  
    Hi,

    You can check these attributes and see if it works for you.

    SQL_ATTR_LOGIN_TIMEOUT
    SQL_ATTR_CONNECTION_TIMEOUT
    SQL_ATTR_RECEIVE_TIMEOUT
    Check out this link for more details.
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0006816.html
    Thanks
    Vineet
    Hi Vineet,

    I have already tried the attributes you have mentioned, but unfortunately it did not work out. Can you please give it a try and let me know.

    Thanks,
    Neeraj
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 ODBC timeouts not working when used with UnixODBC

    ‏2010-11-04T12:25:42Z  
    Hi Vineet,

    I have already tried the attributes you have mentioned, but unfortunately it did not work out. Can you please give it a try and let me know.

    Thanks,
    Neeraj
    Hi,

    Could you please try SQLGetConnectAttr() to return the current setting of a connection attribute.

    Thanks
  • neerajsathe
    neerajsathe
    7 Posts

    Re: DB2 ODBC timeouts not working when used with UnixODBC

    ‏2010-11-09T07:03:11Z  
    Hi,

    Could you please try SQLGetConnectAttr() to return the current setting of a connection attribute.

    Thanks
    I have tried using SQLGetConnectAttr, I have to correct the code little bit to make it work. Below is the corrected code and the program output

    
    
    /* odbc.c testing unixODBC */ #include <stdlib.h> #include <stdio.h> #include <sql.h> #include <sqlext.h> #include <sqltypes.h>   SQLHENV                     V_OD_Env;     
    // Handle ODBC environment 
    
    long                   V_OD_erg;     
    // result of functions SQLHDBC                     V_OD_hdbc;    
    // Handle connection   
    
    char                  V_OD_stat[10]; 
    // Status SQL SQLINTEGER          V_OD_err,V_OD_rowanz,V_OD_id; SQLSMALLINT               V_OD_mlen; 
    
    char             V_OD_msg[200],V_OD_buffer[200];     
    
    int main(
    
    int argc,
    
    char *argv[]) 
    { 
    
    int l_tout = 5; 
    
    int c_tout = 10; SQLCHAR outstr[1024]; SQLSMALLINT outstrlen;   
    // 1. allocate Environment handle and register version  V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { printf(
    "Error AllocHandle\n"); exit(0); 
    } V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { printf(
    "Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
    } 
    // 2. allocate connection handle, set timeout V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { printf(
    "Error AllocHDB %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
    } V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER*)l_tout, 0); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
    "Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
    } V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER*)c_tout, 0); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
    "Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
    } V_OD_erg = SQLGetConnectAttr(V_OD_hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER*)&l_tout, 0, 0); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
    "Error Getting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
    } V_OD_erg = SQLGetConnectAttr(V_OD_hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER*)&c_tout, 0, 0); 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
    "Error Getting SQL_ATTR_CONNECTION_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); 
    } printf(
    "LOGIN TIMEOUT=%d\nCONNECTION TIMEOUT=%d\n",l_tout,c_tout);   
    // 3. Connect to the datasource "web"  V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) 
    "dbnode1", SQL_NTS, (SQLCHAR*) 
    "db2node1", SQL_NTS, (SQLCHAR*) 
    "redhat", SQL_NTS); 
    /*V_OD_erg = SQLDriverConnect(V_OD_hdbc,NULL,"DSN=dbnode2;UID=db2node2;PWD=redhat",SQL_NTS,outstr,sizeof(outstr), &outstrlen,SQL_DRIVER_COMPLETE);*/ 
    
    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) 
    { printf(
    "Error SQLConnect %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf(
    "%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); 
    } printf(
    "Connected !\n");   SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); 
    
    return 0; 
    }   OUTPUT LOGIN TIMEOUT=5 CONNECTION TIMEOUT=10
    
  • neerajsathe
    neerajsathe
    7 Posts

    Re: DB2 ODBC timeouts not working when used with UnixODBC

    ‏2010-11-09T07:53:19Z  
    I have tried using SQLGetConnectAttr, I have to correct the code little bit to make it work. Below is the corrected code and the program output

    <pre class="jive-pre"> /* odbc.c testing unixODBC */ #include <stdlib.h> #include <stdio.h> #include <sql.h> #include <sqlext.h> #include <sqltypes.h> SQLHENV V_OD_Env; // Handle ODBC environment long V_OD_erg; // result of functions SQLHDBC V_OD_hdbc; // Handle connection char V_OD_stat[10]; // Status SQL SQLINTEGER V_OD_err,V_OD_rowanz,V_OD_id; SQLSMALLINT V_OD_mlen; char V_OD_msg[200],V_OD_buffer[200]; int main( int argc, char *argv[]) { int l_tout = 5; int c_tout = 10; SQLCHAR outstr[1024]; SQLSMALLINT outstrlen; // 1. allocate Environment handle and register version V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf( "Error AllocHandle\n"); exit(0); } V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf( "Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } // 2. allocate connection handle, set timeout V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf( "Error AllocHDB %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER*)l_tout, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf( "Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); } V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER*)c_tout, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf( "Error Setting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); } V_OD_erg = SQLGetConnectAttr(V_OD_hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER*)&l_tout, 0, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf( "Error Getting SQL_LOGIN_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); } V_OD_erg = SQLGetConnectAttr(V_OD_hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER*)&c_tout, 0, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf( "Error Getting SQL_ATTR_CONNECTION_TIMEOUT %s (%d)\n",V_OD_msg,V_OD_err); } printf( "LOGIN TIMEOUT=%d\nCONNECTION TIMEOUT=%d\n",l_tout,c_tout); // 3. Connect to the datasource "web" V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "dbnode1", SQL_NTS, (SQLCHAR*) "db2node1", SQL_NTS, (SQLCHAR*) "redhat", SQL_NTS); /*V_OD_erg = SQLDriverConnect(V_OD_hdbc,NULL,"DSN=dbnode2;UID=db2node2;PWD=redhat",SQL_NTS,outstr,sizeof(outstr), &outstrlen,SQL_DRIVER_COMPLETE);*/ if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf( "Error SQLConnect %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf( "%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf( "Connected !\n"); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); return 0; } OUTPUT LOGIN TIMEOUT=5 CONNECTION TIMEOUT=10 </pre>
    The connection attributes are getting set properly as the SQLGet* call is returning proper attribute value as shown in the program output. The main problem still remains as it is i.e. the timeouts are not occurring at the set time spec.