Normally, access to an IBM® Informix® database can take from less than a second up to some expected period of time, depending on the database operation. And sometimes it can take much longer than you expected due to many reasons, such as network speed, system performance and system load. In the worst-case scenario, the Informix client may be blocked forever for the expected server response that never comes. This article explains how to interrupt an SQL or connection request requiring more time than expected in order to improve the performance of your Informix database application.

Yunming Wang (yunming@us.ibm.com), Advanced Support Engineer, IBM

Yunming Wang works at IBM as an advanced support engineer on the Informix Advanced Problem Diagnostics (APD) team. He has been working at Informix and IBM since 1998, focusing on Informix and DB2 programming APIs and database connectivity. Areas of specialization include ODBC, JDBC, OLEDB/.Net, ESQL/C, and TCP/IP. Most recently, he has been involved with an IDS virtual appliance project that deals with virtualization and cloud computing technology. Before he joined Informix, he was a software developer. He received his Master degree in Computer Engineering from the University of Arkansas in 1995.



22 March 2012

Also available in Chinese

Introduction

Data accessibility is becoming increasingly mission-critical for many businesses. The Informix server is well known for its fast performance and high degree of reliability. However, accessing the data stored on Informix server from clients also relies on the performance of hardware and operating systems. Informix provides many technologies to guarantee the performance and reliability of data access, such as MACH-11 with Connection Manager, automatic database statistics update, etc. However, this article focuses on how Informix deals with some abnormal scenarios that can occur due to unpredictable random system events that can cause a database operation to take much longer than expected, including slow networking speed, sudden heavy system load, lost TCP packets, or other unforeseen events.

Most of the time, the Informix client communicates with the Informix server using TCP socket in blocking mode, meaning the client will wait until data from the server is available. For a slow connection request, you can interrupt and retry it by setting Informix environment variables INFORMIXCONTIME and INFORMIXCONRETRY to the proper values based on your normal system behaviors.

While the database server executes an SQL request, the application is blocked until the expected response from the database server is available to read. Sometimes you might want to interrupt an SQL request to obtain the application control because you don't want to wait for completion of the current SQL statement. For example, if you inadvertently provide the wrong search criteria for a long query, you want to cancel the SELECT statement, rather than wait for unneeded data. The situation could be worse if it is a multi-tier application. The Informix client, including CSDK and JDBC, allows users to interrupt an SQL request when deeded. The application needs to be designed and implemented to allow users to cancel SQL requests.

If you are running an application from a third-party vendor, it could be difficult to interrupt SQL requests if the application does not have the option. To handle this, Informix introduced a new feature in recent client releases that allows you to enable TCP socket time out in the Informix client so as to interrupt the request without making any changes to the code.

All the sample programs listed in this article are simplified for demonstration purpose only.


Informix connection timeout

Overview

Connecting to an Informix databases usually takes from less than a second to several seconds, depending on the networking speed, but sometimes it may take longer than you expected. The INFORMIXCONTIME and INFORMIXCONRETRY are two client environment variables that can control the connection behavior.

INFORMIXCONTIME

The INFORMIXCONTIME environment variable specifies how many seconds the database connection request continues each attempt to establish a connection to a database server before returning an error. If the INFORMIXCONTIME environment variable is set to 0 or a value less than 0, the database connection request will wait until it succeeds or fails. If it is not set, the default value can vary:

  • ESQL/C: Default value of INFORMIXCONTIME is set to 60 seconds. You can set the INFORMIXCONTIME environment variable in the OS environment for your ESQL/C program with OS commands.
    • UNIX®/Linux®: export or setenv command depending on the shell.
    • Windows®: Set command or Control Panel.
  • ODBC: It's set to 15 seconds by default. Instead, you can use the SQL_ATTR_LOGIN_TIMEOUT connection attribute to specify the connection timeout interval for a connection request. If both INFORMIXCONTIME and SQL_ATTR_LOGIN_TIMEOUT are set, the SQL_ATTR_LOGIN_TIMEOUT connection attribute takes precedence.
  • OLEDB and .NET: Default value is 15 seconds. You can set the INFORMIXCONTIME environment variable for Informix OLEDB provider, but not for an Informix .NET provider that uses the IfxConnection.ConnectionTimeout property for connection timeout.
  • JDBC: Default value is 0, meaning the connection will never time out. You can set the INFORMIXCONTIME environment variable in the JDBC connection URL or the JDBC DataSource connection properties. JDBC 3.70.JC1 introduced another environment variable called LOGINTIMEOUT. You can use it to specify how long, in milliseconds, the server port is polled to establish a connection. If your application does not connect to the Informix database server within the specified time, an error is returned. It can only be set as a DataSource property. Setting it in JDBC connection URL will have no effect.

