Passing Runtime parameter using $UserStatus

[sohasaid]

Dears,

I'm trying to pass a runtime parameter to a job which holds a value from a previous one. i.e. selecting max(date) from job1 then passing the result in where clause of job2.
After searching, I've followed the following scenario:
viewtopic.php?t=132137&highlight=overwr ... eter+value

Not directlyTypically, that '1st ODBC' work would be in one job and the results would be landed somewhere, say in USERSTATUS. Then a Sequence job would read the value from user status and assign it to a Job Parameter in a second job where the '2nd ODBC' would use it in its where clause.

Job design is:

Code: Select all

Job1_Activity --> UserVariableActivity --> Job2_Activity

Steps:
1- Create a server routine called 'DSSetUserStatus' to set the value of $UserStaus variable:

Code: Select all

Call DSSetUserStatus(Arg1) 
Ans =Arg1

2-At job1, select max(date) in max_date column, then use transformer to create a stage variable which has a drivation and sending the output to a dummy file:

Code: Select all

DSSetUserStatus(max_date)

3-At job2, in where clause set date = #Param.MaxDate#
4-Now the $UserStatus variable should hold the max_date value.

My questions:
1- At job1_Activity, what is the required expression for 'UserStatus' expression?
2- At UserVariableActivity, after adding a new row what's the expected expression also?

Sorry for long post.

Regards.
[/code]

[ray.wurlod]

1. Job1_Activity.$UserStatus - pick it from the list of operands in an expression.

2. Job1_Activity.$UserStatus - pick it from the list of operands in the expression editor.

[sohasaid]

Ray, I've tried to use 'Job1_Activity.$UserStatus' at Job1_Activity and UserVariableActivity but no output is generated from job2 which means that it didn't receive the parameter at run time. Also, I've examined the log of job2 and the select statement is receiving the default value of parameter?

Also, I'm confused about where & how to map extactly the value @UserStatus to a specific parameter in a parameter set, #MaxDate# for example?

Thanks for reply.

[ray.wurlod]

@UserStatus is not available in other processes.

Look at the job log for job 2 to see what it actually received. Did you pick the activity variable from the expression editor when filling in the parameter value expression in the Job activity Job2_Activity?

[sohasaid]

Did you pick the activity variable from the expression editor when filling in the parameter value expression in the Job activity Job2_Activity?

It worked out, Ray. Your question was the key. At job2, I was defining a parameter set not a single parameter on job2 level, so the Value Expression for it at Job2_Activity was always 'As pre-defined' .

Steps:
1- I've created a new single parameter on the job2 level #MaxDateVar#
2- use #MaxDateVar# in job2's where condition to retreive data based on it
3- At Job2_Activity stage, assign 'Job1_Activity.$UserStatus' to #MaxDateVar#

So, I've found no need to use UserVariableActivity stage, I removed it and it worked fine.

Code: Select all

Job Design: Job1_Activity -->Job2_Activity

Thanks a lot, Ray for your support & time.

Regards.

[yaminids]

Ray,

Would it be possible to pass two variables using UserStatus?

I followed the design, explained above, and prepared 2 jobs and 1 Sequence. Also, I created a customer routine to get the user status

Call DSSetUserStatus(Arg1)
Ans =Arg1

Job-1 --> passes an argument to the routine
Job-2 --> selects data from db based on the variable retrieved by $USERSTATUS

I'm working on a new project which requires me to pass more than 1 variable from Job-1 to Job-2. Would I be able to do that?

Thanks in advance
Yamini

[chulett]

You can put any number of values in $UserStatus - in a delimited string. Then your call to get any value back would just need to include the field number from the delimited string which you then parse out and return.

[yaminids]

Craig,

Thanks for your response.

To make myself clear, should I change the function to the following

Call DSSetUserStatus("Arg1,Arg2")
Ans =(Arg1, Arg2)

I'm using Stage variables in Job-1 to call the function. How can I pass values to the 2 variables in this job?

Thanks
Yamini

[yaminids]

Craig,

On further analysis I found that we can't pass more than 1 argument to the routine DSSetUserStatus.

Can you please explain how I can put more than 1 value in $USERSTATUS (in a delimited string)

Thanks
Yamini

[chulett]

You concatenate them together with the delimiter of your choice, in other words you create the delimited string to pass to the function. No change is needed in the function itself, all it ever does is take the passed in value, store it in UserStatus and then pass it back out.

So rather than calling it like this:

CallDSSetUserStatus(Arg1,Arg2)

You would call it like this:

CallDSSetUserStatus(Arg1:"|":Arg2)

[ray.wurlod]

And then, when you need to unpack the delimited string, use the Field() function. The first element is returned by

Code: Select all

Field(JobActivity.$UserStatus, "|", 1, 1)

and the second element is returned by

Code: Select all

Field(JobActivity.$UserStatus, "|", 2, 1)

[yaminids]

Ray/Craig,

Thanks a lot for your help

-Yamini