Optimize Informix Database Access

Generally, the time required to access an IBMInformix database is less than one second to a desired time period, depending on the specific database operation. However, sometimes it may take longer time than you expected, for many reasons, such as network speed, system performance, and system load.

The time required to access an IBM Informix database is generally less than one second to a desired time period, depending on the specific database operation. However, sometimes it may take longer time than you expected, for many reasons, such as network speed, system performance, and system load.

In the worst case, the Informix Client may be blocked forever because it cannot obtain the expected response. This article explains how to interrupt an SQL or connection request when it takes longer than expected to improve the performance of your Informix application.

Data accessibility becomes more and more important in many businesses. Informix server is famous for its fast performance and high reliability. However, accessing data stored on the Informix server from a client also depends on the performance of hardware and operating systems. Informix provides many technologies to ensure the performance and reliability of data access, such as MACH-11 including the Connection Manager and automatic database statistics update. However, this article mainly introduces how Informix handles some exceptional scenarios caused by unpredictable random system events, which may cause database operations to take longer than expected, these include slow network speeds, unexpected system loads, loss of TCP packets, and other unforeseen events.

In many cases, the Informix Client uses the TCP socket in blocking mode to communicate with the Informix server. This means that the client will wait until there is available server data for communication. For a slow connection request, you can interrupt and retry the request by setting the INFORMIXCONTIME and INFORMIXCONRETRY environment variables to appropriate values based on your normal system behavior.

When the database server executes an SQL request, the application is blocked before the expected response from the database server is available for reading. Sometimes you may want to interrupt an SQL request to gain control of the application, because you do not want to wait until the current SQL statement completes the operation. For example, if you accidentally provide an incorrect Search Condition for a long query, you want to cancel the SELECT statement instead of waiting for unnecessary data. If it is a multi-tier application, the situation may be worse. The Informix Client (including CSDK and JDBC) allows users to interrupt SQL requests during execution. The Design and Implementation of applications must support the cancellation of SQL requests.

If the running application is from a third-party vendor, it is difficult to interrupt SQL requests if there are no related options in the application. To handle this situation, Informix introduced a new feature in the latest client version that allows you to enable TCP socket timeout on the Informix Client so that requests are interrupted without changing any code.

The sample programs listed in this article are simplified and used only for demonstration purposes.

Informix connection timeout

Overview

Connecting to an Informix database usually takes less than one second to several seconds (depending on the network speed), but sometimes it takes longer than expected. INFORMIXCONTIME and INFORMIXCONRETRY are two client environment variables that can control the connection behavior.

INFORMIXCONTIME

The INFORMIXCONTIME environment variable specifies that the database connection request will only return an error several seconds after each attempt to establish a connection to the database server. If the INFORMIXCONTIME environment variable is set to 0 or a value smaller than 0, the database connection request will wait until the operation is successful or fails. If the variables are not set, the default values may be different in different systems:

• ESQL/C: The default value of INFORMIXCONTIME is 60 seconds. You can use the OS command in the OS environment to set the INFORMIXCONTIME environment variable for ESQL/C Programs.
  • UNIX®/Linux®: Use the export or setenv command, depending on the shell.
  • Windows®: Set command or control panel.
• ODBC: it is set to 15 seconds by default. Instead, you can use this SQL _ATTR_LOGIN_TIMEOUT connection property to specify the connection timeout interval of the connection request. If both INFORMIXCONTIME and SQL _ATTR_LOGIN_TIMEOUT are set, the SQL _ATTR_LOGIN_TIMEOUT connection attribute has a higher priority.
• OLEDB and. NET: The default value is 15 seconds. You can set the INFORMIXCONTIME environment variable for the Informix OLEDB provider, but this does not apply to the Informix. NET provider that uses the IfxConnection. ConnectionTimeout attribute to set connection timeout.
• JDBC: The default value is 0, indicating that the connection never times out. You can set the INFORMIXCONTIME environment variable in the JDBC connection URL or JDBC DataSource connection attribute. JDBC 3.70.JC1 introduces another environment variable named LOGINTIMEOUT. You can use it to specify the time period (in milliseconds) for polling server ports to establish connections ). If the application does not connect to the Informix database server within the specified time, an error is returned. It can be set to only one DataSource attribute. Setting it in the JDBC connection URL will not take effect.

You can set the INFORMIXCONTIME environment variable to an appropriate value based on normal application behavior.

If the connection request involves searching for DBPATH, you may see that the connection request takes longer than the INFORMIXCONTIME limit, so that an error is returned. If DBPATH contains multiple servers, the INFORMIXCONTIME value must be divided by the number of server entries 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 the client can perform on each database server within the time limit specified by INFORMIXCONTIME. For example, if INFORMIXCONTIME is set to 30 seconds and INFORMIXCONRETRY is set to 2, the client will try to resend the connection request at 15th seconds if the initial connection attempt cannot be implemented. If necessary, it will perform another connection attempt at 30th seconds before termination.

Before CSDK and JDBC 3.70.xC1, the default value of the INFORMIXCONRETRY environment variable is 3. Starting from JDBC 3.70.xC1, the new default value is 1, which means that only one Retry is performed after the initial connection attempt.