You can set the INFORMIXCONTIME environment variable to a proper value based on your normal application behaviors.

If the connection request involves searching DBPATH, it's possible to see that the connection request take longer than the INFORMIXCONTIME limit to return an error. If DBPATH contains multiple servers, the INFORMIXCONTIME value is divided among the number of server entries contained in DBPATH. In this case, you should increase the INFORMIXCONTIME value accordingly.

INFORMIXCONRETRY

The INFORMIXCONRETRY environment variable specifies the total number of connection attempts that should be made to each database server by the client during the time limit INFORMIXCONTIME specifies. For example, if INFORMIXCONTIME is set to 30 seconds and INFORMIXCONRETRY is set to 2, the client will attempt to resend the connection request at the 15th second if the initial connection attempt does not go through. It will make another connection attempt at the 30th if needed before aborting.

The default value of the INFORMIXCONRETRY environment variable is 3 prior to CSDK and JDBC 3.70.xC1. The new default value is 1 starting JDBC 3.70.xC1, meaning there will be only one retry after the initial connection attempt.


Informix SQL request interruption in an applications

Overview

The Informix client can interrupt an SQL request in the middle of execution by the Informix server for various reasons, including when the SQL statement is running too long, the data needed at the SQL statement execution time cannot be found, a user has issued a bad query, etc. If you are using Informix ODBC, .NET, or JDBC client, you can cancel the SQL request or let the driver time it out. For ESQL/C applications, you can only design your application to interrupt it by calling Informix library functions.

Regardless of the Informix client APIs you're using, the Informix client handles the SQL statement interruption the same by sending a cancel request to the Informix database server using TCP out_of_band signaling. This means the packet for the cancel request is sent as "urgent or expedited data" in a separate stream. When the Informix server receives the cancel request, it attempts to discontinue the processing of the SQL request. If the server successfully interrupts the SQL request, it returns the error -213 (ISAM error -157) to the client without closing the current connection.

When the application regains control after an interrupted SQL request, any resources allocated to the SQL statement remain allocated. Any open databases, cursors, and transactions remain open. Any system-descriptor areas or SQLDA structures remain allocated. The application program is responsible for the graceful termination of the program. It must release resources and roll back any open transactions. If the application uses auto-commit transaction mode, the default transaction management mode for Informix server, there is no need for the application to handle the open transaction for the SQL statement interrupted.

In case the application terminates without taking care of any open transactions, the Informix server cleans up the uncompleted transactions as abandoned or long transactions eventually and all the data partially modified in the transaction are discarded.

Not every SQL request is interruptible. Some types of database operations are not interruptible, and others cannot be interrupted at certain points. The database server might not immediately terminate execution of an SQL statement, and your application might not regain control as soon as it sends the interrupt request. You can find the list of interruptible SQL statements in the Information Center.

Interrupting an SQL request in a JDBC application

In a JDBC application, a long-running query can be interrupted with the following two JDBC standard methods:

  • Statement.cancel()
  • Statement.QueryTimeout()

The Statement.cancel() can be used by one thread to cancel a statement being executed by another thread. It's mainly used in multi-threaded applications. Typically, a monitor thread can be implemented to check the executing time of an SQL statement on a worker thread. When the SQL statement runs longer than the timeout interval, the monitor thread will call the Statement.cancel() method to interrupt the statement. When the Statement.cancel() method is called, Informix JDBC driver will send the cancel request to the server.

