Pages

25 October 2011

ORA-02049 SQLException from iBatis 2.x with Spring in IBM WAS 6.2

If the number of threads that try to access a datasource concurrently, exceeds the maximum number of connections to database; it will cause BadSqlGrammarException. Oracle database returns ORA-02049 error code in the detail of exception. There are two solutions, i googled on the web: one of them is increasing the SHARED_POOL_SIZE in init.ora on the database side. The other one is changing the datasource connection pools properties on the websphere application server as shown in the screenshot at the bottom of this post.

Exception Log
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the ${sql-map-id}-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: java.sql.SQLException: ORA-02049: timeout: distributed transaction waiting for lock

 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111)
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
 at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
 at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:411)

Recommended Datasource Connection Pools Settings at WAS 6.2
Recommended Datasource Connection Pools Settings at WAS 6.2

No comments:

Post a Comment

Thx for reading! Comments are appreciated...