Data_id limitations on 1.x and 2.x versions of symmetric

The sym_data table has a data_id column that is simply a unique id for that sym_data row. This column is incremented by one for each new sym_data row. In older versions of SymmetricDS, the data_id column was an integer type. In certain high volume scenarios, you could hit the integer limit on the data_id column for how big it can be. This results in errors like this in the log (this is a DB2 example):

2012-12-22 06:43:38,585 ERROR [DataLoaderService] [qtp30961619-20] Failed to load batch 00034-649785 because: PreparedStatementCallback; uncategorized SQLException for SQL [insert into DB2INST1.TR_TRN(ID_STR_RT,ID_WS,DC_DY_BSN,AI_TRN,ID_OPR,TY_TRN,TS_TM_SRT,TS_TRN_BGN,TS_TRN_END,FL_TRG_TRN,FL_KY_OFL,SC_TRN,ID_EM,INF_CT,TY_INF_CT,ID_RPSTY_TND,ID_TLOG_BTCH,ID_BTCH_ARCH,SC_PST_PRCS,TS_CRT_RCRD,TS_MDF_RCRD,FL_TRE_TRN,BAG_DON,OFFER_CD) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [09000]; error code # [-723]; DB2 SQL Error: SQLCODE=-723, SQLSTATE=09000, SQLERRMC=DB2INST1.SYM_ON_I_FOR_RCPT_TR_TRN_CRP;-359;23522;, DRIVER=3.57.82; nested exception is com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-723, SQLSTATE=09000, SQLERRMC=DB2INST1.SYM_ON_I_FOR_RCPT_TR_TRN_CRP;-359;23522;, DRIVER=3.57.82
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into DB2INST1.TR_TRN(ID_STR_RT,ID_WS,DC_DY_BSN,AI_TRN,ID_OPR,TY_TRN,TS_TM_SRT,TS_TRN_BGN,TS_TRN_END,FL_TRG_TRN,FL_KY_OFL,SC_TRN,ID_EM,INF_CT,TY_INF_CT,ID_RPSTY_TND,ID_TLOG_BTCH,ID_BTCH_ARCH,SC_PST_PRCS,TS_CRT_RCRD,TS_MDF_RCRD,FL_TRE_TRN,BAG_DON,OFFER_CD) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [09000]; error code [-723]; DB2 SQL Error: SQLCODE=-723, SQLSTATE=09000, SQLERRMC=DB2INST1.SYM_ON_I_FOR_RCPT_TR_TRN_CRP;-359;23522;, DRIVER=3.57.82; nested exception is com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-723, SQLSTATE=09000, SQLERRMC=DB2INST1.SYM_ON_I_FOR_RCPT_TR_TRN_CRP;-359;23522;, DRIVER=3.57.82
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
...

The first -723 error simply says this:

During execution of an UPDATE, MERGE, INSERT, or DELETE statement, a trigger was activated. One of the triggered SQL statements received an SQL error condition.

The -329 error is the real culprit:

THE RANGE OF VALUES FOR THE IDENTITY COLUMN OR SEQUENCE IS EXHAUSTED

The long term solution to this problem is to upgrade SymmetricDS Pro to a newer version. For the short term, you can alter the data_id column on both sym_data and sym_data_event to be type bigint. Here are the steps for DB2 (this would be different for each database dialect).

1. Get the current max value of data_id in the existing sym_data table. Keep the value, you'll need it later.
   select max(data_id) from sym_data_event.

2. Drop the identity attribute on sym_data data_id column
   alter table sym_data alter column data_id drop identity

3. Turn data capture off for sym_data
   alter table sym_data data capture none

4. Change the column from int to bigint
   alter table sym_data alter column data_id set data type bigint

5. Set data_id to be an identity column again
   alter table sym_data alter column data_id set generated always as indentity

6. Set the initial seed value for the identity column
   alter talbe sym_data alter column data_id restart with <value from step #1 + 1>

7. Turn data capture back on
   alter table sym_data data capture changes

At this point sym_data now has its data_id column as a bigint. Next we must deal with sym_data_event.

8. Turn data capture off for sym_data_event
   alter table sym_data_event data capture none

9. Change the column from int to bigint
   alter table sym_data_event alter column data_id set data type bigint

10. Turn data capture back on
    alter table sym_data_event data capture changes

11. You must also alter select REF_DATA_ID on sym_data_ref to be a BIGINT versus an INT

Now both tables have data_id as bigint. On DB2, you now must reorg both tables or you will get errors on inserts/updates or deletes

The last step is to tell SymmetricDS NOT to update the schema automatically. By default symmetric will look at its own operational tables, and if any of them are different than what it expects, it will fix them. In this case, you don't want to "fix" the bigint back to int, so you must add the following line in your symmetric properties file:

auto.config.database=false

This will tell symmetric not to auto update the tables when it sees something different than it expects.

Note that the alter tables and reorgs can take a fairly significant amount of time depending on the number of rows in sym_data and sym_data_event. In this example, 100 million rows took approximately 1 hour to alter table and another hour to reorg. That is variable depending on database, hardware and other factors.