Identity Manager application server log shows repetitive 'Exception while writing to the DB' error

Products

• CA Identity Manager

Issue/Introduction

Application Server log shows the following repetitive "Exception while writing to the DB" error

12:07:10,326 ERROR [ims.RuntimeStatusDetail] Exception while writing to the DB
java.sql.SQLException: Numeric Overflow
 at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139)
 at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:125)
 at oracle.jdbc.driver.OracleCallableStatement.getInt(OracleCallableStatement.java:1346)
 at oracle.jdbc.driver.OracleCallableStatementWrapper.getInt(OracleCallableStatementWrapper.java:617)
 at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper.getInt(Unknown Source)
 at com.netegrity.ims.persistence.PersistenceProvider.saveRuntimeStatusDetail(PersistenceProvider.java:2955)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailService.writeToPersistence(RuntimeStatusDetailService.java:534)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailService.writeRuntimeStatusDetail(RuntimeStatusDetailService.java:509)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailMessageBean.onMessage(RuntimeStatusDetailMessageBean.java:78)
 at weblogic.ejb.container.internal.MDListener.execute(MDListener.java:438)
 at weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:361)
 at weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:297)
 at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:5107)
 at weblogic.jms.client.JMSSession.execute(JMSSession.java:4778)
 at weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:4173)
 at weblogic.jms.client.JMSSession.access$000(JMSSession.java:127)
 at weblogic.jms.client.JMSSession$UseForRunnable.run(JMSSession.java:5632)
 at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:681)
 at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
 at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
 at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
 at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
 at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:655)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)

Due to this problem, task execution performance is affected, and Last Activity column in View Submitted Task is blank.

Cause

The Numeric Overflow error is due to the runtimestatusdetail12 table's Sequence having reached its max value.

Resolution

To remedy this issue the Task Persistence (TP) database needs to be recreated. There is no way to manually update the sequence value without recreating the tables.

The issue here is due to TP database sequence which has overflowed and we can remedy this problem by rebuilding (creating a new) TP database.

We can create a new schema in the Oracle database and modify the JDBC data source in the application server (JBoss/Wildfly/WebLogic/WebSphere) to point correctly to use the new schema. Upon Identity Manager restart, Identity Manager will recreate TP tables within the new schema.

Modify the TP (task persistence) data source in the standalone-full-ha.xml for JBoss/Wildflay, for example) to point to new task persistence schema/database. The remaining data sources should *not* be touched. 

The steps to rebuild TP database for this purpose are

1. Shutdown Identity Manager
2. Create a new schema (user with same permissions as the old TP database schema)
3. Modify TP data source so it points to new schema
4. Restart IM

Please note that when IM uses a brand new TP database the following happens.
    - View Submitted Task page will not show previous tasks' data contained in old TP database.
    - Tasks that were running in progress before using the new TP database will need to be recovered. So please note down those tasks before switching to new TP database and consider recovery plan.
    - Task Schedules will be removed. So please note down any task schedules and organize to configure the schedules again after switching to new TP database.