Wednesday, July 15, 2009

Error Logging in PowerCenter

In order to capture any Informatica PowerCenter errors into a flat file or database during runtime, Informatica Corporation suggests row-level error logging. The major disadvantage with this is that the performance of the workflow is affected because of row-level processing as opposed to block processing.

In order to overcome this, a simple approach can be followed that can be a common approach for all the workflows. This approach is based on the fact that $Session_Name.ErrorMsg will store NULL value if the session runs fine, otherwise stores the latest error message from the Session run.

1) Create two workflow variables - one for Error message $$ERROR_MESSAGE and the other $$SESSION_NAME to store the failed session name.

2) Create an assignment task in the workflow and create links to it from each of the sessions. Please note that the flow should be TOWARDS the assignment task from the sessions.




3) Modify the link expression for all these links to $Session_Name.PrevTaskStatus = FAILED.

4) In the assignment task, assign $Session_Name.ErrorMsg to the workflow variable $$ERROR_MESSAGE and assign Session_Name to $$SESSION_NAME.



5) You need a bit of nested iifs to achieve this.

For variable $$ERROR_MESSAGE, the expression contains

:udf.if_null_or_blank($Session_Name_1.ErrorMsg,
:udf.if_null_or_blank($Session_Name_2.ErrorMsg,
:udf.if_null_or_blank($Session_Name_3.ErrorMsg,
:udf.if_null_or_blank($Session_Name_4.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_5.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_6.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_7.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_8.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_9.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_10.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_11.ErrorMsg ,
:udf.if_null_or_blank($Session_Name_12.ErrorMsg ,
'A Fatal Error occurred'
,$Session_Name_12.ErrorMsg
)
,$Session_Name_11.ErrorMsg
)
,$Session_Name_10.ErrorMsg
)
,$Session_Name_9.ErrorMsg
)
,$Session_Name_8.ErrorMsg
)
,$Session_Name_7.ErrorMsg
)
,$Session_Name_6.ErrorMsg
)
,$Session_Name_5.ErrorMsg
)
,$Session_Name_4.ErrorMsg
)
,$Session_Name_3.ErrorMsg
)
,$Session_Name_2.ErrorMsg
)
,$Session_Name_1.ErrorMsg
)




:udf.if_null_or_blank(input_String_Argument, output_if_NULL_Blank, output_if_not_NULL_Blank) is a user-defined function with expression contents

iif(isnull(input_String_Argument) or length(ltrim(rtrim(input_String_Argument)))
= 0, output_if_NULL_Blank, output_if_not_NULL_Blank)

In the same way, create the expression for the $$SESSION_NAME. It should be the same expression as for the $$ERROR_MESSAGE but in the else part of the iif(), the session names should be specified instead of ErrorMsg.

6) From this assignment task take a link to a session which stores the contents of these workflow variables into a database table or a flat file. let us call this session mapping as LOG mapping.

You may question the scope of these workflow variable inside the Log mapping. If you can use the workflow variable in the source qualifier SQL override, then you can get the data from the same. Like this:

select '$$ERROR_MESSAGE', '$$SESSION_NAME'
from dual




Take 2 ports out of the source qualifier onto a expression transformation and then continue loading into a relation table target or a flat file target.

7) It is IMPORTANT to make sure that the General tab property of the Assignment task --> Treat Input Links as "OR". This makes sure if at least one session fails, the assignment task is triggered and the error is logged.

If you implement the Error Logging this way, you will be able to catch all kinds of Informatica errors.

19 comments:

Anonymous said...

Hi Sarma.

How does log mapping gets to read the workflow variables $$Error_Message and $$Session_Name. Can you elaborate more on this...

Pranay

Radhakrishna Sarma said...

Hi Pranay,

I have answered this in the 6th point the article.

Cheers
Sarma.

Anonymous said...

Hi Sarma.

"From this assignment task take a link to a session which stores the contents of these workflow variables into a database table or a flat file. let us call this session mapping as LOG mapping.". It says to connect Assignment task and session task next to it via link task. How these values are used by log mapping to insert into a table. You mean to say to create a dummy source and put select $$ERROR_NAME,$$SESSIN_NAME from dual...

Pranay

Radhakrishna Sarma said...

Exactly! You use a dummy source like DUAL and use the SQL Override I had given you.

