I have a process flow which includes many data flow.
Each dataflow above is a simple DBMS SQL flow which query a source database and load data to a target table. I need to pass a custom variable to the SELECT statement. Right now I create a variable inside each DF1, DF2, DF3… The process flow in each dataflow will look like this:

My query looks like this:
SELECT A1, A2
FROM TableA
WHERE TIME_MODIFIED >= TO_DATE('&&MyCustomDateVariable', 'YYYY/MM/DD HH:MI:SS')

Is there a way to define a variable in a process flow and use this same variable in the SELECT statement inside the dataflow DF1, DF2,DF3… without having to define a variable in each dataflow?
Thank you.

This message has been edited. Last edited by: Tamra,

Yes, you can set the password once in the master flow (as opposed to each of the DBMS-SQL flows) and then after it is set pass the global variable value into each of the DBMS-SQL flows.

Make sure the program you call to setup the '&&' global variable has the 'Execute as RPC' box checked. This ensures that the global value is maintained through all processing.
[Note I am on an old version of Data Migrator- 7.6; so hopefully the below will transalte. I call a stored proc via a D.M. flow to set my global - is is the flow that executes with RPC, as 7.6 does not have a 'Set Variables' box.]

Then when you setup each of your DBMS-SQL flows, select the properties and pass in the variable in the Parameters box.

To pass it in, on the 'Parameters' line key the name of the local variable inside the DBMS-SQL flow (which doesn't have to be the same core name as your global but can be). Note you will NOT include the single ampersand here. Then follow this with the equal sign followed by the global variable (but with the double ampersands). If you want to pass multiple variables, just separate each assignment by a comma.

For example:

Let's assume &MyCustomDate is the local variable inside the DBMS-SQL flow and that &&MyCustomDateVariable is the global variable you would like to initiate just once at the beginning of your 'master' flow.

You would put the following in the parameter box (under Properties) of the DBMS-SQL program.

MyCustomDate=&&MyCustomDateVariable

Then inside your DBMS-SQL flow you can reference &MyCustomDate anywhere in the SQL statement (here you DO include the single ampersand).

Hope this helps (and that the differences between 7.6 and 7.7 have not changed this process significantly).

Sharon

It looks like you are using a Global Variable. If it is set in the process flow, each of the DataFlows should be able to access it. The only time I've need to define the Global inside of a dataflow is in a pararell group. Each dataflow will need to the property "Run as a Stored Procedure" checked.

@Sharon: Thanks a lot for your detail guide. I follow it and able to get it work. In 7.7, we can use the Set Variable to setup global variable and pass this variable as a parameter to each data flow just like you described. We don't have to create a program to setup a variable. By the way, I don't see the 'Execute as RPC' option in 7.7, probably they change it to 'Run as stored procedure'.

@Michael: Thanks for your input. I'm able to pass the variable & run the process flow even when I don't check on the "Run as stored procedure".
I notice that in a process flow, when a dataflow is inside a parallel group, there is no "run as stored procedure" option (when i right click > Properties on the dataflow.)
What is the different when we check/uncheck the "run as a stored procedure"?

Hi there,

In 7.6 For the RPC chioce - I was told it allowed the global variable to keep its value between the master flow and the flow being called. In my instance, I was calling a flow that in turn called a stored proc to set the global. That flow needed to have the box checked so that the global value would be maintained and available for use upon return to the master flow. Then the master flow could either pass it in as a parameter OR you could just check the RPC box on the next flow being executed by the master and the global value would then be available to it. In my setup, I was passing the global because I was using a parallel group in my master flow (which, as you noted, the flows in it do not have the RPC/run as stored proc choice). Also, because of the extensive calling of various flows that we do, we got into the habit of explicitly passing the global value once established in the master flow and then letting the called flows use it locally or itself pass further down as a local variable.


In my 7.6 world, if you declare a global and then try to use it in an RDMS-SQL flow without explicitly passing it in AND without the RDMS-SQL flow having the RPC box checked, it actually blew up, saying it didn't have a value for the global variable. When I checked the RPC box (but still did NOT explicitly pass it in), it runs fine. If you choose to pass the global in to a flow, then the program that received it will then use it as a local variable. At that point, I believe the only difference between checking your 'run as stored proc' box (if it behaves like my RPC box) is which type of agent is used to execute the program. When I check the box, it uses the deferred (DFM-DEFAULT) agents, when I don't check the box it uses the regular DEFAULT agents for the given flow being executed. Another way of seeing the effect (at least in 7.6): when you look at the job log for your master flow, if you do not check the box, each successive call to a flow is a link in the log that you have to drill down to see its own log; if you do check the box the log is one continuous document.

I hope this isn't too confusing.....

Sharon

If you check the run as stored procedure then you do not need to pass the Global Variables as parameters to the called data flow. The called data flow will execute in the same agent. The data flows in a parallel group each flow executes in its own agent and has its own set of global variables. This requires the passing in of parameters to each data flow in the parallel group.

Sharon & Michael, Thanks a lot for clarifying the detail.