IBM Support

Arithmetic overflow errors occur when using an IBM i2 iBase database with Alerting enabled

Troubleshooting


Problem

When performing an action in IBM i2 iBase (for example saving new entities, running reports, investigating alerting etc), an Arithmetic overflow error occurs.

Symptom

One symptom can be that you find you are unable to save new entities in iBase, or that you are unable to use alerting successfully.



Note that the error can manifest itself in different ways. The important part of the error is the 'Arithmetic overflow error converting IDENTITY to data type int.'  Following is an example of the text of the error.

Arithmetic overflow error converting IDENTITY to data type int.
INSERT INTO dbo.YourTable_ (Unique_ID,Create_Date,Create_User,My_Fields_) VALUES(REFERENCE01,01/01/2017,SampleUser,SampleField)

Error #3159 occured in:
Microsoft OLE DB Provider for SQL Server
idDBEngine:ExecuteSQL
idDBEngine:SaveRecord(YourTable_:REFERENCE01)
CFBRecord:SaveRecord
CDBRecord:Save
FDatasheet:SaveDatasheet

 

Cause

Certain features in IBM i2 iBase make use of tables in Microsoft™ SQL Server that use IDENTITY columns. These IDENTITY columns are assigned to use the data type 'int'. The int data type can represent integers in the range from negative 2,147,483,648 to positive 2,147,483,647, inclusive. When a table, record update request exceeds this 2,147,483,647 limit, it causes the error reported.

Resolving The Problem

Locating the affected table
  1. Ensure all users are signed out of IBM i2 iBase User and Designer
  2. Open and login as an Administrator to SQL Server Management Studio
  3. Make sure you take a backup of the database before you make any further changes
  4. Select the affected database from the dropdown list at the top of the page. (In this example we are selecting the User_Guide database).
  5. Click "New Query"
  6. Enter the following SQL query into the New Query window, and "Execute" the query 
      SELECT IDENT_SEED(table_name) AS Seed, IDENT_INCR(table_name) AS Incr, IDENT_CURRENT(table_name) AS Current_Value,  	table_name FROM INFORMATION_SCHEMA.TABLES   		WHERE OBJECTPROPERTY(object_id(table_name),'TableHasIdentity')=1  		ORDER BY IDENT_CURRENT(table_name) DESC
    The query lists all tables that contain an IDENTITY property and displays the current value.   The table with the problem has a current value of 2147483647.  Following is an example screen capture of what the output can look like.  In the screen capture, the _AL_Edit_Log table has a current value at the maximum value possible to store in an INT.
  7. image 3818
  8. For each table with a current value = 2147483647, run the following script, substituting '_AL_Edit_Log' with the actual name of the affected table. This script will modify the IDENTITY column in your database to use a BIGINT rather than the standard INT value. 
      DECLARE @FixTable_Name AS NVARCHAR(50) = '_AL_Edit_Log'  DECLARE @Constraint_Name NVARCHAR(50), @FixColumn_Name AS NVARCHAR(50)    SET @Constraint_Name = (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @FixTable_Name)  SET @FixColumn_Name = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @FixTable_Name)    EXEC('ALTER TABLE ' + @FixTable_Name + ' DROP CONSTRAINT ' + @Constraint_Name)  EXEC('ALTER TABLE ' + @FixTable_Name + ' ALTER COLUMN ' + @FixColumn_Name + ' BIGINT')  EXEC('ALTER TABLE ' + @FixTable_Name + ' ADD CONSTRAINT ' + @Constraint_Name + ' PRIMARY KEY (' + @FixColumn_Name + ')')  GO


[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXW43","label":"i2 iBase"},"ARM Category":[{"code":"a8m50000000Ci9vAAC","label":"i2 iBase and i2 Analyst's WorkStation->Database->Error Messages"}],"ARM Case Number":"TS003700683","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.9.x","Line of Business":{"code":"LOB24","label":"Security Software"}}]

Document Information

More support for:
i2 iBase

Component:
i2 iBase and i2 Analyst's WorkStation->Database->Error Messages

Software version:
8.9.x

Operating system(s):
Windows

Document number:
289413

Modified date:
01 June 2020

UID

swg21997139

Page Feedback