The Statement.setQueryTimeout() method limits the number of seconds the Informix JDBC driver will wait for an SQL statement to execute. If the limit is exceeded, an SQL exception will be thrown. The Informix Statement.setQueryTimeout() method calls the Statement.cancel() method to interrupt the SQL statement.

The listing below is an example of using the Statement.setQueryTimeout() method to time out a query.

Listing 1. Example of calling Statement.setQueryTimeout()
try
{
  String strSQL = "SELECT * FROM mytable";
  Statement stmt = conn.createStatement();

  // setting the query timeout to 5 seconds
  stmt.setQueryTimeout(5);


  // executing the query
  ResultSet rs = stmt.executeQuery(sql);

  rs.close();
  stmt.close();
}
catch(SQLException e)
{
  System.out.println("Query failed!");
  System.out.println("FAILED: " + e.getMessage());
}

Interrupting an SQL request in an ODBC application

In ODBC applications, you can interrupt an SQL request by calling the following two ODBC APIs:

  • SQLCancel()
  • SQLSetStmtAttr() with SQL_ATTR_QUERY_TIMEOUT attribute set to a number of seconds

The ODBC function SQLCancel() can be called to interrupt an SQL request if:

  • It needs input data at the executing time. In this case, SQLCancel() just closes the statement handle without sending anything to the server and returns error code -11010 (cancel treated as FreeStmt/Close).
  • It is executed in threaded mode. When interrupting an SQL request executed by another thread, SQLCancel() sends a cancel request to the server and returns SQL_SUCCESS if the SQL request is being processed by the server. Otherwise, SQLCancel() closes the statement handle and returns error code -11010 (cancel treated as FreeStmt/Close).

When an SQL statement is canceled by SQLCancel(), the application receives the following ODBC error message: [Informix][Informix ODBC Driver][Informix]Statement interrupted by user.

The following example creates two threads: a worker thread, which executes a query; and the other cancels the query if it runs longer than 20 seconds.

