Forum Stats

  • 3,837,503 Users
  • 2,262,265 Discussions


OdiStartScen in async mode: variables lost in execution queue

673655 Member Posts: 15
edited Feb 23, 2009 11:11AM in Data Integrator
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
    566902 Member Posts: 809
    Is the variable declared as not persistent?
  • 673655
    673655 Member Posts: 15
    Yes, but I tried also with "Historize" and "Latest value".
  • 566902
    566902 Member Posts: 809
    I see what you mean, I can see the same behaviour. The execution parameters don't seem to be being stored.
  • Cezar Santos
    Cezar Santos Member Posts: 1,901

    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
    673655 Member Posts: 15
    Hi Cezar,
    what do you exactly mean for "watchover step"?

    Thank you
  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    A small trick to storage variable values for "eternity" lol


    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

    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
    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????
    Cezar Santos
  • 673655
    673655 Member Posts: 15
    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.

  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    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
    673655 Member Posts: 15
    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,
  • Cezar Santos
    Cezar Santos Member Posts: 1,901
    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. :D

    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.
This discussion has been closed.