Timeout properties in IBM Data Server Driver for JDBC and SQLJ

Objective

• queryTimeout
• queryTimeoutInterruptProcessingMode
• timerLevelForQueryTimeOut
• memberConnectTimeout
• blockingReadConnectionTimeout
• commandTimeout
• connectionTimeout
• loginTimeout
• currentLockTimeout
• enableTimeoutOnCursor

1.         queryTimeout 

Description: queryTimeOut specifies the amount of time (in seconds) that a client waits for query execution to complete before attempting to cancel the execution and return control to the application.

• A separate connection is used to inform the server to interrupt the current request.

• If the statement is cancelled, the driver automatically closes the statement, but the connection remains active.

Usage:

• For T4 and T2 LUW: Statement.setQueryTimeout(int value); (where int value is in seconds)

• If queryTimeOut is not set directly on the Statement, commandTimeout is checked and applied.

• After commandTimeout seconds elapse, the driver throws SQLException.

• Default: 0 (no timeout limit). Supported only by Type-4 driver.

Notes:

• If the server does not support interrupt capability, java.sql.SQLException with "unsupported feature" is thrown.

• Statement.setQueryTimeout applies only to that statement and takes precedence.

• The internal behavior of queryTimeout depends on the setting of queryTimeoutInterruptProcessingMode. This property determines how the driver handles query interruption when a timeout occurs.

Sysplex Environment with SSL/TLS:

• JCC driver uses interrupt server and port details from configuration.

• On cancel, JCC creates a new SSL socket if the interrupt port matches the parent connection port and SSL is enabled; otherwise, a TCP/IP socket is opened.

• Db2 for z/OS: Cancel request is not supported; socket connection is closed instead.

Best Practices:

• commandTimeout applies at connection level and cascades to statements if Statement.setQueryTimeout is not set.

• commandTimeout can be set in the connection URL.

• Updating queryTimeout requires application code changes.

Known Issues:

• Without explicit purge, TimerTasks accumulate, causing OutOfMemoryError.

• Solution: Use JCC 4.31.21 with global property: db2.jcc.purgeQueryTimerTasks=yes (Default behaviour 11.5.9.0 (Mod 9 Fix Pack 0) [ibm.com])

Exception details:

com.ibm.db2.jcc.am.SqlTimeoutException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.35.30

2.        queryTimeoutInterruptProcessingMode 

Description: Determines the interrupt processing mode for query/command timeout:

• Cancels only the executing statement or

• Drops the connection by closing the socket.

Valid Values:

• INTERRUPT_PROCESSING_MODE_DISABLED (0)

• INTERRUPT_PROCESSING_MODE_STATEMENT_CANCEL (1)

• INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET (2)

Notes:

• For Db2 z/OS, driver enforces CLOSE_SOCKET mode regardless of user setting.

• For LUW, driver sends a CANCEL request to the database.

Exceptions:

• Type-4 connectivity to Db2 for z/OS: Always CLOSE_SOCKET (2)

  • Sysplex workload balancing: SQL error -30108

  • Automatic client reroute: SQL error -4498

  • Neither enabled: SQL error -4499

• LUW data servers:

  • STATEMENT_CANCEL (1): SQL error -952

  • CLOSE_SOCKET (2):

  • Workload balancing: -30108

  • ACR enabled: -4498

Exception details:

1) queryTimeoutInterruptProcessingMode=1 (STATEMENT_CANCEL)

• Behavior: The driver issues a statement cancel against the server; the connection/socket stays open.

• Exception: SQLException with SQLCODE -952 (indicates that processing was interrupted due to a timeout/cancel).

2) queryTimeoutInterruptProcessingMode=2 (CLOSE_SOCKET)

• Behavior: The driver closes the socket immediately (it’s a hard interrupt).

• Exception: You will typically see a SQLException with a communication-related SQLCODE: 

  • -4499 when neither workload balancing nor automatic client reroute is involved.

  • -4498 when automatic client reroute is enabled (without workload balancing).

  • -30108 when workload balancing is enabled.
    (Exact code also varies by server type; for Db2 for z/OS with type 4 connectivity, the driver uses CLOSE_SOCKET and returns one of the above based on those features.)

    Quick decision table

