B2B Integration

Sterling B2B Integration

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  Manually resetting a sequence ID in IBM Sterling Integrator

    Posted Tue April 15, 2025 04:11 AM

    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.

    SEQNAME DBOWNER NEXTCACHEFIRSTVALUE MAXVALUE CYCLE TABLE Used
    SQL20020202020202233 APP 373739 999999999 N select * from MBX_MAILBOX_GUID order by next_mailbox_id desc limit 10 0.04%
    SQL20020202020202233 APP 832332321 999999999 N select * from WFD_GUID_UNIQUEID order by next_wfd_id desc limit 10 83.23%
    SQL20020202020202233 APP 1068941 999999999 N select * from DATA_FLOW_GUID order by next_flow_id desc limit 10 0.11%
    SQL20020202020202233 APP 3342233 999999999 N select * from MBX_MESSAGE_GUID order by next_message_id desc limit 10 0.33%
    SQL20020202020202233 APP 455322 999999999 N select * from Calibri order by next_sess_arc_id desc limit 10 0.05%
    SQL20020202020202233 APP 4322 999999999 N select * from SERVICE_DEF_GUID order by current_id desc limit 10 0.00%
    SQL20020202020202233 APP 43244 999999999 N select * from SERVICE_INST_GUID order by current_id desc limit 10 0.00%
    SQL20020202020202233 APP 323435 999999999 N select * from CORREL_KEY order by id desc limit 10 0.03%
    SQL20020202020202233 APP 4334 999999999 N select * from TUNING_PROP_GUID order by next_tun_id desc limit 10 0.00%


    ------------------------------
    ABUL HASAN SHADHALI
    ------------------------------


  • 2.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted Wed April 16, 2025 08:53 AM

    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


    ------------------------------
    Attila Toke
    ------------------------------

    Original Message


  • 3.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted 30 days ago

    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



    ------------------------------
    ABUL HASAN SHADHALI
    ------------------------------

    Original Message


  • 4.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted 30 days ago

    Hi Abul,

    No issues or impact was noticed at all.

    Thanks,

    Attila



    ------------------------------
    Attila Toke
    ------------------------------

    Original Message


  • 5.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted 30 days ago

    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



    ------------------------------
    ABUL HASAN SHADHALI
    ------------------------------

    Original Message


  • 6.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted 29 days ago

    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



    ------------------------------
    Attila Toke
    ------------------------------

    Original Message


  • 7.  RE: Manually resetting a sequence ID in IBM Sterling Integrator

    Posted Wed April 16, 2025 09:21 AM

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



    ------------------------------
    Nicola Vaglica
    ------------------------------

    Original Message