Extracting information from SQL Table

This article describes how to use the MetaManager’s™ script runner module to extract SQL information from a package.

Problem

Customer wants to get information about the SQL Query for Query Subject in all of
the published models in Cognos

This is a classic scenario where our document tools provide all of the searching
that you need and the output formats to suit your needs. However the module falls
short on getting you exactly the data that you need. Our script integration into
documenter allows you to provide your own business logic and supply data to the
module to be included in the output

Solution

We can use MetaManager’s™ Script Runner module to develop a script that downloads
model files for packages, loads them into an XML parser then performs some sort
of logic (e.g. search the SQL query for a field).
Here’s
an example of extracted SQL from a package.

Below is a sample script that can be used to extract SQL from packages.

Download the complete
script here.

 
                
/*
* *** MetaManager™ Custom Script Example ***
*
* This script is a starting point to be used with MetaManager modules
* that support scripting.  To use this script simply enable the Custom Script option 
* in the module and provide this file as the script.  Depending on which module is calling
* the script one of the function below will be executed.  Based on the module and what 
* information the module needs, contextual information will be provided as parameters to 
* the JavaScript method below and the module may be expecting a return value.  
* Additional information is documented in each method below.
*
* Note that the methods below are called by each of their respective modules using the
* hard coded method name.  While it is OK to add additional methods to this script, do not 
* change the names of the example methods supplied below. We're here to help so if you need
* direction email us for support. 
*/


/*** Content Documenter ***/
// This method will be passed the searchPath of the report that is being modified as
// well as a special writer object for writing data to the output file. The writer.appendRow()
// method should be called for each row of data to be written to the output file.  This 
// method can be called many times for each object, which enables providing multiple lines
// of output for each object documented if needed. The parameter to the appendRow method is
// a name:value pair array, with each name being the column name and cell value for that column.
// There is no limit to the number of columns of data to be provided.
function ContentDocumenter_DocumentObject(writer, searchPath)
{
	// Output information to console for debugging
	log("Processing: " + searchPath);
	
	// Get a copy of the current MetaManager connection to IBM Cognos
	var server = api.getCurrentServer();
	
	// Get a copy of the object being processed
	var cmobject = server.getCMObject(searchPath);
	
	// Perform a complex documentation of a package 
	// Determine if a package uses DQM by documenting the value of the
	// useV5DataServer option in the Model XML
	if(cmobject.objectType == "package")
	{
		var modelXML = cmobject.packageFns.getModelXML();
		var root = loadXML(modelXML)
	
		var querySubjects = root.selectNodes("//querySubject");
	
		for( var i = 0; i < querySubjects.length; i++ )
		{
			var querySubject = querySubjects[i];
			var name = querySubject.selectSingleNode("name");
			var sql = querySubject.selectSingleNode("definition/dbQuery/sql");
			if(sql == null) continue;
			
			log("name: " + name.innerText);
			writer.appendRow({
				"Query Subject":name.innerText,
				"SQL":sql.innerText
				});
		}
	}

}



/* *** Helper Functions  *** */

function loadXML(xml)
{
	var xml = xml.replaceAll("xmlns=", "holdnamespace=");
	var doc = helpers.file.readXMLData(xml);
	return doc;
}	

function getXML(doc)
{
	var xml = doc.outerXml.replaceAll("holdnamespace=", "xmlns=");
	return xml;
}
	
String.prototype.replaceAll = function (str1, str2, ignore) {
		return this.replace(new RegExp(str1.replace(/([/,!\^${}[]().*+?|<>-&])/g, "\$&"), (ignore ? "gi" : "g")), (typeof (str2) == "string") ? str2.replace(/$/g, "$$$$") : str2);
	}

var m_LogInfo;
function log() {
	var line = "";
	for (var i = 0; i < arguments.length; i++) {
		var arg = arguments[i].replaceAll("r", " ").replaceAll("n", " ").replaceAll("t", " ");
		line += arg + "t";
	}
	m_LogInfo += line + "rn";
	console.log(line);
}