I would like to make sure that all campaign (operation) IDs are over 10.000 (since we import historical campaigns from a different source having IDs from 1 to about 9600).
Could you tell me step by step, I should I do this ?
I extended the "operation" schema with a custom one, if may help.
Someone suggested me to use a sequence for the primary key, but I never used it.
Thanks for your help,
Please may you explain a bit more why you must force the ID values in your import job, instead of letting Adobe Campaign import system increment automatically the primary key value of the targeted standard objects?
And will you import the data with Adobe Campaign tools (workflow activity or manually with Import model treatment)? Or with the help of an external ETL (Extract / Transform / Load) tool ?
Some standard Adobe Campaign tables (schemas) use autopk=true so it increments automatically the primary key value (most of times internal=true ID generated but can be other fields as well).
The incremental counter is managed through sequences schemas, see xtkNewId in documentation, and these sequences values are shared for several tables/objects.
For instance, the folders (nodes) of the Adobe Campaign tree-list use such xtkNewId.
And even with an empty new instance, the values must start over 1001.
So you can import the historical campaigns (operations/deliveries) with standard AC tools, and let the system set the ID values accordingly.
If you want to keep the external ID you can store it in an additional field in same schemas (see schemas extension), but not force the default primary key values.
In case of you really need to force standard/default primary key values using sequences (autopk=true), then you must claim for range with GetNewIds procedure, but you won't obtain a range in small values, but after the current max value.
Hope this helps.
I have a working theory on how to achieve this.
In a very simple way , without extending your schema and chaning anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with 10000.
The command would have to run on the DB and the syntax would be something like :
ALTER sequence <Sequence name> MINVALUE 0 MAXVALUE 2147000000 RESTART WITH 10000 CACHE 1000 CYCLE;
This will reset the Sequence to restart with 10,000 and will start assigning IDs incrementally after 10,000.
Please bear in mind though , that this change will have an effect across all schemas which uses this sequence and the change will not only be limited to the operation schema.
If you want to make the change effective only in this schema you will have to implement a couple of more complex changes which include
1>. Create a new sequence on DB and also create a custom function for generating IDs for this sequence. Make sure to have the "restart with" parameter set at 10000 or after creating the Sequence , use the ALTER command to reset the sequence to start with 10,000.
2>. Go to your operation schema extend it and make sure that autoPk="true" and pkSequence="<new_sequence>" is set.
Your operation schema would then start using this new sequence with IDs starting from 10,000.
Thank you very much, I was searching something like that acting (if possibile) directly on the SQL tables
Just to be clear <Sequence name> must be equal to <new_sequence> ?
And could you tell me where in Adobe Campaign a should define the new sequence and the function that generates the IDs for the new sequence ?
Jean-Serge Biron Thanks a lot!
However this is the scenario :
As a requirement we have imported historical campaigns from an outer data source. We will create our campaigns in AC and we'll export both historical campaign and new custom AC campaigns through a Salesforce connector in the same 'SF Campaign' table having a unique primary key valid for both types of campaigns. Unluckily we cannot ask any modifications on the SF entity.
Hi Salvatore & Adhiyan,
Actually there are 2 main cases:
1. sequence managed by the RDBMS (database) itself
2. sequence managed by Adobe Campaign (autopk=true)
Of course I used to use both depending on context.
These are the steps you can use for POSTGRES to generate a new function and sequence.
Note: The mandatory fields here are autopk and pkSequence.
1. In the example, we created sequence NeoCustomId (where Neo and CustomId are namespace and label respectively)
Basically, you need two things
- call CreateSequenceIfNecessary() function to generate the sequence
- Create the function GetNew<sequence full name>s (for instance GetNewNeoCustomIds) which allows to distribute the IDs to column afterward
2. The OOB sequences enable the cache usage
Actually, this value is
- 30 for XtkNewId (it should be for the small tables)
- 1000 for the others (broadLog, trackingLog, and the big tables)
The third argument of the CreateSequenceIfNecessary() function allows to do this (empty means no cache application). We recommend to use this to get better performance.
3. You could always reference to the SQL functions mentioned in the OOB entities <engine>-nldb.sql and <engine>-nms.sql
PostgreSQL: xtk:postgresql-nldb.sql , nms:postgresql-nms.sql
Oracle : xtk:oracle-nldb.sql , nms:oracle-nms.sql
Let me know if this helps
The ALTER SEQUENCE command can be used to make changes safely even for the sequences managed by Campaign. It's tried and tested. We are actually executing the Change in the DB directly where sequences are stored , so it's the same as running it directly on the DB
I wanted to check if we can add pksequence as type uuid (unique identifier). Is it possible to add a SQL script and create sequence of uuids and add it to the operation table?
Do you know if it would affect any other table?
Appreciate any help.