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

I was just going through Powercenter Monitor's help guide and noticed a page describing about the different possible statuses that a workflow or a task can be in at a given point in time. I found a status  called "Unknown Status" as one possbile status, and per guide here is when it can happen.


Unknown Status

Tasks The Integration Service cannot determine the status of the workflow or task, or the Integration Service does not respond to a ping from the Workflow Monitor. The status may be changing. For example, the status may have been Running but is changing to Stopping.

I felt this status is somewhat interesting and thought I should pay some attention. First possible cause (explained in help), failing to ping IS, is quite convincing. However, I am wondering why a workflow would take "Unknown Status" when it changes from  Running to Stopping like in the example explianed in second possible case. I guess if this has to fit in second case, that status would only for a very brief period - brief enough to change the  status from Running to Stopping for example. Please comment on this second point if someone has better idea.

I am posting this now because I have recently experience some other interesting scenario that lead a workflow to "Unknown Status". So, I thought it is worth sharing this experience...

Workflow changed to "Unknown Status" even before it started running. So, no workflow log file generated. Only next obvious source of information is IS log, and I found below entry (slightly modified as it is classified) in there...

From IS Log:

ERROR Wed Oct 7 00:55:09 2009 1409415520 LM_36317 Error in starting execution of workflow [id = 2651] [w_sales_fact].
ERROR Wed Oct 7 00:55:09 2009 1409415520 LM_36363 Workflow [w_sales_fact]: Cannot rename workflow log file [/opt/app/Informatica/pc/server/infa_shared/WorkflowLogs/w_orders_LOAD.log.bin] to [/opt/app/Informatica/pc/server/infa_shared/WorkflowLogs/w_orders_LOAD.log.1.bin].
INFO Wed Oct 7 00:55:01 2009 1409415520 LM_36814 Workflow [w_sales_fact]: Got scheduled request to start workflow.
INFO Wed Oct 7 00:55:01 2009 1398925664 LM_36814 Workflow [w_orders]: Got scheduled request to start workflow.
INFO Wed Oct 7 00:55:00 2009 183009705024 LM_36813 Workflow [w_sales_fact], run instance name [] is scheduled to run now, schedule last saved by user [karteek].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27029 Use persisted repository value [0] for user-defined workflow/worklet variable:[$$Load_Failed_ctr].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27029 Use persisted repository value [0] for user-defined workflow/worklet variable:[$$Is_LoadFailed].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27027 Use default value [] for user-defined workflow/worklet variable:[$$Delay].
INFO Wed Oct 7 00:55:00 2009 183009705024 LM_36813 Workflow [w_orders], run instance name [] is scheduled to run now, schedule last saved by user [karteek].

The reason it went into unknown status was that there were 2 workflows that were wrongly configured with a same log file name (w_orders_LOAD.log), and also scheduled for some time. At one point of time on thier schedule they were both initiated (acquired  the execution lock) and attempted to create their workflow log files. Unfortunately they were doing the write attempt on same file exactly at same time, and only one was through, but other was left in "Unknown Status".

This is very common thing that we as developers miss coding/naming standanrds, and here in Informaica chances would increase when we copy the same object and customize it for a different purpose. We overlook many things as  the original piece worked quite well. Here it exactly happened the same..."w_orders" was copied from "w_sales_fact" , but missed to change its "w_orders" workflow specific changes, and one such  change being the log file name. We corrected the log file name and fixed that problem forever for that workflow. But how many such other workflows with wrong log filenames was a question. So, I then did some research on Powercenter repository database, and wrote the below query. This basically searches wherever conflicts are there between workflow name and its log file name. You can change the log file name pattern below as per your naming standards.

select subject_area, task_name workflow_name, attr_value cur_log_name, task_name || '.log' new_log_name 
from infav8.rep_task_attr a, infav8.rep_all_tasks b
where attr_name = 'Workflow Log File Name'
and a.task_id = b.task_id
and a.version_number = b.version_number
and task_name || '.log' <> attr_value  -- change pattern in this line
order by 1, 2

Hope it helps!

Update 11/28/2009:
I have collected few more situations that can possibly turn a workflow's status into "Unknown Status".

• When a PowerCenter repository have versioning enabled and a dependent activity is still checked out when scheduling the workflow - solve this by checking in all the dependent objects (from Informatica KB - ID: 100943)
• There is a known issue (CR 182478) that has been fixed in PowerCenter 8.6, causing workflow to be in "Unknown Status". To resolve the issue, upgrade to PowerCenter 8.6. However, as a workaround in versions earlier than PowerCenter 8.6, set Save workflow log for these runs to zero (0) in workflow properties (ID: 106278).

Update 12/21/2009
When workflow parameter files is defined (in workflow properties), but if either parameter file is not present or no permissions  to read it, then also workflow goes into Unknown status (verified in 8.6). Error message would be like one below.