3.          timerLevelForQueryTimeOut 

Description: Controls timer creation for query timeout:

• QUERYTIMEOUT_STATEMENT_LEVEL (1): Timer per Statement; disposed after execution.

• QUERYTIMEOUT_CONNECTION_LEVEL (2): Timer per Connection; disposed on Connection.close.

• Default: 0 (NOT_SET)

Usage:

ds.setTimerLevelForQueryTimeOut(DB2BaseDataSource.QUERYTIMEOUTSTATEMENTLEVEL);

Global property:

db2.jcc.timerLevelForQueryTimeOut=1

Override property:

db2.jcc.override.timerLevelForQueryTimeOut=2

Best Practices:

• Prefer QUERYTIMEOUT_CONNECTION_LEVEL to avoid multiple timer threads.

• Set timerLevelForQueryTimeOut explicitly to avoid problematic defaults on older builds.

• If you are on JCC 4.26.x (11.5 GA) or similar, follow IBM’s APAR guidance: upgrade to JCC ≥ 4.27.25 (11.5 M4 FP0) or use the documented workaround (explicitly set timerLevelForQueryTimeOut to a stable value; support notes mention 2 or -1 depending on environment)

Known Issues:

• OutOfMemoryError when timer threads are not closed after network failures (e.g., SQL -4499).

• Fix available in JCC 4.31 (V11.5 M7 FP0 JDBC & SQLJ).

Exception details:

When timerLevelForQueryTimeOut is set to QUERYTIMEOUT_STATEMENT_LEVEL, a timed‑out SQL operation still raises com.ibm.db2.jcc.am.SqlTimeoutException. On the wire you’ll see Db2 return SQLCODE=-952 and SQLSTATE=57014 (“statement interrupted due to timeout”). The timerLevelForQueryTimeOut setting changes where the driver attaches its timeout timer (per statement vs. broader scope), not the exception type that is thrown.

4.          memberConnectTimeout 

Description: Specifies seconds to wait before a socket connection attempt to a member fails during ACR processing.

Usage:

• Property: memberConnectTimeout

• Data Type: int

• Default: 0 (uses loginTimeout)

Best Practices:

• Set 1–3 seconds for fast failover in HA environments.

• Use with loginTimeout for overall control.

   

Known Issues:

• High values cause failover delays.

• Misconfiguration may lead to unexpected failures or slow recovery.

Exception details:

memberConnectTimeout controls socket connect timing, and when it times out, the driver throws a socket‑timeout error (not a SQL execution timeout) – expressed as ERRORCODE=-4499, SQLSTATE=08001/08004.

When the connect attempt exceeds memberConnectTimeout, the socket open fails and the driver throws a java.sql.SQLException (or a more specific DisconnectNonTransientConnectionException on newer builds) with ERRORCODE = -4499 and one of the SQLSTATEs commonly seen for connectivity issues:

• SQLSTATE=08001 – “SQL client unable to establish connection” (often used for network/handshake errors).

• SQLSTATE=08004 – “Server rejected the connection” (often indicates the target wasn’t found or refused).

5.          blockingReadConnectionTimeout 

Description:
The amount of time in seconds before a connection socket read times out. This property applies only to IBM Data Server Driver for JDBC and SQLJ type 4 connectivity and affects all requests that are sent to the data source after a connection is successfully established.
This applies especially to long-running queries or idle connections waiting for data. If the timeout is exceeded, the driver throws an exception, allowing applications to avoid hanging indefinitely.

Usage:

This property is typically used in connection URLs or set via a properties object in Java:
Connection URL example:
jdbc:db2://hostname:50000/DBNAME:blockingReadConnectionTimeout=30;
Setting using properties object:
Properties props = new Properties();

props.setProperty("blockingReadConnectionTimeout", "30");

Connection con = DriverManager.getConnection(jdbcUrl, props);

Default Value:

• 0 (zero) — This means no timeout (i.e., wait indefinitely).

• A positive integer means the timeout period in seconds.

Best Practices:

• When used in production environments, it prevents indefinite hangs during network/database issues.

• When combined with retry logic, after a timeout, retrying the operation may help in transient failures.
  Tune the timeout based on workload