Listing 2. The main function
int main()
{
    SQLHENV         henv;
    SQLHDBC         hdbc;
    SQLHSTMT      hstmt;
    SQLCHAR connectOutput[512] = {0};
    SQLSMALLINT result;
    int rc;
    HANDLE hThread[2];
    DWORD dwThreadID[2];

      // Allocate the ODBC environment and save handle.
      rc = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
      if ( (rc != SQL_SUCCESS_WITH_INFO) && (rc != SQL_SUCCESS)) {
         printf("SQLAllocHandle(Env) Failed\n\n");
         return -1;
      }

      // Allocate ODBC connection handle and connect.
      rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
     if (checkError (rc, SQL_HANDLE_ENV, henv, 
         (SQLCHAR *) "Connection Handle Allocation failed\n")) {
         Cleanup();
         return -1;
     }        

     // Connect to Informix
      rc = SQLDriverConnect(hdbc, 
         NULL,
         (UCHAR*) "DSN=ifx1170fc4;",
         (SQLSMALLINT) SQL_NTS
         , connectOutput
         , sizeof(connectOutput)
         , &result
         , SQL_DRIVER_NOPROMPT);
     if (checkError (rc, SQL_HANDLE_DBC, hdbc, 
	(SQLCHAR *) "Connecting to Informix server failed\n")) {
         Cleanup();
         return -1;
      }
      
      /* Allocate the statement handle */
      rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
     if (checkError (rc, SQL_HANDLE_DBC, hdbc, 
	(SQLCHAR *) "Statement Handle Allocation failed\n")) {
         Cleanup();
         return -1;
      }

    hThread[0] = CreateThread( 
          	NULL,               
            	0,                  
            	do_test,      // worker thread function 
            	hstmt,              
            	0,                  
            	&dwThreadID[0]);    
 
    // Check the return value for success. 

    if (hThread[0] == NULL)
	   printf("Unable to create thread!!\n");

    printf("\n The worker thread created!\n");
	
    fprintf (stdout, "\nCreating the cancel thread\n");
    hThread[1] = CreateThread( 
          	NULL,                
            	0,                   
            	do_cancel,     // cancel thread function 
            	hstmt,               
            	0,                   
            	&dwThreadID[1]);     
 
    // Check the return value for success. 
    if (hThread[1] == NULL)
	   printf("Unable to create thread!!\n");

    printf("\n The cancel thread created!\n");

    fprintf (stdout, "Wait until all threads have terminated.\n");
    WaitForMultipleObjects( 2, hThread, TRUE, INFINITE);

   rc = SQLDisconnect(hdbc);
   checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "SQLDisconnect failed");
   
   rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);   
   checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) " Connection Handle Free failed\n");

   rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Environment Handle Free failed\n");
        return -1;
    }
    
   return(0)
}
Listing 3. Function called by cancel thread
DWORD WINAPI do_cancel (void *arg)
{
    /* Declare variables */
    SQLHSTMT        hstmt;
    SQLRETURN       rc = 0;

    hstmt = (SQLHSTMT)arg;
    if (hstmt == NULL)
    {
       fprintf (stdout, "Cancel thread: Invalid statement handle\n");
       return -1;
    }

    Sleep(5000);
    
    rc = SQLCancel(hstmt);

    if (checkError (rc, SQL_HANDLE_STMT, hstmt, 
	(SQLCHAR *) "Error in cancel thread - cancelling the statement failed\n"))
	return -1;
	
    printf ("do_cancel: the worker thread is cancelled due to running for too long.\n");
    
    return 0;    
}
Listing 4. Function called by worker thread
DWORD WINAPI do_test (void *arg)
{
    /* Declare variables */
    SQLHSTMT        hstmt;
    SQLCHAR SQLStmt[255] = "insert into tab1(col,col2) values (3,'ghl')";
    SQLRETURN       rc = 0;

    hstmt = (SQLHSTMT)arg;
    if (hstmt == NULL)
    {
       fprintf (stdout, "Worker thread: Invalid statement handle\n");
       return -1;
    }
        
    /* Set lock mode to wait for repro assuming the tab1 is locked */
    rc = SQLExecDirect(hstmt, (UCHAR*)"set lock mode to wait;", SQL_NTS);
    checkError (rc,SQL_HANDLE_STMT,hstmt,(SQLCHAR *)"do_test -- set lock mode failed\n");

    /* Prepare and Execute the query */
    rc = SQLExecDirect( hstmt, SQLStmt, SQL_NTS );
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, 
	(SQLCHAR *) "Error in worker thread -- SQL statement execution failed\n"))
    {
	return -1;
    }
    
    return 0;
}

The SQL_ATTR_QUERY_TIMEOUT statement attribute is to specify how many seconds to wait for an SQL statement to execute before returning to the application. By default, this attribute is set to 0, meaning there is no timeout and the client will wait until the SQL request completes or fails with error. You can set the SQL_ATTR_QUERY_TIMEOUT attribute to a positive integer value to allow Informix ODBC driver to interrupt the SQL statement when it runs for longer than timeout interval.

The following example sets the SQL statement timeout interval to 25 seconds.

rc = SQLSetStmtAttr(hstmt, SQL_ATTR_QUERY_TIMEOUT,  (SQLPOINTER)25,  0);

When the 25 seconds timeout interval is expired, Informix ODBC driver attempts to interrupt the statement by sending a cancel request to the server and report the following error upon the completion of the cancellation.

	[Informix][Informix ODBC Driver]Timeout expired.
	[Informix][Informix ODBC Driver][Informix]Statement interrupted by user.

When the Informix database server receives the cancel request, the server attempts to interrupt the processing of the SQL request and returns error -213 to the client if the SQL request interruption succeeds. The server does not terminate the current connection when interrupting the SQL request. It is the ODBC application's responsibility to handle the error properly and take care of any open transactions if needed.

