How to Alter User defined variables within a sequence

sparq · Post by **sparq** » Mon Mar 07, 2011 7:26 pm

Hello

Just wondering if anyone has been successful in changing the content of user defined variable by other downstream user defined variable activity. Basically, I am trying to implement is a loop that increments a user defined variable. The datastage help says it can be done but it hasn't work for me (version 8.5).

Any suggestions will be much appreciated.

Cheers

ray.wurlod · Post by **ray.wurlod** » Mon Mar 07, 2011 8:16 pm

You could possibly use a User Variables activity ahead of the loop to initialize the variable, and a User Variables activity within the loop to increment. Note that this is an idea that I have not investigated. Is the loop a counted loop? In that case you could simply add that to the initial value. Or maybe you could use an Execute Command activity to increment by 1 using an expr command. Just another thought.

sparq · Post by **sparq** » Mon Mar 07, 2011 9:04 pm

Hi Ray,

Thanks for you reply.

What we have here is two UDVs activities : UDV1 and UDV2

in the UDV1, there's a variable x and it's content is initalized with 0. In the next UDV2 (within a loop), theres a variable y and its expression is UDV1.x = UDV1.x + 1.

Pretty simple and it compiles although after running the sequence the log shows that UDV1.x remains constant. Its content doesn't change.

Have I spotted a new bug in the version 8.5???

Cheers

ray.wurlod · Post by **ray.wurlod** » Mon Mar 07, 2011 9:39 pm

No, there's no bug, it's doing precisely what you ask. Look at it in BASIC code:

Code: Select all

x = 1
Loop
While some_condition
   y = x + 1
Repeat

y can only ever be x + 1, and x is unchanging.
Now answer my question about it being a counted loop or not.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 07, 2011 9:44 pm

Use this routine inside the loop rather than a user variables activity.

Code: Select all

FUNCTION GetCounter(aValue)
* Argument is ignored but must be supplied.
* Returns 1 the first time invoked, 2 the second time, and so on.
      COMMON /GetCounter/TheCounter
      Ans = TheCounter + 1
      TheCounter = Ans
RETURN(Ans)

sparq · Post by **sparq** » Mon Mar 07, 2011 10:03 pm

It's not a counted loop. I'm building a sequence using the loop/endloop activities to loop through a string - list of tablenames delimmited by commas. I need a variable to use as counter.

The example you gave doesn't show how to change the original value. The manual says clearly that it can be done by other user defined variable activity.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 07, 2011 10:30 pm

Can you cite this reference? Have you challenged IBM with it?

I can certainly see how it can be done with a user-defined STAGE variable, but not with a User Variables activity. Unless, of course, the User Variables activity invoked a function such as the one I supplied, or one of the key management routines.

sparq · Post by **sparq** » Mon Mar 07, 2011 11:06 pm

This is a piece of text extracted from the Datastage help (F1).

User Variables activity properties
Use the user variable stage to define global variables within a sequence.

These variables can then be used elsewhere in the sequence, for example to set job parameters. Variables are used in the form stage_label.parameter_name, where stage_label is the name of the User Variable activity stage as given in the Diagram window.

The values of the user variables are set by expressions in the stage's properties. (For details, see "Expressions".)

You would most likely start a sequence with this stage, setting up the variables so they can be accessed by subsequent sequence activities. The exit trigger would initiate the sequence proper. You can also use a User Variable activity further into a sequence to change the value of a variable previously defined by an earlier User Variable activity.
The variables are defined in the Properties page for the stage. To add a variable:

Choose Add Row from the shortcut menu.
Enter the name for your variable.
Supply an expression for resolving the value of the variable.

jwiles · Post by **jwiles** » Mon Mar 07, 2011 11:50 pm

The reference you have cited is correct, however I think that you are implementing it incorrectly.

Based on what you've said in your earlier posts, you have the following:

UVA1: X: 0
UVA2: Y: UVA1.X = UVA1.X + 1

You say that the value of X never changes. I bet that the value of Y also never changes...and is always 0. When the expression UVA1.X = UVA1.X + 1 is evaluated, what is the result? Is 0 = 0 + 1?

In order to change the value of a variable defined in another udv activity, you must reference the full name in the Name column in the later udv activity:

UVA1: X: 0
UVA2: UVA1.X: UVA1.X + 1

What you did was to define a new variable Y (UVA2.Y) and assign it the value from the result of the comparison of UVA1.X and UVA1.X+1.

Make sense?

Regards,

sparq · Post by **sparq** » Wed Mar 09, 2011 12:17 am

Hi James,

That's it! It worked.

Now, tell me where in the documentation you found that???

Cheers

jwiles · Post by **jwiles** » Wed Mar 09, 2011 8:25 am

It isn't specifically documented, I just put two and two together based on experience. And....there was nothing saying you COULDN'T do that. Probably why I was kicked out of the computer lab for a week during university

Also, keep in mind that the UVA interface is similar to the standard transformer interface, in that you provide a variablename/columnname to calculate and a derivation/expression that performs the calculation. You can't change the value of variable within a derivation, only reference it.

So, to me it made sense that if I wanted to change the value of an existing User Variable (which are global to the sequence), I give it's fully-qualified name as the Variable Name in the later UVA.

Regards,