Applied dimensionality

Seems like I can rename this blog ‘Reminisces’ any day now. After some years of not prodding into Cognos EP I was asked to look at a rather confusing error. Export deployment wouldn’t work, throwing ‘null’ error initialising object selection dialogue. Yes, as these two IBM support articles (1,2) state: it’s a problem with duplicated objects in PAD. But since was the first time I saw this rather massive instance (80+ apps, hundreds of macros), I was interested in finding a way to automate this task. Although number of EP users is decreasing, hope that anybody facing this issue in future will benefit from this post. And if you’re curious about how things are stored in PAD, scroll over )

There are a few things that can be duplicated in PAD, so I’ll describe each situation accompanied with code I wrote to check for duplicated items. Fortunately, export dialogue shows what it’s checking at failure point, so you’ll know where to look.

Macros

All you need to know about macros is store in P_MACRO* tables. P_MACRO holds general macro info, including it’s name that is potential duplicate. EP developers must’ve overlooked the fact that macro name is case-sensitive from Administration Console point of view and case-insensitive from export deployment wizard’s point of view. So if you have two macros: copy_sales and copy_Sales, you’ll find out about this duplication only when you’ll try to export deployment.

Code checking for such situations is very straight-forward.

SELECT * FROM p_macro WHERE
 LOWER(rtrim(ltrim(macroname))) IN
 (
 SELECT LOWER(rtrim(ltrim(macroname))) FROM P_MACRO
 GROUP BY LOWER(rtrim(ltrim(macroname)))
 HAVING COUNT (*) >1
 )
 ORDER BY LOWER(rtrim(ltrim(macroname)))

Found some duplicated macros this way, but the problem persisted.

Macros are also recorded in PAD xml (see below), but table approach is way easier.

Applications

Applications pose a more interesting problem.

ADMINOPTION table

Most of the application details are stored in ADMINOPTION table of each applications scheme (Oracle) or database (Ms SQL Server). I wrote some time ago about updating JVM options for Contibutor client via this table. In my search for duplicates I was interested whether there are duplicate application ids or display names. So it made sense to query all adminoption tables at once.

Here’s a sample Oracle SQL script for such queries. It’s easy to replicate it for MS SQL server (seems that didn’t publish such examples as of yet, mail me if you need them).

CREATE TABLE temp
(USR_NAME VARCHAR(50) NULL,
OPTIONID VARCHAR(250) NULL,
DESCRIPTION VARCHAR(512) NULL,
OPTIONVALUE VARCHAR(512) NULL);
commit;
BEGIN
  FOR usr IN (SELECT owner FROM all_tables
  WHERE TABLE_NAME = 'ADMINOPTION')
  LOOP
  EXECUTE IMMEDIATE 'INSERT INTO temp (usr_name, optionid, description, optionvalue)
  select '''|| (usr.owner) ||''' USR_NAME, OPTIONID, DESCRIPTION, OPTIONVALUE from ' || (usr.owner) ||'.ADMINOPTION' ;
  END LOOP;
END;
 
-- check for duplicate application_display_name
SELECT optionvalue, COUNT(*) FROM temp
WHERE optionid = 'APP_DISPLAY_NAME'
GROUP BY optionvalue
HAVING COUNT (*) > 1

Strangely, such exercise showed me some duplicated names, but detaching those apps from PAD didn’t help, Export wizard was still complaining about duplicated apps.
So I started SQL profiling to see what was really happening under the hood and was impressed by finding

PAD xml

Turns out, all application details are stored in an XML file located in P_PAD table. This file is actually huge and messy, but it contains some fields that are not available in ADMINOPTION table or Administration console output. Deep inside this file, applications are described like:

<applications>
<application id="{94BF5F99-E6AF-4D9C-BA63-E2303055296B}" name="go_capex_contributor" 
objidref="obj_APPLICATION"><properties><property propidref="pr_DATASTORE_CON_DOC">
<![CDATA[<connection>   
...
  <applicationname>    go_capex_contributor   </applicationname>  
</connection>  ]]>
</property><property propidref="pr_DISPLAY_NAME"><![CDATA[go_capex_contributor]]></property>
<property propidref="pr_UNIQUE_ALIAS"><![CDATA[test]]></property>
...
</applications>

And it turns out that the ‘name’ property held duplicates in my case. Since manually extracting properties is tad too tedious for me, I wrote a simple python script that does exactly that. It’s not a complete solution, more an approach sample. I’m using pyparsing as usual.

