Workflow fails with Arithmetic overflow error for data type smallint, value = 32768

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

Issue/Introduction

Workflows are failing and the following errors appear in the Identity Manager application server log:

2018-04-02 10:53:25,352 ERROR [com.workpoint.server.pojo.GenericServerBean] (Query_alertq#AlertQ1#WPDS) Arithmetic overflow error for data type smallint, value = 32768.

2018-04-02 10:53:25,352 ERROR [com.workpoint.server.pojo.GenericServerBean] (Query_alertq#AlertQ1#WPDS) Arithmetic overflow error for data type smallint, value = 32768. 2018-04-02 10:53:25,352 ERROR [org.jboss.as.ejb3.invocation] (Query_alertq#AlertQ1#WPDS) JBAS014134: EJB Invocation failed on component MonitorPvt_EJB for method public abstract com.workpoint.common.data.table.AlertMonitorTable[] ...

com.workpoint.server.pojo.MonitorPvt.queryAlertQMonitors(java.lang.String,java.lang.String,java.math.BigDecimal,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String[],java.lang.String,boolean,java.lang.String,boolean,int,boolean) throws com.workpoint.server.ejb.WorkPointEJBException: com.workpoint.server.ejb.WorkPointEJBException: An SQL Exception has occurred. Please see the server logs for details.

Environment

Release: 14.x
Component: IDMGR

Cause

A limit has been reached on the EVALUATION_COUNT field in one of the monitor queue tables. The maximum count, according to Workpoint Support is 32765, a hard limit, so the count needs to be reset. This is likely due to one or more specific processes or activities that organically runs up the count to its limit.

 

This is an issue with the third party Workpoint product integrated with CA Identity Manager (IM) and CA Governance Manager (IG). 

For Oracle SQL the problem exists in Workpoint 3.5.2 Version 20140214 Patch 004 (and earlier), which ships with CA IM 14.0 and 14.1 - EVALUATION_COUNT has been changed to Number(38,0) in later versions.

For MS SQL problem is ongoing due to no changes being made to database schema and it using smallint for EVALUATION_COUNT. 

Resolution

Roll back the EVALUATION_COUNT field in the monitor queue tables to 1 if it reaches 32765 to prevent the database error.

These are the tables to check:

WP_MAIL_MONITOR
WP_ALERT_MONITOR
WP_EVENT_MONITOR
WP_JOB_MONITOR
WP_SCRIPT_MONITOR

Please ask your database administrator to check the EVALUATION_COUNT as well as ROW_VERSION columns in the above tables for any values approaching 32765 and replace those values with 1.

Additional Information

This issue has been fixed for Oracle endpoint in CA Identity Manager via the 14.2 CP5 Cumulative Patch and 14.3 CP1.