Hi Team, 

I noticed that the sequence ID in the database is not enabled with Cycle=N, which means auto-reset is disabled. I am curious about what happens when the sequence ID reaches its maximum limit. I have successfully mapped the SI components and identified them from the database table (as shown below, with values entered manually).

My key questions are:

1. What happens when the sequence ID reaches the maximum limit?

2. What happens when the sequence ID is manually reset?

3. Will manually resetting the sequence ID impact the performance of Sterling Integrator?

I couldn't find any IBM articles related to manually resetting sequence IDs that are used by Sterling Integrator components.

Additionally, I attempted archival purging and ran the datasweeper in SI, but instead of reducing the sequence ID growth, the sequence ID increased further.

Hello,

We ran this in DB2 and it reset it when one ran out, so it was reset

alter table MBX_MESSAGE_GUID alter NEXT_MESSAGE_ID RESTART WITH 1

alter table ACT_SESSION_GUID alter NEXT_SESS_ARC_ID RESTART WITH 1

Thanks
Attila

Hi @Attila Toke,

Thank you very much for your response. After Manual reset, did you find any performance related issues like slowness or any other impact?

Thanks in advance,

Abul Hasan Shadhali

alter table MBX_MESSAGE_GUID alter NEXT_MESSAGE_ID RESTART WITH 1

alter table ACT_SESSION_GUID alter NEXT_SESS_ARC_ID RESTART WITH 1

Thanks
Attila

Hi Abul,

No issues or impact was noticed at all.

Thanks,

Attila

alter table MBX_MESSAGE_GUID alter NEXT_MESSAGE_ID RESTART WITH 1

alter table ACT_SESSION_GUID alter NEXT_SESS_ARC_ID RESTART WITH 1

Thanks
Attila

Hi Attila, 

Thank you very much for your response. It's reassuring to hear that a manual reset will not have any impact. I now have another query:

For resetting the sequence ID for the table ACT_SESSION_GUID, should we clear or delete its data from the tables ACT_SESSION_GUID and ACT_SESSION? Are there any other related tables whose data needs to be cleared or deleted for data integration purposes?

Once the data in all relevant tables is cleared or deleted, should we proceed with resetting the sequence ID for the table ACT_SESSION_GUID?

Thanks in advance for your response.

With Regards, 

Abul Hasan Shadhali

alter table MBX_MESSAGE_GUID alter NEXT_MESSAGE_ID RESTART WITH 1

alter table ACT_SESSION_GUID alter NEXT_SESS_ARC_ID RESTART WITH 1

Thanks
Attila

Hi Abul.

During our system patch window, while SFG is down we always run this, to clear them out.

TRUNCATE TABLE ACT_SESSION_GUID DROP STORAGE CONTINUE IDENTITY IMMEDIATE;
TRUNCATE TABLE DATA_FLOW_GUID DROP STORAGE CONTINUE IDENTITY IMMEDIATE;
TRUNCATE TABLE DMI_FACT_GUID DROP STORAGE CONTINUE IDENTITY IMMEDIATE;
TRUNCATE TABLE MBX_MAILBOX_GUID DROP STORAGE CONTINUE IDENTITY IMMEDIATE;
TRUNCATE TABLE MBX_MESSAGE_GUID DROP STORAGE CONTINUE IDENTITY IMMEDIATE;

I would truncate them and then reset the identity columns.

Thanks,

Attila

alter table MBX_MESSAGE_GUID alter NEXT_MESSAGE_ID RESTART WITH 1

alter table ACT_SESSION_GUID alter NEXT_SESS_ARC_ID RESTART WITH 1

Thanks
Attila

Hello Abul, as far as I know, there is also the identity in ACT_SESSION_GUID table that has a maximum of 999999999.