Adobe Campaign Classic - Sequences Exhaustion

Hi cavallo714​

I have sent you the ppt on your email address. Please check and confirm if you have received it.

Regards,

Ananya Kuthiala

Hello Everyone,

Let me check internally if we can upload the presentation on the Office Hours page. I will keep you all posted.

Regards,

Ananya Kuthiala

Hi ayats67229299​

I see you have raised a support ticket for the same. One f our team members will provide you with the scripts over the ticket.

Regards,

Ananya

Hello @ukender 

Which script do you require and what build of ACC are you using ?

Please be cautious while switching any sequence without analysis as that could have consequence on the instance.

Regards,

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