CODEWITHSUNDEEP
jdbctransactionswebsphere-8
adam..
adam..

Reputation: 135

How to avoid transaction timeout (WTRN0006W) on Websphere 8.5.5 with pure JDBC

In EAR application running on Websphere application server 8.5.5 we have to execute CallableStatement (call stored procedure in Oracle DB) which runs more than five minutes or more depending on input data. The operation is automatically rolled back because of transaction timeout (code WTRN0006W) which is set to 120 seconds by default in Websphere AS. We can't change this value due to customers requirements.

We can split input data to smaller chunks and execute CallableStatement several times to achieve shorter run time (30 seconds or so). Processing whole data chunks still takes more than 120 seconds (as expected). But the transaction timeout still occurs although for every statement execution with small chunk (in loop) we are getting connection from datasource configured in WAS, set autocommit to false, after statement execution doing commit and closing connection. Then again with next chunk in next loop cycle.

The whole process of statement executions is done in Stateless EJB which is called from Singleton EJB scheduled to run twice a day. We are not using JTA neither JPA, just JDBC.

Is it possible to avoid transaction timeout if we execute statement several times?

How we obtain datasource during application start:

javax.naming.Context ctx = new InitialContext();
javax.sql.Datasource ds = (javax.sql.Datasource) ctx.lookup("jndi/datasource1");

How we obtain Connection:

java.sql.Connection conn = m24sb.getConnection();
conn.setAutoCommit(false)

How we execute statement:

try (CallableStatement sta = conn.prepareCall("{ call SOME_STORED_PROC }"))) { 
// ... setting statement params
sta.execute();
// ... resolving returned values
}

and then commit and closing connection.

Thanks in advance for answers!

Upvotes: 0

Views: 1587

Answers (1)

njr
njr

Reputation: 3484

Try marking your stateless session bean method as transaction NOT_SUPPORTED or NEVER, which will cause it to run outside of a global transaction. (Note that you would need to do this anyway for your connection.commit() call to be valid -- it likely just isn't getting that far due to the timeout).

@javax.ejb.TransactionAttribute(javax.ejb.TransactionAttributeType.NEVER)
public void yourStatelessEJBMethod() {
    ... code that invokes stored procedure and commits transaction
}

Upvotes: 2

Related Questions