instrumentation in Informatica

 Instrumentation in applications is important. Especially when it comes to troubleshooting performance 😏. Unfortunately some applications seems to miss this feature. 

For me, Informatica Workflow Manager is such an application. I might be wrong - if I am, please tell me! 

But recently I was facing several issues in Oracle databases where I was only told the workflow name and workflow instance. After a lot of guesswork to identify the problematic sessions and SQLs, I was sure there must be something more useful, so I started to browse & search the internet. 

I found the Transaction Environment SQL. Even not explicit stated, for mee it seems a good point to identify a specific activity. Informatica has some Built-in Variables which are valid candidates, e.g. $PMWorkflowName. 

My first suggestion

BEGIN
DBMS_APPLICATION_INFO.set_module(
    module_name => '$PMWorkflowName',
    action_name => '$PMWorkflowRunInstanceName');
END;

failed with some internal errors:

ORA-06550: line 4, column 35:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

 

   := . ( % ;

 

Database driver error...

Function Name : executeDirect

SQL Stmt : BEGIN

DBMS_APPLICATION_INFO.set_module(

    module_name => 'WF_SHAREPOINT_MYCOMPLAINCE',

    action_name => 's_M_STG_MC_RUN')

Oracle Fatal Error

Database driver error...

Function Name : ExecuteDirect

 

Oracle Fatal Error

]

Some more research and another KB article showed  a small additional requirements: 
Semicolons ( ; ) are seen as termination character. As there are multiple semicolons inside the PL/SQL block, they must be escaped by a backslash ( \ ).

With this knowledge, the working code is 

BEGIN
DBMS_APPLICATION_INFO.set_module(
    module_name => '$PMWorkflowName',
    action_name => '$PMSessionName')\;
DBMS_SESSION.set_identifier( $PMWorkflowRunId )\;
DBMS_SESSION.set_context('E2E_CONTEXT', 'ECID_UID', $PMWorkflowRunId )\;
END\;

This solution wil not fit every need, but hopefully it can be seen as a valid startingpoint for better instrumentation in Informatica.