[TIPS&TRICKS] ACC: Postupgrade Warnings - resolve sequences using a loop + GenCreateOrUpdateSchemaSequenceDDL

[#1]

When upgrading from previous versions of Adobe Campaign Classic where the XtkNewId sequence is still used by custom schemas (prior to ACC 18.10), a warning is displayed in the summary log to create/update custom sequences to prevent exhaustion of the OOTB XtkNewId sequence.

Example of postupgrade sequence warning

022-07-19 12:14:38.695Z 00000F28 1 info log Successful completion of document update job
2022-07-19 12:14:38.805Z 00000F28 1 info log =========Summary of the update==========
2022-07-19 12:14:38.820Z 00000F28 1 info log CFG-600022 PROD_AC instance, 3 warning(s) and 0 error(s) during the update.
2022-07-19 12:14:38.820Z 00000F28 1 warning log A custom schema 'cus:fraudFileNameLog' is using a built-in sequence 'XtkNewId'.
2022-07-19 12:14:38.820Z 00000F28 1 warning log A custom schema 'cus:tacticalStopArchieve' is using a built-in sequence 'XtkNewId'.
2022-07-19 12:14:38.820Z 00000F28 1 warning log A custom schema 'cus:tacticalStopBackup' is using a built-in sequence 'XtkNewId'.

To resolve the above warning, you may use the GenCreateOrUpdateSchemaSequenceDDL function to create a custom sequence as well as automatically update the schema; the script is configured as following.

String sqlDDL = GenCreateOrUpdateSchemaSequenceDDL ( String schemaId, String newSequence, String oldSequence )

Add a JS activity in a workflow and paste the following code, replace parameters to match your schema details.

var sqlScript = xtk.builder.GenCreateOrUpdateSchemaSequenceDDL("cus:fraudFileNameLog", "fraudFileNameLog_sequence", "XtkNewId");
xtk.builder.ExecSqlScript('', sqlScript);

Check the workflow audit log for feedback.

In some scenarios, there may be hundreds of custom schemas to update, and so you may opt to create a javascript loop to iterate through each of the schemas and automate the process as below:

Configuration required is to add all custom schemas in the array and the loop will iterate. The script below is configured to update custom schemas using xtkNewId only, there may be some instances where the ootb sequence is different and so you may opt to create an object rather than an array which includes the old sequence for each custom schema and modify the loop to read from a variable containing the old sequence rather than hardcoding it in the function.

/* Add schemas to array */
var schemaArr = 
["cus:tacticalStopBackup","cus:tacticalStopArchieve","cus:fraudFileNameLog"];

var arrayLength = schemaArr.length;

/* Loop, Iterate array and execute function */
for (var i = 0; i < arrayLength; i++) {
   //logInfo(schemaArr[i]);
  sqlScript = xtk.builder.GenCreateOrUpdateSchemaSequenceDDL(schemaArr[i], "auto_" + schemaArr[i].split(':')[1] + "_seq", "XtkNewId");
  xtk.builder.ExecSqlScript('', sqlScript);
}

Generated sequence naming convention configured in the script will be as following

1. auto_tacticalStopArchieve_seq
2. auto_tacticalStopBackup_seq
3. auto_fraudFileNameLog_seq

Notes/References

• The function may fail to configure correctly custom sequences for sequences using negative values, always review generated sequences after execution.
• The function may not correctly update schemas under acc deployment on microsoft sql server, further testing is required.
• GenCreateOrUpdateSchemaSequenceDDL https://experienceleague.adobe.com/developer/campaign-api/api/sm-builder-GenCreateOrUpdateSchemaSequ...
• From ACC 18.10, XtkNewId is no more the default value for the sequence in the out-of-the-box schemas. You are now able to build schema or to extend existing schema with a dedicated sequence.
• More details about sequence exhaustion below.
  • https://helpx.adobe.com/customer-care-office-hours/campaign/sequences-exhaustion-campaign-classic.ht...