(my_is) Start task: ERROR: Workflow [wf_test]: Parameter file [/opt/app/Informatica/pc/server/infa_shared/SrcFiles/test.prm] not found. Please check the Integration Service log for more information.



Karteek

There is some interesting behaviour that I noticed recently in Informatica's repository with respect to session's run time information. If you are not much comfortable with Informatica repository that I now I am going to explain about, here is some quick notes.

Informatica entire application lies on a data repository that consists of several tables (mostly start with OPB*). Perhaps in order to make an ease access to repository information, Informatica also provided several views based on the OPB tables. For many of the common questions you may want the answer REP views should help, bu there is always an exception and OPB tables are there. When you write queries on OPB tables take due care that you do not ruin database with bad joins - for this Informatica manual gives the columns that generally good candidates for joins when using REP views, but generally the same information applies to joining tables as well since a view is just a named query. Some useful views are... REP_WORKFLOWS - Workflow master list REP_WFLOW_RUN - Worklow run time details REP_ALL_TASKS - Master task list REP_TASK_INST_RUN - Task level run time details REP_SESS_LOG - Session's Success/Failed records etc...

With those details, let me explain the what the prob

em is in 7.1. Workflow_Run_ID uniquely identifies each run of a workflow - means when a workflows starts (starting part of the workflow or full workflow or a schedule run) it would have a new workflow_run_id.

For suspended workflow (enabled "Suspend on Failure"), common way we recover it is by resuming it after the fix is provided (in 8.6 option name is "Recover"), and resuming it obviously not a new run so doesn't carry a new worklflow_run_id after resumed. It also means that there would not be a new entry into REP_WFLOW_RUN (or OPB_WFLOW_RUN table) when a workflow is resumed.

Moving a little back, a workflow goes to suspended state when it's meant to fail by the configuration of underlying tasks - means when underlying task fails that makes it's parent (or workflow in a simple setup) to fail and so workflow suspends. So, when such suspended workflow is resumed that actually restarts the failed tasks in the run. I noticed, in 7.1, even though resuming does not create a new workflow_run_id, tasks that restarts when its parent workflow is resumed would create new entries in REP_TASK_INST_RUN. That might be for good reason, but I see that of not much help rather misleading as there is no column that distinguishes task restarts (start_time of task can give that info, but that's not a good candidate).

Moreover, Informatica 7.1 behaves badly in maintaining information in those records - if a task is failed, say 3 times, when resumed multiple times, and finally succeeded in 4th attempt, then all the 4 records of that task run would get updated with last run information (except start_time that I identified) - end_time, error_code etc...and I would say this is completely misleading. Similar behaviour can be noticed in other tables/views that stores the task level run time information like REP_SESS_LOG (OPB_SESS_LOG).

If you run this below query on 7.1 repository you would notice some records being fetched (provided there was atleast one attempt of resuming a suspended workflow)

select workflow_run_id, instance_id, count(*)
from infav71.rep_task_inst_run
group by workflow_run_id, instance_id
having count(*) > 1

Even more dangerous part is with the views that joins OPB_TASK_INST_RUN and OPB_SESS_LOG tables. As I said, there is no good candidate in those tables to identify the multiple task (or instance to be precise) runs when suspended workflow is resumed, joining these 2 tables would make cartesian product - means 4 entries in both these 2 tables would return 8 records - that's a flaw, and you can notice that in 7.1.

select * from infa71.OPB_TASK_INST_RUN     where INSTANCE_ID = 18738 and WORKFLOW_RUN_ID = 7113806;

SUBJECT_ID    WORKFLOW_ID    WORKFLOW_RUN_ID    WORKLET_RUN_ID    CHILD_RUN_ID    INSTANCE_ID    INSTANCE_NAME    TASK_NAME    TASK_ID    TASK_TYPE    START_TIME    END_TIME    RUN_ERR_CODE    RUN_ERR_MSG    RUN_STATUS_CODE    RUN_MODE    VERSION_NUMBER    SERVER_ID    SERVER_NAME
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 2:00    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 1:47    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 1:14    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:52    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:17    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:02    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR

If you look at the above result only start_time is differing, and values of older runs are updated to last run values (error_code, end_time etc... though they have different values from run to run)

Looks like it's fixed in 8.6 - I did not test in version between 7.1 and 8.6 though. In 8.6 however number of resuming of a suspended workflow may be, a failed task instance would have only one run per workflow_run_id. This is much better, rather misleading with more information. if you run the first group by query in your 8.6 repository, you should not be getting any records at all.

select workflow_run_id, instance_id, count(*)
from infav88.rep_task_inst_run
group by workflow_run_id, instance_id
having count(*) > 1
0 row(s) retrieved

Hope that helps.

I appreciate your comments... Thanks!

Karteek