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.
Keine Kommentare:
Kommentar veröffentlichen