SQL Arithmetic Overflow Error When Inserting New i5 Job Through JIL or WCC

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent

Issue/Introduction

When attempting to insert a new i5 job into WAAE through JIL or WCC, an error similar to the following is encountered...

CAUAJM_E_18802 Error from SQLExecute() Failed with SQL_ERROR.
CAUAJM_E_18601 SQLSTATE: 22003, Native error: 220, Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for data type smallint, value = 32815.
CAUAJM_E_18601 SQLSTATE: 01000, Native error: 3,621, Message: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Environment

WAAE 11.3/11.3.5/11.3.6 pre-SP3Windows/UNIXSybase or MSSQL Event Server only

Cause

There is a special table in the WAAE database for i5 jobs called "ujo_i5_job". In earlier releases of WAAE 11.3.x, there are two columns in this table that are set as datatype SMALLINT. The numeric value that is inserted into these columns is derived from a counter elsewhere in the database. Over time, the counter can grow too large for a SMALLINT datatype. When this occurs, new i5 job inserts will start receiving the error message described above.

Resolution

This issue was resolved in 11.3.6 SP3 by converting the two columns to datatype INT so that it would allow larger values.

If running with a release older than 11.3.6 SP3, it is always recommended to upgrade to the latest service pack to resolve issues such as this where a fix is available. However, in the older releases, this particular issue can be circumvented by performing the following steps...

1. Stop the scheduler(s) and app server(s)

2. Run the following sql commands in the WAAE database. (If using dual databases, run these in both)

ALTER TABLE ujo_i5_job MODIFY job_parms INT

ALTER TABLE ujo_i5_job MODIFY others INT

3. Restart the scheduler(s) and app server(s)