USER VARIABLE Stage in Datastage

User Variable stage is mainly get used to define/declare Global Variable within a Sequence

Variables defined in this stage can used anywhere in the sequence with below syntax

UV_Stage_Name.Variable_Name

We can define variable with the help of SQL, UNIX shell script also.

Let have more light with the help of below example.

Let’s say there is a job activity calling a Datastage job and getting its parameter/variable from input User Variable stage as below

User Variable Stage configuration

Let’s configure UV stage now. Double click on it and structure like below window will pop up,

User Variable stage Parameter

There are three tabs – General which has general info like, User Variables under which we define our parameters and Triggers where we can set condition for O/P link.

In above image a variable has been defined ‘Var1’ having expression as given.

This variable can be defined in any job of this sequence with following syntax or expression.

UV_GetParam_Date.Var1

Refer below image where Seq_Job’s parameters are defined

Job activity Configuration


 

One can fire up SQL/Oracle query to derive any parameter as require and can pass through using User Variable stage.

PFB screenshot for the same.

UV_4

Here, one need to provide Database details like database name, Server name, user , password.

In above example these details have been passed using parameters and calculated date using simple oracle statement.

One can use same User Variable stage as above as per the requirement.


11 Responses to “USER VARIABLE Stage in Datastage”

  1. Juan says:

    Hi cool post. I’m trying to store in a variable a value from a db table, but I can’t find documentation for the ExecuteSQL function that you use in your example.

    I’ve read in forums people saying that the solution is to fetch the table and store the value in a file, but I think it’s impractical and you’re doing it very simple.

    Could you please point me where to find more info about ExecuteSQL function.

    Thanks in advance.

    • Brock says:

      Juan – I second this. This solution looks very useful but I would also like to see more info on it. Hopefully somebody sees this and responds.

    • admin says:

      Thanks for the valuable Feedback.
      ExecuteSQL is not a function, its a Routine created or developed within.
      We will provide the info about it in next article.

  2. Matt says:

    Same question as Juan. Good example, but unable to use the ExecuteSQL function within user variable. Thanks! M

  3. DSGuy says:

    Is ExecuteSQL built in function in DS or user defined?

  4. Bob says:

    Did the article describing the Datastage Routine to query the database get published?

  5. Sk says:

    Dear Admin,
    Do you have the article already published explaining how to Create and Use “ExecuteSQL” function. If so could you please share the link to that here.

    I was looking to get a single value from a SQL Server table into a user variable, to then be used by downstream sequences? I am trying to do this without landing the value in any file.

    Any help is greatly appreciated.
    Regards,
    SK

Leave a Reply

© 2017 Database ETL. All rights reserved.