search cancel

MQ tables - Avoiding Arithmetic Overflow Error

book

Article ID: 201871

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

When current MQ table (MQ1WP or MQ1OWP for example) identities are getting close to MSSQL server INT data type limit 2,147,483,647, what is the correct course of action?

What is a way to avoid arithmetic overflow error?

The error could look something like:

U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: c5aca0  DB-HDBC: c5ad80
U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
UCUDB - Status: '22003' Native error: '8115' Msg: 'Arithmetic overflow error converting IDENTITY to data type int.'
UCUDB - Status: '01000' Native error: '3606' Msg: 'Arithmetic overflow occurred.'
UCUDB Ret: '3590' opcode: 'INPK' SQL Stmnt: 'INSERT INTO MQ[tablename]
U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR   ', '01000', 'Arithmetic overflow occurred.'

Environment

Release : All

Component : AUTOMATION ENGINE

Database: SQL Server

Resolution

It should be possible to reseed the identity of the MQ tables with a command like:

DBCC CHECKIDENT (MQ1WP, RESEED, 1)

Before doing this, a DBA will need to confirm that there are no low identity MQ1WP records that have been hanging there for a long time.  
The IDENTITY field cannot detect existing PKs with a value it is about to use next, if there is one it will lead to an error message.  There shouldn't be any that low, but again, that needs to be confirmed first.  

A DBA will need to be consulted to help with this and should also be able to determine specifics, including if the reseed can be done while Automic is running.