Make campaign ID (primary key) start at least from 10000

[salvdangelo]

Hi,

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,

Salvatore

florentlb​

Amit_Kumar​

Adhiyan​

Vipul Raghav​

Adobe Campaign​

[Jean-Serge_Biro]

Hi Salvatore,

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.

Regards
J-Serge

[Jean-Serge_Biro]

Please have also a look on xtkNewId (and GetNewIds):

https://docs.campaign.adobe.com/doc/AC/en/browseAC.html

https://docs.campaign.adobe.com/doc/AC/en/PRO_Database_maintenance_Application_objects.html

[Adhiyan]

Hi Salvatore,

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.

Regards,

Adhiyan

[salvdangelo]

Hi Adhiyan,

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.

[Jean-Serge_Biro]

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.

But you must take care that you must not alter the sequence value targeted with instruction given by Adhiyan (Oracle syntax, but similar ones in other RDBMS) for sequences managed by Adobe Campaign. So please have a look in your targeted schema, if the column to update is autopk=true, then use GetNewIds stored procedure (or equivalent Javascript function). And have a look on the stored procedure, you will understand;) But keep in mind that Adobe Campaign internal sequences share most of times between several objects.

Regards
JS

[Adhiyan]

Hi Jean-serge,

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

Regards,
Adhiyan

[ishanishah]

Hi Adhiyan​,

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.

Thanks,

Ishani

[Adhiyan]

Hi Ishani,

Technically this should be possible but I am not sure if sequence creation is possible with type as UUID.

Generally it's a number and it's assigned values automatically.

Also , have not seen such a configuration yet. Might as well you want to make that column as unique or not Null and populate them with values sequentially instead of making that as PK.

Regards,

Adhiyan

[florentlb]

Hi Adhiyan​,

Can you follow up on the questions about your previous answers?

Thanks a lot!

Salvatore, please let us know if you managed to achieve this.

Florent

[Adhiyan]

Hi Salvatore,

These are the steps you can use for POSTGRES to generate a new function and sequence.

1. In the console, navigate to "Administrator/ Configuration/SQL scripts" and create a new SQL entity

2. Put below SQL code into this new element

Postgresql

-- Log: Creating specific Id generator for customId

SELECT CreateSequenceIfNecessary('NeoCustomId', '1000', 'cache 1000');

create or replace function GetNeoCustomId() returns integer as '

declare

iId integer;

begin

select into iId NextVal(''NeoCustomId'');

return iId;

end;

' language plpgsql

;

create or replace function GetNewNeoCustomIds(integer) returns text as '

declare

strIds text;

i integer;

iId integer;

begin

strIds = '''';

for i in 1 .. $1 loop

select NextVal(''NeoCustomId'') into iId ;

if i > 1 then

strIds := strIds || '','';

end if;

strIds := strIds || cast(iId as text);

end loop;

return strIds;

end;

' language plpgsql

;

3. Run the script from the console

4. It should create a new sequence in the DB along with the function to deployed by AC

5. In the schema, edit the root "element" node as follows to use the new sequence.

<element autopk="true" pkSequence="NeoCustomId" name="abc" pkgStatus="never">

Note: The mandatory fields here are autopk and pkSequence.  

Summary

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

For example

PostgreSQL:   xtk:postgresql-nldb.sql ,  nms:postgresql-nms.sql

Oracle         :   xtk:oracle-nldb.sql ,   nms:oracle-nms.sql

Let me know if this helps

Regards,

Adhiyan