• Setting too short a timeout can prematurely fail long running but legitimate operations.

• Monitor failure logs because If exceptions occur frequently, you may need to increase this value.

Notes:

• Only applies to blocking reads, such as when a ResultSet is being fetched.

• Not applicable to the initial connection establishment – use loginTimeout for that.

• Can be combined with queryTimeout on a Statement object, but they serve different purposes such as 

• queryTimeout is a server-side timeout for query execution.

• blockingReadConnectionTimeout is a client-side timeout for reading from the network/socket.

Exceptions:
When the blockingReadConnectionTimeout property is exceeded, the IBM JCC driver behavior is specific and depends on the version and the nature of the network failure.

For a standard timeout event where the socket read times out, the correct and most specific exception thrown by the IBM DB2 JCC driver is:

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException

While this is the internal JCC class, it is a subclass of the standard JDBC exception: java.sql.SQLNonTransientConnectionException

This exception is typically accompanied by the DB2 SQL Error Code -4499.

SQLState: The SQLState associated with this timeout is usually 08001 (Connection failed) or 08006 (Connection failure).

The JCC driver treats a blockingReadConnectionTimeout as a fatal blow to the physical connection. Because the driver cannot guarantee the state of the socket or the synchronization of the protocol stream after a timeout, the connection is marked as "stale" or "closed."

You cannot reuse the same Connection object. You must close the current connection and instantiate a new one to resume operations.

6.          commandTimeout  

Description:

Specifies the maximum time in seconds that an application that runs under the IBM Data Server Driver for JDBC and SQLJ waits for SQL operations to complete before the driver throws an SQLException. The wait time includes time to obtain a transport, perform failover if needed, send the request, and wait for a response. 
If the java.sql.Statement.setQueryTimeout method is invoked, the query timeout value that is set through Statement.setQueryTimeout overrides the commandTimeout value.

commandTimeout applies to the execution of Statement, PreparedStatement, and CallableStatement methods execute, executeQuery, and executeUpdate. 
commandTimeout also applies to the executeBatch method if property queryTimeoutInterruptProcessingMode has the value INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET (2).

Usage:

• Connection URL:

  jdbc:db2://host:port/DBNAME:commandTimeout=60;

• Programmatic via Properties:

  props.setProperty("commandTimeout", "60s");

  Connection conn = DriverManager.getConnection(url, props);

• Via JDBC DataSource (e.g. DB2BaseDataSource):

  ds.setCommandTimeout(60); // seconds

Default value:
The data type of this parameter is int. The default is 0, which means that there is no timeout.(i.e. infinite wait)

This property was introduced in driver releases 3.64 and 4.14 and later.

Notes:

