OdiStartScen in async mode: variables lost in execution queue

673655

I have the following case:
- a scenario must be called for each row in a database table
- this scenario has a variable, declared in the scenario parent package: variable values are read from the table
- rows are more than the "maximum number of sessions" for the agent
- execution of the scenarios should run in parallel

So I made a procedure, with only one step. The source command is something like "SELECT col1 alias1 FROM MyTable". The target command, on Sunopsis API, is OdiStartScen -SCEN_NAME=MyScenario "-GLOBAL.VAR1=#alias1"

When the procedure is executed by the agent, I have the following outcomes:
- if OdiStartScen is run synchronously (-SYNC_MODE=1): OK, no problem
- if OdiStartScen is run asynchronously (-SYNC_MODE=2) AND the number of rows in MyTable is less than the "Maximum number of sessions" set in Topology Manager for the executing agent: OK, no problem
- if OdiStartScen is run in async mode, and the number of rows in MyTable is greater then the maximum number of sessions, then the execution is OK only for the first rows of the table. Successive executions get enqueued, but when it's time for the agent to run them, Operator reports the followin error: "Variable has no value".

So it seems to me that variable values don't get enqueued, is it correct?

566902

Is the variable declared as not persistent?

673655

Yes, but I tried also with "Historize" and "Latest value".

566902

I see what you mean, I can see the same behaviour. The execution parameters don't seem to be being stored.

Cezar Santos

Hi,

Let me try to contribute...

That is a knew ODI behavior....

You can:

Or create watchover steps
Or at the package, force the variable do a refresh from itself, something like:

select "#my_own_variable" from dual

that will storage the variable when at "Historize" mode.

I, personally, prefer to use watchover.

673655

Hi Cezar,
what do you exactly mean for "watchover step"?

Thank you

Cezar Santos

A small trick to storage variable values for "eternity" lol

Simple:

1 ) create a procedure

2) create option at procedure (I named it VARIABLE_VALUE), "Value" type.

3) create a step at procedure:
Tab: Target
Technology: Java BeanShell
Check the "Ignore Errors" option
Code:

throw new Exception("\n\n<%=odiRef.getOption("VARIABLE_VALUE")%>\n\n")


How to use:
Drag and drop the procedure in the package and write the variable or variables at option text box like:

My variable value is: #variable
or
The values are: Variable 1 --> #variable Variable 2 --> #variable

Or whatever text that you wish to use.

Now you can drag and drop this procedure so many times you need at the package and see the variable value at any moment of the process.

At Operator you will see this steps as warnings, go to Execution tab at the step and the value will be there.

Does it help you????

673655

Hi Cezar,
unfortunately it still doesn'work. If I raise the "maximum number of sessions" I can see the values in the Operator log. But lowering it, still gives "java.lang.Exception: Variable has no value".

Actually, the only workaround I found is to use the session name parameter: if I put the variable value in the session name while calling OdiStartScen I'm able to read it in the package by using a refreshing variable whose refreshing function is "select '<%=odiRef.getSession("SESS_NAME")%>' from dual". Clearly, if I have more than one variable I must concatenate them in the session name, with a separator character, and do some parsing via Instr and Substr in the refreshing function.

Anyway, thank you for your support.

Romano

Cezar Santos

Hi Romano...

My bad, I focused just in show the variable value...

I'm trying to figure out a workaround... Please:

How many records will be expected from the query?
How many parameter? Just one or more?
Too, how is the maximum bytes length for each returned parameter?

I got an idea but need to test it...

673655

Hi Cezar,
actually I have 30 records, but they may grow in future;
at least two parameters, a varchar2(50) and a timestamp(3)

I discovered the problem while experimenting with the agent load balancing, because I need to distribute the package execution on agents on different machines.

Thank you,
Romano

Cezar Santos

Now I understood the problem.

Let me suggest a workaround... I did not test it because I don't have such environment here (at true not even an ODI to test right now) but, in theory, should work.

The fact is: ODI is not able to pass parameters between agents.

Solution: Pass just the string, not parameters.

Please, could you copy here the exact command at your start scen step (including the parameters "call")? That will make easier to build the code.