IBM Support

'Error : Arithmetic overflow error converting IDENTITY to data type int' when trying to publish Data

Troubleshooting


Problem

User schedules a published Data Mart. User checks the batch queue afterwards. There is an error message.

Symptom

Batch Queue:
<1234> Finished with Error Process Cube abcdef <date> etc. etc.

Double-click on this to see:

Details Section <1234>
Text
Error: Arithmetic overflow error converting IDENTITY to data type int.

Event Viewer on Application Server:

#1
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 14/02/2008
Time: 07:10:59
User: N/A
Computer: APPSERVER
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 2592 ,Logged: Error occured at 14/02/2008 8:10:59 in FrSrvFunc, Error No=-2147217833, Source=Server function:Dbp_ExecuteImmediate(..) , Description=Arithmetic overflow error converting IDENTITY to data type int., HelpFile= HelpContext=0

#2
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 14/02/2008
Time: 07:10:59
User: N/A
Computer: APPSERVER
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 2592 ,Logged: Error occured at 14/02/2008 8:10:59 in FrSrvFunc, Error No=-2147217833, Source=Server function:Dbp_InsertSqlRemote(..) , Description=Arithmetic overflow error converting IDENTITY to data type int., HelpFile= HelpContext=0

#3
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 14/02/2008
Time: 07:10:59
User: N/A
Computer: APPSERVER
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 2592 ,Logged: Error occured at 14/02/2008 8:10:59 in FrSrvFunc, Error No=-2147217833, Source=Server Sub:Db_PushSqlRemore(..) , Description=Arithmetic overflow error converting IDENTITY to data type int., HelpFile= HelpContext=0

#4
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 14/02/2008
Time: 07:10:59
User: N/A
Computer: APPSERVER
Description:
The VB Application identified by the event source logged this Application FrangoOLAPServer: Thread ID: 2592 ,Logged: Error occured at 14/02/2008 8:10:59 in FrOLAPServer, Error No=-2147217833, Source=FrOLAPServer.OlapNykomoT.GenerateValues#Server Sub:Db_PushSqlRemore(..) , Description=Arithmetic overflow error converting IDENTITY to data type int., HelpFile= HelpContext=0

Cause

Microsoft SQL server is being asked to increase the value of a table column's key to be larger than the maximum value allowed by the default setting ('int').

More Information:
The Controller database contains a table 'xocubefact' which has an identity. The Controller Data Mart publish process deletes and inserts many rows on a regular basis. This causes the key to grow.

  • There is a default limit (for the 'int' setting) which means that the largest integer value possible (for this key) is 2,147,483,647
    • After a very large number of data mart publishes, it is possible for this key to grow larger than this limit. When the limit is exceeded, the error message occurs.
  • By modifying this to 'bigint', this allows many more (up to 9,223,372,036,854,775,807) rows to be inserted and then deleted].
In some customer's environments, it is also necessary to change of the column 'INO' of the 'DMFACT' table.

Resolving The Problem

TIP: Depending on the customer environment, you may only need to perform one of the following changes. However, to be 100% sure (and to be proactive for the future) it is recommended to make both changes:


    (1) Open the main Controller application repository database. Modify the table 'xocubefact' so that the column 'rowid' datatype is changed (from 'int') to 'bigint'.

    (2) Open the Data Mart database (which typically is a separate database, but may be the same as the Controller application repository database). Modify the table 'DMFACT' so that the column 'INO' datatype is changed (from 'int') to 'bigint'.


Steps:


IMPORTANT: Before proceeding, create a complete backup of the relevant Controller & Data Mart databases (as a precaution).
    SQL 2000:
    1. Logon to the SQL server as an Administrator
    2. Launch "SQL Enterprise Manager"
    3. Locate the Controller database (for example 'Controllerlive')
    4. Expand database, and open the section 'tables'
    5. Locate table 'xocubefact'
    6. Right-click on 'xocubefact' and choose 'Design Table'
    7. Scroll down the column "Column Name" until you find the row corresponding to "rowid"
    8. To the right of 'rowid' (inside the column 'Data Type') you will find the value 'int'
    9. Change the value (from the original 'int') to the new value 'bigint'
    10. Save changes and close this screen
    11. Afterwards, repeat the above process but this time modify the table 'DMFACT' so that its column name 'INO' is changed to be 'bigint'.
    12. Test
    TIP: For printscreen of the above, see attached document '1347703 - Arithmetic overflow error converting IDENTITY to data type int.pdf'.
    SQL 2005 / 2008:
    1. Logon to the SQL server as an Administrator
    2. Launch "SQL Server Management Studio"
    3. Locate the Controller database (for example 'Controllerlive')
    4. Expand database, and open the section 'tables'
    5. Locate table 'xocubefact'
    6. Right-click on 'xocubefact' and choose 'Design'
    7. Scroll down the column "Column Name" until you find the row corresponding to "rowid"
    8. To the right of 'rowid' (inside the column 'Data Type') you will find the value 'int'
    9. Change the value (from the original 'int') to the new value 'bigint'
    10. Right-click on the name/label of the tab (that corresponds to the screen that you are using to edit to the table) and choose 'close'
    11. When prompted, choose 'yes' to save changes
    12. When prompted, choose 'yes' to save the tables.
    13. Afterwards, repeat the above process but this time modify the table 'DMFACT' so that its column name 'INO' is changed to be 'bigint'.
    14. Close "SQL Server Management Studio"
    15. Test.



    Note: if the following message is displayed when saving the changes:

    Saving changes is not permitted. The changes you have made require the
    following tables to be dropped and re-created. You have either made changes to
    a table that can't be re-created or enabled the option Prevent saving changes
    that require the table to be re-created.

    xocubefact

    Change the option to allow changes, Tools Menu > Options > Designers, un-select 'Prevent saving changes that require table re-creation' and repeat the changes.

1347703 - Arithmetic overflow error converting IDENTITY to data type int.pdf1347703 - Arithmetic overflow error converting IDENTITY to data type int.pdf

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.5.1;8.5;8.4;8.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1038419

Document Information

Modified date:
15 June 2018

UID

swg21347703

Page Feedback