• Covers execution of Statement, PreparedStatement, CallableStatement, and executeBatch if queryTimeoutInterruptProcessingMode is set to INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET (mode 2).
• Does not apply to socket read/receive delays (that's blockingReadConnectionTimeout) or initial connection establishment.
• setQueryTimeout in code has priority over commandTimeout driver settings.

Best Practices:

• Set a command timeout in environments with potential server slowness to avoid infinite  execution.
• In batch jobs or long queries, consider using longer timeouts.
• Avoid setting very low values that may prematurely terminate valid long-running queries.
• Monitor logs for frequent timeouts—adjust commandTimeout accordingly or optimize queries.

Exceptions:
The primary exception thrown for a commandTimeout is: java.sql.SQLTimeoutException

 the IBM JCC driver maps this to its internal class: com.ibm.db2.jcc.am.SqlTimeoutException

Instead of killing the entire connection, it attempts to cancel the specific execution and keep the connection alive for future use.
The underlying physical connection to the database remains valid and open.

Error Code: It is typically associated with the DB2 Error Code -4490.

SQLState: The SQLState returned is 57014, which specifically indicates that the processing of the SQL statement was terminated because a cancel request was received (in this case, triggered by the timer).

7.          connectionTimeout 

Description:

Specifies the maximum time in seconds that the IBM Data Server Driver for JDBC and SQLJ waits for a reply from a group of data servers when the driver attempts to establish a connection.

Usage:

• In JDBC URL:

  jdbc:db2://host:port/DBNAME:connectionTimeout=30;

• Via Properties object:

  props.setProperty("connectionTimeout", "30");

  Connection$conn = DriverManager.getConnection(url, props);

• Using DB2BaseDataSource or similar (if supported):

  ds.setConnectionTimeout(30);

Default value:

• The data type of this parameter is int. The default value is 0.
• If connectionTimeout is set to a positive value, that value overrides any other timeout values that are set on a connection, such as loginTimeout.
• If connectionTimeout is set to 0, and automatic client reroute is not enabled, there is no time limit.

Best Practices:

• Use in production to avoid indefinitely hung connection attempts, especially in high-latency or unstable network environments.

• Tune based on environment expectations—e.g. cloud deployments often benefit from non-zero values (30–60 s).

• Too short a value may cause premature failures during transient server slowdowns balance sensitivity vs reliability.

• For pool usage, combine with connection‑pool-specific timeouts—understanding they operate at different layers.

Exceptions:

When the connectionTimeout property is exceeded (the time spent trying to establish the initial connection to the server), the IBM JCC driver behavior is similar to a network failure but occurs at the handshake stage.

The correct exception thrown by the IBM JCC driver is:

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException

As with other connection-level failures, this is caught as the standard JDBC exception: java.sql.SQLNonTransientConnectionException

Error Code: Typically returns -4499.

SQLState: Usually 08001 (specifying that the connection was unable to be established).

Alternative Error Code: In some environments (like Sysplex or specific Cloud configurations), you may see -4210 with SQLState 57033 ("Timeout getting a transport object from pool").

Precedence: If connectionTimeout is set to a positive value, it overrides the standard JDBC loginTimeout.

8.          loginTimeout 

Description:

The maximum time in seconds to wait for a connection to a data source. After the number of seconds that are specified by loginTimeout have elapsed, the driver closes the connection to the data source. 
The data type of this property is int. The default is 0. A value of 0 means that the timeout value is the default system timeout value. This property is not supported for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on Db2 for z/OS.

Usage:

• In a JDBC URL (driver-level setting):

  jdbc:db2://host:port/DBNAME:loginTimeout=30;

• Via Java Properties object:

  props.setProperty("loginTimeout", "30");

  Connection conn = DriverManager.getConnection(url, props);

• Connection pool usage:

  DB2ConnectionPool pool = new DB2ConnectionPool();

  pool.setLoginTimeout(30); // seconds

Notes:

• If the data server environment is a Db2 pureScale environment, the wait time for a connection is determined by a combination of loginTimeout, maxRetriesForClientReroute, and retryIntervalForClientReroute. loginTimeout determines only the time for a single attempt to establish a connection to a data server.
• There might be multiple attempts to establish a connection, based on the maxRetriesForClientReroute value. There might also be gaps between attempts to establish a connection, based on the retryIntervalForClientReroute value.
• During automatic client reroute processing, the memberConnectTimeout property takes precedence over the loginTimeout property.

Best Practices:

• Always set a reasonable loginTimeout (e.g., 30 s) to avoid indefinite hangs during connection attempts, especially in unpredictable network or cluster environments.
• Ensure alignment between driver-level and pool-level settings—mismatched timeouts can lead to confusing errors or unnecessary delays.
• Use programmatic overrides (via DataSource or DriverManager) when specific operations require different timeout behavior.
• Monitor and adjust—if connection timeouts occur frequently, investigate network or server issues or increase the timeout threshold.

Exceptions:

When the loginTimeout is exceeded, the IBM JCC driver throws an exception during the initial connection handshake. Because loginTimeout is a standard JDBC property (defined by DriverManager or DataSource), its behavior is closely aligned with the JDBC specification but uses JCC-specific internal mapping.

The correct exception thrown by the IBM JCC driver is:

java.sql.SQLTransientConnectionException

This maps to the internal JCC class: com.ibm.db2.jcc.am.SqlTransientConnectionException

Error Code: Typically returns -4499 (Generic "connection rejected" or "connection failure").

SQLState: Commonly 08001, indicating that the connection was unable to be established.

This timeout covers the entire duration from the initial socket open until the user is fully authenticated and the session is established.

9.          currentLockTimeout 

 Description:
Specifies whether DB2 Database for Linux, UNIX, and Windows servers wait for a lock when the lock cannot be obtained immediately. 
 

Usage:

• URL-based configuration:

  jdbc:db2://<host>:<port>/<database>:currentLockTimeout=30;

• Via DataSource property:
  If using a container or library like Open Liberty and the DB2BaseDataSource, you can set it programmatically:

ds.setCurrentLockTimeout(30);
 

The data type of this property is int.   Wait for integer seconds. integer is between -1 and 32767, 

           inclusive.

          LOCK_TIMEOUT_NO_WAIT: ( 0 ) Do not wait for a lock. This is the default.   

          LOCK_TIMEOUT_WAIT_INDEFINITELY: ( -1 ). Wait indefinitely for a lock.       

          LOCK_TIMEOUT_NOT_SET:( -Integer.MAX_VALUE )  Use the default for the data source.

The currentLockTimeout property is now supported for connections to Db2 for z/OS. Setting this property sets the CURRENT LOCK TIMEOUT special register on Db2 for z/OS.

Notes:

• This property impacts the duration the client waits when a database resource is locked by another transaction.

• It overrides the default database-level LOCKTIMEOUT parameter for that JDBC connection session.

• The value must be a positive integer 

Best Practices:

• Always specify a currentLockTimeout (e.g., 30s) to avoid indefinite hangs when trying to acquire resource locks.

• Match the JDBC-level timeout with database-level settings to maintain consistent behavior.

Exceptions:

If the client cannot acquire a lock within the specified timeout period, the JDBC driver will typically raise a SQLException indicating a lock timeout. This mirrors the server’s behavior in reporting lock-related conflicts or delays.
When the currentLockTimeout is exceeded (the time an application waits to acquire a lock on a database resource), the IBM DB2 JCC driver treats it as a transaction-level failure.

The correct exception thrown by the driver is:

com.ibm.db2.jcc.am.SqlTransactionRollbackException

This is a subclass of the standard JDBC exception: java.sql.SQLTransientRollbackException

This is a Rollback Exception. Because the database could not satisfy the lock request within the allowed time, it automatically rolls back the current unit of work (transaction) to ensure data integrity.

Error Code: Typically returns -911.

SQLState: Usually 40001 (Deadlock or timeout occurred; transaction rolled back).

Reason Code: In the exception message (SQLERRMC), you will often see a reason code.

Reason Code 68: Indicates a Lock Timeout.

Reason Code 2: Indicates a Deadlock (closely related but different).

10.          enableTimeoutOnCursor 

Description:

For DatabaseMetaData or ResultSet methods that use Statement objects in their implementations, specifies whether the commandTimeout and queryTimeoutInterruptProcessingMode property values control the timeout behavior for those Statement objects.

Examples of methods that use Statement objects in their implementations are:

ResultSet.updateRow

ResultSet.insertRow

ResultSet.deleteRow

DatabaseMetaData.getProcedures

DatabaseMetaData.getTables

DatabaseMetaData.getColumns

Usage:

• URL-based configuration:

  jdbc:db2://<host>:<port>/<database>:enableTimeoutOnCursor = true;

• Via DataSource property:
  you can set it programmatically:

  ds.setEnableTimeoutOnCursor(“true”); 

Default Value:

The data type of this property is boolean. Default value is true.

If enableTimeoutOnCursor=false: The ResultSet.next() call could hang indefinitely if the network or server slows down mid-fetch.

Exceptions:

When this property is set to true and the time limit is reached during a fetch operation     (moving the cursor to the next row), the exception details are as follows:

The primary exception thrown is: java.sql.SQLTimeoutException

the IBM JCC driver maps this to: com.ibm.db2.jcc.am.SqlTimeoutException

This occurs when the ResultSet.next() call takes longer than the remaining time allowed by the queryTimeout. Without this property set to true, the driver only applies the timeout to the execution of the query, not the subsequent fetching of rows.

Error Code: Typically returns -4490.

SQLState: Returns 57014 (indicating the operation was canceled due to a timeout).

The connection remains open. Since this is a transient timeout, the underlying physical connection is still valid, but the specific cursor/statement that timed out is closed.