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
