I was recently going through my.informatica.com site to find some interesting tool set called “Debugging Tools” from Informatica. There is a tool called “MetaQuery 2.0”, a java window application, that connects to PowerCenter repository database and fetches some key properties of various repository related objects. As my-informatica is for customers & partners, I can not give the site contents directly or MetaQuery tool for downloading here (it may be classified).  However, I am just pasting the description provided there for this tool below.
Metaquery 2.0
“Metaquery is an Informatica tool to query against PowerCenter repository for some of the key properties in session, workflow, and mapping objects. This tool is helpful to gather information regarding properties set in any of these objects (like session or workflow) and get the output report exported into a readable excel sheet or text file. The latest version supports repositories in DB2 and Sybase in addition to Oracle repositories. More properties in session and workflow are queries upon as well.”
I think this certainly is a useful tool to have for repository analysis and debugging. This tool is not exposing the queries it is using to fetch the data (in any readable format), but you are allowed only to choose the options on GUI (for example, sessions with target table enabled option) and submit. I usually write some queries on repository with all sort of reverse engineering, but as the underlying architecture/database design is no where documented it is difficult at times to get the requirement done properly and accurately.
Some screen shots of tool:



When I saw this tool first time on my-informatica site, I felt happy that it would reveal some facts about how some repository tables are related and logic behind using them. But it was not the case as the tool revealed only usage options from GUI, and all the underlying queries are still hidden in java .class files.  I had to use an alternative approach for revealing the queries. I used JD Java Decompiler and decompiled the class file (.\XML\SQLQueriesOra.class) and that is it – all the queries that the tool used for 46 features are available. I have compiled all of them at once place in a spread sheet (link provided below). Same query details can also be retrieved from java command line console, where in java echos the query each time you submit the request from front end. Hope you enjoy it!
A sample query used for getting the “The name of the source table used in the mapping” is below:
SELECT A.SUBJ_NAME,C.MAPPING_NAME,D.INSTANCE_NAME SOURCE_NAME_IN_MAPPING FROM OPB_SUBJECT A,OPB_MAPPING C,OPB_WIDGET_INST D WHERE A.SUBJ_ID=C.SUBJECT_ID AND C.MAPPING_ID=D.MAPPING_ID AND D.WIDGET_TYPE=1 AND D.WIDGET_ID IN (SELECT SRC_ID FROM OPB_SRC )
Welcome you all to add some of your experiences on your repository usage.
Repository Queries for PowerCenter 8x (from MetaQuery).xls
Karteek