# -*- coding: utf-8 -*-
#Function: Parses Cognos Enterprise Planning PAD contents file xml and extract all aplication unique names
#Usage: run python ep_pad_analyzer.py -i file_with_pad_contents -o output_csv_file
#Author: Yuri Kudryavcev email mail@ykud.com
#
import codecs
import csv
import os
import sys
import re
import string
import time
import getopt
import datetime
from pyparsing import Word, Literal, OneOrMore, nums, Suppress, SkipTo ,makeXMLTags, alphas
 
def writeToLog(message):
 print ('%s: %s')%(str(datetime.datetime.now()), message)
#
def SafeFileOpen(filename,mode,encoding):
 try:
 fl=codecs.open(filename,mode,encoding,'ignore')
 except IOError:
 print "Cannot open file "+filename+"\n"
 return (-1)
 return fl 
#
def main(argv):
 """input format: python ep_pad_analyze.py -i c:\pad.xml -o result.csv -l log_file.txt
 
 Arguments:
 -i or --input file, grabbed from P_PAD table of Contributor Database
 -o or --output -- file for pad info csv format
 -l or --log log file
 -h or --help This message
 """
 WorkLogFile=""
 LogDirectory=""
 inFile = ""
 OutDirectory=""
 out_put_file = "pad_applications.csv"
 # getting command line arguments
 try:
 opts, args = getopt.getopt(argv, "hi:o:l:", ["help","input=","output=","log="])
 except getopt.GetoptError:
 print main.__doc__
 sys.exit(2)
if len(argv) <= 1:
 print main.__doc__
 sys.exit(2)
 for opt,arg in opts:
 if opt in ("-h","--help"):
 print main.__doc__
 sys.exit(2)
 elif opt in ("-i","--input"):
 inFile = arg
 elif opt in ("-o","--output"):
 out_put_file = arg
 elif opt in ("-l","--log"):
 WorkLogFile = arg
 
 if WorkLogFile=="":WorkLogFile="pad_analyze_log.txt"
 #setting up logging
 WorkLogFilefl = SafeFileOpen(WorkLogFile,"w",'utf-8')
 out_put_fl = SafeFileOpen(out_put_file,"wb",'utf-8-sig')
 #backup_stdout = sys.stdout
 #backup_stderr = sys.stderr
 #sys.stdout = WorkLogFilefl
 #sys.stderr = WorkLogFilefl
 english_chars = u''.join(unichr(x) for x in xrange(0x041, 0x05B)) + u''.join(unichr(x) for x in xrange(0x061, 0x07B))
 application_unique_alias = Suppress('property propidref="pr_DISPLAY_NAME"><![CDATA[') + Word(english_chars + nums +' ()' + '_-') + Suppress(']]></property>')
 application_id = Suppress('<application id="{') + Word (english_chars + nums + '-').setResultsName("application_id") + Suppress('}" name="') + Word(english_chars+ '_').setResultsName("application_name")# + SkipTo('<applicationname>') + '<applicationname>' + Word(alphas).setResultsName('datastore_app_name') + '</applicationname>'
 writeToLog("Starting scanning.")
 writeToLog("Input file is: %s .Log file is: %s"%(inFile, WorkLogFile))
 fl = SafeFileOpen(inFile, 'r','utf-8')
 contents = fl.read().encode('utf-8')
 for x in application_id.searchString(contents):
 print x
 fl.close()
 #closing up logging
 out_put_fl.close()
 WorkLogFilefl.close()
 #sys.stdout = backup_stdout
 #sys.stderr = backup_stderr
# 
if __name__ == "__main__":
 #main(sys.argv[1:])
 main(['-i','pad.xml'])

Administration Links

There’s a P_INTERAPPLINK table in PAD, but it doesn’t contain link names, so it’s worthless for duplicate search. But you can use it for some reporting and finding connected applications.

But xml file mentioned above contains all you need, there’s a link description part that goes like this:

<links>
<link_instance id="{B4FC0A77-CD3F-4239-859C-FC4208539B77}" name="application_link_test"
 objidref="obj_LINK_INSTANCE"><properties><property propidref="pr_DISPLAY_NAME"><![CDATA[application_link_test]]></property></properties>
....
</links>

So using the same approach as for applications works and you can find duplicated links without going through all of them in the PAD.