Interrupting an SQL request in .NET application

Informix .NET provider supports IfxCommand.Cancel() method for canceling the execution of a command. If the attempt to cancel fails, no exception is generated. If there is nothing to cancel, nothing happens.

When an SQL statement is canceled in the middle of execution, the following error message is returned to the application:

ERROR [HY008][Informix .NET provider][Informix]Statement interrupted by user.sqlerrm(tab1)

where tab1 is the table name involved.

The following example is a multi-threaded C# program that creates two threads — one executes a long query and the other interrupts the query by calling IfxCommand.Cancel() if the query runs longer than 5 seconds.

Listing 5. Sample program that calls IfxCommand.Cancel()
using System;
using System.Data;
using System.Text;
using System.EnterpriseServices;
using IBM.Data.Informix;
using System.Threading;

class Timeout
{
    public static IfxCommand my_cmd;

    public static void do_Cancel()
    {
        Thread.Sleep(5000);  //sleep for 5 seconds before starting to cancel the command
        try {
           my_cmd.Cancel();
           System.Console.WriteLine("SQL command cancelled" );
        }
        catch (Exception ex)
        {
            System.Console.WriteLine(“ Exception occurred in Cancel()" );
            Console.WriteLine(ex.Message);
        }               
    }

    public static void do_Cmd()
    {
        my_cmd.CommandText = "select count(*) from systables a, systables b, systables c";
        System.Console.WriteLine(“ Running a long query - may take minutes ...");
        try {
           IfxDataReader reader = my_cmd.ExecuteReader();
           System.Console.WriteLine(“ Query finished");
           reader.Close();
        }        catch (Exception ex)
        {
            System.Console.WriteLine(“ Exception occurred in ExecuteQuery()" );
            Console.WriteLine(ex.Message);
        }        
    }

    static void Main()
    {
        string connectionString = 
                   "Server=ifx1170uc4;database=stores;uid=informix;password=mypassword;";
        try
        {
            using (IfxConnection connection = new IfxConnection(connectionString))
            {
                connection.Open();

                my_cmd = connection.CreateCommand();

                System.Console.WriteLine(“ Starting the command thread");
                Thread cmdThread = new Thread(new ThreadStart(do_Cmd));
                cmdThread.Start();

                System.Console.WriteLine(“ Starting the cancel thread");
                Thread cancelThread = new Thread(new ThreadStart(do_Cancel));
                cancelThread.Start();

                cmdThread.Join();
                cancelThread.Join();

            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The program will display the following output when the long query is interrupted:

C:\clientsdk\dotnet>cmdcancel.exe
Starting the command thread
Starting the cancel thread
Running a long query - may take minutes ...
SQL command cancelled
Exception occurred in ExecuteReader()
ERROR [HY008] [Informix .NET provider][Informix]Statement interrupted by user.

You can also set the IfxCommand.CommandTimeout property to the number of seconds to wait before terminating the attempt to execute a command or the execution of a command. An error is generated after termination.

The following C# example creates an IfxCommand and sets its CommandTimeout property to 20 seconds.

Listing 6. Sample program that sets IfxCommand.CommandTimeout property
using System;
using System.Data;
using System.Text;
using IBM.Data.Informix;

class Program
{
    static void Main()
    {
        string connectionString = 
                  "Server=ifx1170uc4;database=stores;uid=informix;password=mypassword;";
        try
        {
            using (IfxConnection connection = new IfxConnection(connectionString))
            {
                connection.Open();
                IfxCommand  my_cmd = connection.CreateCommand();
                System.Console.WriteLine("Running a long query ...");
                my_cmd.CommandText = 
                            "select count(*) from systables a, systables b, systables c";
                my_cmd.CommandTimeout = 5;   // time out if it runs longer than 5 seconds
                IfxDataReader reader = my_cmd.ExecuteReader();
                reader.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The program will display the following output when the long query is timed out:

C:\clientsdk\dotnet\>cmdtimeout.exe
Running a long query ...
ERROR [HYT00] [Informix .NET provider]Timeout expired.
ERROR [HY008] [Informix .NET provider][Informix]Statement interrupted by user.

By default, the Informix .NET provider sets the CommandTimeout property to 30 seconds. If the IfxCommand CommandTimeout property is not set in a .NET application, an SQL command gets timed out after the 30-second timeout interval expires.

Setting the CommandTimeout property to 0 disables the SQL command timeout, and the command runs until it is successful or failed.

Interrupting an SQL request in ESQL/C application

To interrupt an SQL request being processed by the database server, you can use the sqlbreak() library function in your ESQL/C application. Unlike other Informix client APIs, Informix ESQL/C does not have the SQL statement timeout feature available for an application to take advantage of. Instead, the application must be designed and implemented to allow users to interrupt SQL requests when needed. There are two possible methods you may use in your ESQL/C application to allow SQL request interruption:

  • Provide the application user with the ability to interrupt an SQL request once it has begun execution by using a user-defined signal-handler function. When the user presses the Interrupt key, the application becomes unblocked and calls the SIGINT signal-handler function. This signal-handler function includes a call to sqlbreak() to interrupt the database server.
  • Specify a timeout interval with the sqlbreakcallback() function. After the timeout interval elapses, the application becomes unblocked and calls the callback function. This callback function includes a call to sqlbreak() to interrupt the database server.

The Informix ESQL/C demo program timeout.ec demonstrates how to set up a timeout interval. It comes with Informix CSDK installation and can be located under $INFORMIXDIR/demo/esqlc on UNIX or Linux, or %INFORMIXDIR%\demo\esqldemo on Windows.

This program uses the sqlbreakcallback() function to perform the following actions:

  • To specify a timeout interval of 200 milliseconds for execution of an SQL request.
  • To register the on_timeout() callback function to be called when an SQL request begins and ends as well as when the timeout interval elapses.

If execution of an SQL request exceeds the timeout interval, the callback function uses the sqldone() function to ensure that the database server is still busy, prompts the user for confirmation of the interrupt, then uses the sqlbreak() function to send an interrupt request to the database server. See more information about this demo program Informix 11.70 Information Center.


Informix SQL request interruption in TCP socket layer

Overview

It is possible that you run into the following scenarios sometimes:

  • You want to interrupt a query issued from the application that does not have the option to cancel an SQL statement.
  • You want to cancel an uninterruptible SQL request.
  • Your application is blocked and not responding due to some networking problem, such as missing TCP packet that causes the Informix client and server to wait for each other.

When that happens, most time you may end up killing the current process and restarting it. If that happens to an application server or a proxy, restarting the process affects all the current users and sometimes causes severe consequences.

To help Informix users handle the situations described above, Informix introduced a feature in recent releases of Informix client, including CSDK and JDBC. The feature allows Informix users to enable TCP socket timeout options for specific connections to the Informix server. If the TCP socket timeout option is enabled for an Informix database connection, any Informix client thread blocked for receiving the expected server response gets timed by the operating system when the timeout period expires. It is important to understand that the timeout is performed by the TCP socket layer that is actually part of the operating system and that it can interrupt any type of database operations on the client side, without notifying the Informix server.

Whenever the TCP timeout occurs to the application, the communication between the Informix client and server is broken due to the system error returned from the TCP socket layer. The Informix client returns the communication error or raises a system exception to the application accordingly, depending on what Informix client API is used. The application must handle the error or exception properly to make sure it takes care of any open transactions and resources allocated. The Informix server rolls back the current transaction and clean up resources if needed once it detects the broken communication.

It is highly recommended that you use this timeout feature cautiously because the TCP socket timeout is actually a system error or exception that terminates the current session without notifying the Informix server. It should be set to a value that will never be expired normally. It might be used as a way to get the application out of some abnormal scenarios, such as a hang caused by a lost TCP packet. When a connection is interrupted by TCP socket timeout, the Informix server will not do anything about the current SQL request being processed until the server detects the session is dropped. If the request is waiting for a lock, the session can stay until the lock is available.

Contact IBM technical support for assistance if needed before you start to enable it for your application.

TCP socket timeout in JDBC

Informix JDBC driver communicates with Informix server by using the Java socket class that by default uses blocking mode. Because of that, when Informix JDBC driver reads from a socket connection, it will wait if no immediate response from the server is available. Starting with Java™ 1.5, a Java networking application can call the method Socket.setSoTimeout() to set a timeout on the socket.

 public void setSoTimeout(int timeout) throws SocketException

This method sets the SO_TIMEOUT field with the specified timeout in milliseconds. With this option set to a non-zero timeout, a read() call on the InputStream associated with this socket will block for only this amount of time. If the timeout expires, a java.net.SocketTimeoutException is raised, though the socket is still valid.

To take advantage of this Java feature that could potentially help Informix users when needed, Informix introduced a JDBC environment variable named IFX_SOC_TIMEOUT in JDBC driver V3.50.JC4. If it is set to a positive integer value, Informix JDBC will set the timeout on the socket with the value specified and raise an exception if the read operation exceeds the timeout.

The timeout can be set in one of following two ways, in milliseconds:

  • In Connection URL: IFX_SOC_TIMEOUT=1000
  • In a datasource: dataSource.setIfxIFX_SOC_TIMEOUT(1000);

A timeout of zero is interpreted as an infinite timeout or no timeout.

TCP Socket timeout in ODBC

For an Informix non-Java client, the TCP socket read timeout feature is available starting with CSDK 3.50.xC5.

Informix ODBC users can enable the TCP socket read timeout in different ways:

  • Setting RECVTIMEOUT connection keyword with a specified timeout in seconds. The example shows that the ODBC socket read timeout is set to 180 seconds in a connection string:
          rc = SQLDriverConnect(hdbc, 
    			NULL,
    			(UCHAR*) "DSN=ifx1170fc4; RECVTIMEOUT=180",
    			(SQLSMALLINT) SQL_NTS,
    			connectOutput,
    			sizeof(connectOutput),
    			&result,
    			SQL_DRIVER_NOPROMPT);
  • Setting the SQL_INFX_ATTR_RECVTIMEOUT connection attribute with a specified timeout, in seconds before opening the connection. For example:
    rc=SQLSetConnectAttr(hdbc,SQL_INFX_ATTR_RECVTIMEOUT,(SQLPOINTER)180,SQL_IS_UINTEGER);
  • Setting the RTO option with a specified timeout for the Informix server SQLHOSTS entry. The RTO option takes precedence if RTO and RECVTIMEOUT are set. The RTO option in SQLHOSTS is invalid for DSN-less connection. The following SQLHOSTS entry example sets the timeout to 180 seconds:
    ifx1170fc4    onsoctcp      ifx_host1   9088    b=32767,rto=180

If you enabled the TCP socket read timeout in both the ODBC and Informix SQLHOSTS entries, the RTO option in SQLHOSTS has higher priority.

TCP socket timeout in .NET provider

For Informix .NET provider users, this feature can only be enabled by setting the RTO option for the Informix server SQLHOSTS entry, unless an ODBC DSN is used for the connection. On Windows, you set RTO in Informix Setnet32.exe. You must set IfxCommand. CommandTimeout to 0 to make the RTO option effective.

TCP socket timeout in ESQL/C

You can only set the SQLHOSTS RTO option to enable TCP socket timeout for an ESQL/C application. That means you cannot set it for a specific connection in ESQL/C, unlike in JDBC and ODBC. When the timeout occurs, the application will receive error -25580, meaning that a system error occurred in network function.


Conclusion

Database operations can sometimes take much longer than expected for many reasons. This article describes how to address unexpected long-running database requests with options provided by IBM Informix to improve application performance.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=806197
ArticleTitle=Optimizing Informix database access
publish-date=03222012