Replies

Avatar

Avatar

ananya_kuthiala

Employee

Avatar

ananya_kuthiala

Employee

ananya_kuthiala
Employee

10-09-2020

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

Avatar

Avatar

ananya_kuthiala

Employee

Avatar

ananya_kuthiala

Employee

ananya_kuthiala
Employee

10-09-2020

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

Avatar

Avatar

ananya_kuthiala

Employee

Avatar

ananya_kuthiala

Employee

ananya_kuthiala
Employee

10-09-2020

Hi @OlgaPOE 

 

You can do that via generic query editor too with the following steps

ananya_kuthiala_0-1599743542563.png

ananya_kuthiala_5-1599743866714.png

 

ananya_kuthiala_2-1599743601110.pngananya_kuthiala_3-1599743617250.pngananya_kuthiala_4-1599743662212.png

Click next 2-3 times until you see the following screen and then click on "Start the preview of data"

ananya_kuthiala_6-1599743901501.png

 

Regards,

Ananya Kuthiala