Adobe Campaign Classic - Sequences Exhaustion

[cavallo714]

I saw a great webinar posted by Ananya Kuthiala​ and Pooja Singh Devanadan of Adobe Support regarding Adobe Campaign Sequence Exhaustion (Adobe Campaign: Sequences Exhaustion - Adobe Campaign Classic ). In the video, there was discussion of the scripts demonstrated as being posted for those attending the live session. Would it be possible to get those scripts or ppt deck used during the presentation? Unfortunately the video quality has made it extremely difficult to read the code and reproduce it.

I am trying to utilize the code to get a measurement of how many of the key sequences within various schemas have been exhausted.

Thanks!

Lee

[sushantt3671424]

@ananya_kuthiala 

Can you please share the docs which you have explained on videos.

And also if i want to change from 16 bit to 32 bit sequences what steps we need to follow.

Thanks in advance 

[ananya_kuthiala]

Hi @sushantt3671424 

Alot has changed since i presented that topic during office hours 2 years ago.. For example in latest build by default any new schema created would have its dedicated sequence. A lot of out of the box tables have been moved to their dedicated sequence as well which reduces the consumption of xtknewid. I would suggest to go through the release notes Sequence Auto Generation

Second query regarding changing sequence bit, by default sequence are on 32 bit and as of now there is no option to switch them to 64 bits.

Script to check sequences using a particular sequence

/*************************************************************************
* ADOBE CONFIDENTIAL
* ___________________
*
*  Copyright 2017 Adobe Systems Incorporated
*  All Rights Reserved.
*
* NOTICE:  All information contained herein is, and remains
* the property of Adobe Systems Incorporated and its suppliers,
* if any.  The intellectual and technical concepts contained
* herein are proprietary to Adobe Systems Incorporated and its
* suppliers and are protected by all applicable intellectual property
* laws, including trade secret and copyright laws.
* Dissemination of this information or reproduction of this material
* is strictly forbidden unless prior written permission is obtained
* from Adobe Systems Incorporated.
**************************************************************************/

/**
 * - count the number of tables using a sequence like xtkNewId
 * - display the number of reows of each of those tables
 */

// Parameters
var options = {
  sequence: 'xtknewid', // the sequence 
  takeOnlyIntoAccountTables: '' // List (coma-separated) of the tables to take into account, if empty all the tables using the sequences will be taken into account
};

// Parse command line options of the form -arg:--arg1=val1--arg2=val2--arg3...
// In other words, the nlserver javascript -arg is a concatenation of all options (no space)
options = (function(options) {
    var debug = function(message) { logInfo(message); }
    var args = application.arg.split('--');
    for (var i=0; i<args.length; i++) {
      var arg = args[i];
      if (arg == null || arg=='') continue;
      if (arg.trim) arg = arg.trim();
      if (arg=='') continue;
      var kv = arg.split('=');
      var name = kv[0];
      var value = kv[1];
      options[name] = value;
    }
    return options;
})(options);


var sequence = options.sequence.toLowerCase();
var takeOnlyIntoAccountTables = options.takeOnlyIntoAccountTables.length == 0 ? [] : options.takeOnlyIntoAccountTables.toLowerCase().split(',');
logInfo("sequence: " + sequence);
logInfo("takeOnlyIntoAccountTables: " + takeOnlyIntoAccountTables);

var cnx = application.getConnection();
var SQLTableMap = {};

// Get entity that are using the defined sequence
var stmt = cnx.query("SELECT DISTINCT (snamespace||':'||sname) FROM XtkEntity WHERE sentityschema LIKE '%chema%' AND lower(mdata) LIKE '%pksequence=_" + sequence + "_%'; ");

for each (var row in stmt) {
  // Get SQL table name
  var schema = application.getSchema(row[0])
  if ( String(schema.root.SQLTable) != "" ) {
    var table = schema.root.SQLTable.toLowerCase();

    // Filter out the table if requested
    if (takeOnlyIntoAccountTables.length && takeOnlyIntoAccountTables.indexOf(table)<0)
      continue

    // Get column name of the primary key
    var idCol = sqlGetString("select a.attname from  pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and i.relname LIKE '%_id' and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relname like '" + table + "'  and a.attname like 'i%id' and ix.indisunique = 't'");
    if ( idCol != "" )
      SQLTableMap[table] = idCol;
  }
}

//count nb of elt
var total=0,sum=0;

for (var table in SQLTableMap ) {
  var value = sqlGetInt("SELECT COUNT(*) FROM " + table)
  logInfo(table + ";" + value);
  sum = sum + value;
  total = total + 1;
}
logInfo("Nb of tables using the " + sequence + "sequence: " + total);
logInfo("Current nb of rows/id in used in these tables: " + sum);

Also be cautious while switching sequence to negative as that should not be done without complete analysis

Regards,

Ananya Kuthiala