Broadlog Resequencing in Adobe Campaign Classic

 As we all know, the max value for any auto primary key field in Adobe is limited by the max of int which is 2.15B [[+/-) 2,147,483,647]]. As ACC doesn't support bigint as the auto pk column data type, you have to recycle the number every time it reaches near to that 2.14B mark. 

Now Broadlog tables being the most heavily used table in ACC, we observe the issue often with this table but this can happen with other tables as well like Tracking and so on.

It is not difficult to recycle the value of the sequence in ACC, but most of the time the BL and TL data is shared with downstream systems. So, once you reset the sequence, you have to make sure the downstream system knows about it and handle it properly without overwriting the existing historical records. And that is where these types of challenges are faced

• How do we inform downstream when ACC is resetting its sequence
• If downstream is managing the uniqueness of the records, then they have to prefix something to make sure it is unique all the time like Bl Id "1" becomes "100,000,000,001" where 10 is the prefix and once reset, 11 becomes the prefix and thus the next "1" post recycling becomes "110,000,000,001"
• If there are more than one BL, then the above-mentioned prefixing becomes even more complex
• If there are multiple downstream systems, then all of them have to apply the same logic
• After resequencing, there will be a period of time when the Broadlogs will keep on getting updates like a soft bounce was delivered successfully. So, for few days, the downstream will get both the high ids (near to 2.1B) and log ids (near to 1). So, the logic to update records has to take think about multiple prefixes at a given time (eg: 10 and 11 in the above example in 2nd bullet point)

Key Design Decision:

• Let Adobe manage the unique id and share it with all the down streams
• Adobe should share the unique id itself or enough information for the downstream to calculate the unique id easily

Solution:

I was thinking about solutions like managing few tables to keep track of the current sequence per table (as I had multiple BLs) and have a current prefix against that, something similar to below and let the Admins manage it while resequencing and update the required prefix. Adobe will always sum the prefix with the actual BLId and share it with downstream.

ID	Name	Type	Sequence Name	Current Prefix	No of records	Last Verified
1	nms:broadLogRcp	BL	NmsBroadLogRcpId	1,020,000,000,000	1,600,000,232	Date time
2	cus:broadLogProspect	BL	cusBroadlogProspectId	2,010,000,000,000	50,000
3	nms:trackingLogRcp	TL	NmsTrackingLogId	1,010,000,000,000	345,430,980
4	nms:exclusionLogRcp	EL	NmsExcludeLogRcpId

But The challenge was with the high id and low id at the same time as explained above. So if we blindly sum the prefix with the id, we will be in trouble and for that we need the high ids to be summed with the previous prefix and low ids with the current prefix and that also for few days and after that everything will be prefixed with the current prfix - REALLY, too much to handle properly with perfection

So?

Why don't we add a new column in the BL with Bing Int and tie it up with a sequence to make sure it is auto incremented? Let's do it

• Update the Broadlog table to add the Unique Id column

<attribute advanced="true" desc="Unique Id for Broadlog even with resequencing"

               label="Unique Broadlog Id" name="uniqueBroadlogId" type="int64"/>

This will create a column like:

ALTER TABLE NmsBroadLogRcp

     ADD COLUMN biUniqueBroadlogId BIGINT Default 0 NOT NULL;

ANALYZE NmsBroadLogRcp;

Change that statement according to your database

• Postgress SQL before version 10
• ALTER TABLE NmsBroadLogRcp ADD COLUMN biUniqueBroadlogId BIGSERIAL NOT NULL;
• Run a simple workflow to copy the existing PK Ids into the Unique Bl ID column and note the max value
• ALTER SEQUENCE NmsBroadLogRcp_biUniqueBroadlogId_Seq RESTART WITH 4094001; -- My Max value was 409400, which in your case will be most likely near to 2.1B
• Sequence for Serial column is created automatically with the naming as <tablename>_<colname>_seq
• Change the sequence to Max of Bigint value
• ALTER SEQUENCE NmsBroadLogRcp_biUniqueBroadlogId_Seq RESTART WITH 2147483646;
• Alternatively you can do something similar to this as well
  SELECT setval(pg_get_serial_sequence('public.NmsBroadLogRcp', 'biUniqueBroadlogId'), 2147483646);
• Change to 5Trillion and test
• Postgress SQL 10 or above
• You can use Identity Column this time
• Adobe Set Default a 0, so it needs to be removed
  ALTER TABLE NmsBroadLogRcp ALTER COLUMN biUniqueBroadlogId DROP DEFAULT;
• ALTER COLUMN biUniqueBroadlogId
  ADD GENERATED ALWAYS as IDENTITY (START WITH 1000000 INCREMENT BY 1);
• Change the start index of the identity to Max of Int
  ALTER TABLE NmsBroadLogRcp 
      ALTER COLUMN biUniqueBroadlogId
          SET START WITH 2147483646
          SET INCREMENT BY 1;
• Alternatively
  
• ALTER TABLE NmsBroadLogRcp 
      ALTER COLUMN biUniqueBroadlogId
          RESTART WITH 50000000000000;


• Oracle Before 12c
• Oracle doesn't support Identity Column until Version 12c
• So create a sequence and setup an insert trigger for the biUniqueBroadlogId
• CREATE SEQUENCE NmsBroadLogRcp_biUniqueBroadlogId_seq
   START WITH     10000
   INCREMENT BY   1;
• CREATE OR REPLACE TRIGGER NmsBroadLogRcp_on_insert
    BEFORE INSERT ON NmsBroadLogRcp
    FOR EACH ROW
  BEGIN
    SELECT NmsBroadLogRcp_biUniqueBroadlogId_seq.nextval
    INTO :new.biUniqueBroadlogId
    FROM dual;
  END;
• Oracle 12c or Above
• Oracle has introduced Identity Column
• ALTER TABLE NmsBroadLogRcp 
    MODIFY biUniqueBroadlogId NUMBER(20) GENERATED BY DEFAULT ON NULL AS IDENTITY 
    START WITH 10000 INCREMENT BY 1;

Note: I am yet to get an opportunity to try with Oracle, so the SQLs might be little off

For all the cases, you should create Unique Index on the unique Id Column

As we know ACC uses a sequence behind the scenes to create these auto incrementing values and thus, we should follow few rules to avoid this from happening very often like

• Have table specific sequences almost every time.
• As Broadlog, Tracking Logs and Exclusion Log tables are generated using a template, it uses the same sequence by default. Go and change the sequence by proving the name explicitly
• Tracking log and Exclusion log uses the same sequence by default. If you are having seperate table for Exclusion, it is better to have its own sequence than using the sequence from broadlog
• As the default BL seq is used by all OTB BLs, better use your own sequence for BroadlogRcp table and same for Tracking and Exclusion for Rcp (OTB) schemas