By the way, for the links between Assignment task and the next session, it would be normal link. Take a look at the screen shot in the article. If you click on the screen shot, it opens up as a bigger image.

Cheers
Sarma.

Anonymous said...

Cool...thanks for clarifying...its something good I propose to implement in my project.

Pranay

Sanjay said...

Hi Sarma,

It is not allowing to use the workflow variables in the mapping SQL overwride.
This is what I am getting in the log file

User defined query [select $$ERROR_MESSAGE, $$SESSION_NAMEfrom dual] has references to mapping parameters or variables that cannot be resolved correctly.
"

Radhakrishna Sarma said...

Hi Sanjay,

Enclose the variables in single-quotes. I've edited my blog post also with that.

select '$$ERROR_MESSAGE', '$$SESSION_NAME' from dual


Cheers
Sarma.

Sanjay said...

Hi Radha,

Do you have gmail account? I would like to send you the screen shot of an issue I am encountering regarding this.

I would really appreciate if you could look into that.

Thanks,
Sanjay

Radhakrishna Sarma said...

Hi Sanjay,
My email address is radhakrishna [dot] sarma [at] gmail [dot] com.

Cheers
Sarma.

Radhakrishna Sarma said...

Hi Sanjay,

I've edited the user Defined Function definition a bit in the blog post and this should work for you.

Cheers
Sarma.

Sanjay said...

Hi Sarma,

It worked now.

Thanks,
Sanjay

Anonymous said...

Hi Radha.

What happens if some sessions completes at different time. If you set Treat Input Link as 'OR'in assignment task property then if any one of the session fails then assignment task will run and it will capture only that error message if other sessions fails lately by that time assignment task will be successful.

/Rajiv

Radhakrishna Sarma said...

Hi Rajiv,

For multiple errors, you need to change the workflow to add few decision tasks and wait until all the errors are captured.

Cheers
Sarma.

Sowms said...

Hi,

How is this a replacement to row-level error logging?

I ran a simple test with informatica dtm error logging enabled and your approach. The session was violating unique constraint and the error threshold was set to 100.

The output file of Informatica row level error had 100 row error details written whereas the file generated using your approach had only the last error message.

Can you please clarify?

Regards,
Sowmya

Radhakrishna Sarma said...

Hi Sowmya,

Thanks for visiting my blog. You are right. This is not a replacement for row-level error logging. PowerCenter only has task_name.FirstErrorMsg or task_name.ErrorMsg and this approach can not handle multiple errors. I have seen people resorting to row-level error logging just to capture Informatica error. That is the reason I have suggested this approach. Anyway, for multiple errors, you can make use of the task_name.TgtFailedRows etc.

Cheers
Sarma.

Sowms said...

Hi Sarma,

Thanks for clarifying. I am just trying to understand the usecase for this approach.

The repository view REP_SESS_LOG has both the first error code and the last error code along with session name, workflow name , folder name etc. I guess we can get the same details from the MX view than having to modify the workflow to add an assignment task and a session task to capture the detail that is already persisted to the repository.

And also, since you started the post with a performance concern of row-level error logging due to lack of block processing, i assumed the approach you have suggested would capture row level errors for every session. But I guess not.

Regards,
Sowmya

Anonymous said...

I would like to take advantage of your capture of session name or task name inside workflow or worklet. However, there is not a built in variable for this. Is there a way to just capture that? Example $s_test.status, would like to have $s_test to variable.
Best regards

vamshi said...

I would like to take advantage of your capture of session name or task name inside workflow or worklet. However, there is not a built in variable for this. Is there a way to just capture that? Example $s_test.status, would like to have $s_test to variable.
Best regards

Raju Thota said...

Many thanks for the posts, the blog is very helpfull.
My job has become more easy after looking into your blog.
I need your help on the implementing session logs to audit table database.

I have followed the steps and did exactly same, the logic is working fine for single session but we i have multiple sessions assigned to task, its only loading top record.
It is very complex to add assignment task, session to each session to get the session logs.
Is there any property I have missed?

I have written session name as

IIF(ISNULL($s_LUVA.ErrorMsg),
IIF(ISNULL($s_PROD.ErrorMsg),
'NOT FOUND',
's_PROD'),
's_LUVA')

Thanks for your help.

 
HTML Hit Counters