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 suggestionBEGIN
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.
