In this short tip I would like to present a solution for the occuring error in Informatica PowerCenter 9.1.0.
Error description is as follows: "TM_6707 ERROR: unable to acquire execute lock on session instance".
This usually occurs when the same session instance runs simultaneously in a worklet/workflow.
To avoid this situation we can add a wait of a randomly selected amount of time using timer object before the session.
The seed of this random value can be generated from the current timestamp value with precision up to nanoseconds.
Let's describe one practically approved method step by step:
1. Define a new variable in a worklet/workflow of type date/time: $$wkl_WAIT_END_TIME;
2. Assign this variable a random seed value:
$$wkl_WAIT_END_TIME = ADD_TO_DATE (
SYSTIMESTAMP (), 'SS',
MOD ((TO_FLOAT (TO_CHAR (SYSTIMESTAMP(), 'SS.NS')) - TO_FLOAT (TO_CHAR (SYSTIMESTAMP(), 'SS.MS'))) * 763317177 + 33313111, 231)
3. Add a timer task with option "Absolute time" selected and define the workflow date-time variable as $$wkl_WAIT_END_TIME.
Please note that the modulo value (in example - 231) in the assignment defines the maximal number of seconds to wait before the session will start running. This value is to be increased if the number of simultaneously running session instances is growing on the average.
Hope the methodics described was helpful!
You are welcome to discuss this solution.