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

Sometimes we find ora-01410 as misleading. Couple of possible error cause scenarios are explained here... SQL> select * from dual where rowid='musings not allowed here'; select * from dual where rowid='musings not allowed here' *ERROR at line 1: ORA-01410: invalid ROWID I think , this error is probably much straighforward - you asked for some rowid which is actually invalid, by common sense, and Oracle repoted the same. But, we also sometimes see this error, even we don't reference to rowid psedo column - a typical select statement may very likely throw this error. I understood it due to Read Consistency. Even if it may sound strange, following explaination hopefully unveils the fact. Assume a session running a long running query that uses an index that gets rebuilt by another session. Indexes have some iteresting feature when it comes to rebuilding. While rebuilding, Oracle doesn't actually overwrite the inndex while it rebuilds, but it creates a fresh copy of index, and when new copy is ready to serve, it replaces the existing one. This approach improves the availability of the table quite effectively. Same is the reason why an index rebuild requires the the space atleast double the size of the index. Having said that, a long running query that started with using old copy of index, may later start using new copy while it still runs (because other index rebuild sessoin rebuilt/replaced the old copy). So, rowids fetched by the query at the begining (pointing to old index) are obsolete now as they are no longer valid. Hence the invalid rowid error. We can't expect the same read consistency that we get from transaction/DML operations. There are no invalid data or lost data as long as there is enough undo segment. Oracle reads though the undo segments for read consistent data. But in case of rebuild, it is DDL and it's lost forever, so we can't expect Oracle to do read consistency here as well. So, I think, read consistency with DDL is the responsibility of application rather than Oracle. Hope it helps! - Karteek