Output values of one job used as Parameter values in another

DS_MJ · Post by **DS_MJ** » Fri May 12, 2006 8:21 am

Hello:

Would like to use the output values of one job as my parameters input values in the second job.

Systems environment
Ascential Datastage(7.5.1A) running on Linux
R/3 PACK (5.2 unicode) running on Linux
SAP R/3 4.7 unicode running on AIX (64bit) Squadron server
DB2 IW running on either AIX or MVS.

Example:

JOB 1
Extract the the values of the two columns from DB2 API table into a Hashed file.
DB2 ----> Transform ---> Hashed File

OUTPUT of the 2 columns is as follows:
Col_A = 20060906
Col_B = 72000

JOB 2
We want to extract data from ABAP Extract from the value greater then or equal to the output values in the Hashed file. Hence want the values to be input values of the parameters in this job.

ABAP Extract ------> Transform -----> Seq File
Parameters and values defined
#Col_Dt# = 20060906
#Col_Tm# = 72000

Use these parameters in ABAP Extract to extract data.

How do I pass on Values as Parameters in this 2 Job?

Can one evaluate this return value in Sequencer and pass them to the second job via UserVariables Stage. If so can one give an example...?

Would appreciate any ideas on how to accomplish this.

Thank you

DSguru2B · Post by **DSguru2B** » Fri May 12, 2006 9:09 am

It depends on how you plan to control the execution. If its via a sequencer then you can use user variable activity or even a routine activity to open the file where you have your parameter values. In this case you will need to write your date and time to a sequential file instead of a hash file.
If you are planning to control the execution via a unix script then in that case also you need to write to a seq file, read the file in your script and pass it as a parameter to your second job.
Regards,

gateleys · Post by **gateleys** » Fri May 12, 2006 9:36 am

I had this information with me from this forum. I could not locate it again, but the posting is with me. It goes as follows-

gateleys

Use UtilityRunJob to kick off a job from another job, and passing those values as parameter.
Example:
Lets say that you need to pass V1 and V2 to Job5. And lets say that you already had Job5.

Develop a job - Job1- that read the SQL Server table and create a sequential file with two columns - C1, C2- that should have the value that you will pass as parameter values.

Second develop a job -or add thsi stages in previous job- that will contain three stage: a Seq files stage, a transformer and another Seq file Stage, same order, conected with Links. Read the sequential file from previous step, in the first Seq Files Stage.

The last Seq File declare a column as dummy.

in the transformer fill the derivation to the dummy column with:

UtilityRunJob('Job5',"Par1=":C1:"|Par2=":C2, 0,0)
This job will execute Job5 with Parameter Par1 with using value C1 and Parameter Par1 using value C2 .

Notice that you must create and string to define all your parameter as :

" Paremeter = value | parameter2 = value | paramenter3 = value | ...| ParamenterN = ValueN"

the two remaining values: 0,0 mean Unlimeted Row limit ( Process all rows) and Unlimited warning ( Accept all warning ). You should change this according to your needs.

DSguru2B · Post by **DSguru2B** » Fri May 12, 2006 9:40 am

Pretty smooth gateleys

gateleys · Post by **gateleys** » Fri May 12, 2006 11:37 am

DSguru2B wrote:Pretty smooth gateleys

Honestly DSguru (you've been promoted), I keep certain useful postings saved in my system so that I can refer to them when required. I started to do this since sometimes, you cannot get to the post that you have read sometime back. For example the above post; even when I copy and paste certain lines in 'search', it does not return any post. So, could not provide the link, but rather opted to paste what I had. So, no smooooth operation going on from my side.

The lousy part is that DS_MJ has not responded if the technique served his purpose.

DS_MJ · Post by **DS_MJ** » Fri May 12, 2006 12:20 pm

Thanks gateleys and DSguru2B really appreciate your prompt replies.

Sorry Gateleys I thought I should try it first and then reply back. But maybe I should have responded earlier ....

I did what you said.
1. Develop a job - Job1- that read the SQL Server table and create a sequential file with two columns - C1, C2- that should have the value that you will pass as parameter values.

2. In the same job added the the transform and the Seq File.

3. Have Job 5 which has the following stages:

Abap Extract ----> Transform ----> DB2 Table

Need help with the following that you mentioned: How do I set the parameters par1 and par2...?

[/quote]
Notice that you must create and string to define all your parameter as :

" Paremeter = value | parameter2 = value | paramenter3 = value | ...| ParamenterN = ValueN"
[quote]

DSguru2B · Post by **DSguru2B** » Fri May 12, 2006 12:41 pm

OOPS gateleys, he heard ya. just kidding guys.
True, what i do is just add the post to my favourites. That really helps me.
chao

DSguru2B · Post by **DSguru2B** » Fri May 12, 2006 12:43 pm

And thanks for the promotion gateleys but i dont deserve it yet, still a long way to go. Thanks for the encouragement though

gateleys · Post by **gateleys** » Fri May 12, 2006 12:55 pm

DS_MJ wrote: How do I set the parameters par1 and par2...?

With your values of Col_A and Col_B. Aren't they supposed to be the param values?

gateleys

DS_MJ · Post by **DS_MJ** » Fri May 12, 2006 1:25 pm

gateleys wrote:
DS_MJ wrote: How do I set the parameters par1 and par2...?
With your values of Col_A and Col_B. Aren't they supposed to be the param values?

gateleys

yes values of Col_A and Col_B in Job 1 are suppose to be the param values for Job5 parameters. Let me check my job ......!!!

DS_MJ · Post by **DS_MJ** » Fri May 12, 2006 4:32 pm

Notice that you must create and string to define all your parameter as :

" Paremeter = value | parameter2 = value | paramenter3 = value | ...| ParamenterN = ValueN"

My job is failing because I am not doing the above correctly....!!!

This is what I have done.

Job 1 (Runs Fine)
Db2 Table ----> Transform ----> Seq File

JOB 2 ( Job finishes with 2 warnings)

Seq File (from previous job) ---> transform ---> Seqfile (Dummy file one column)

Parameters defined:
SeqFileName
DummyFile

In the Transform did the following:

UtilityRunJob ('JOB_5', "Par_1=" : COL_A : "Par_2=" : COL_B, 0,0)

Got 2 WARNING messages in director:

1 - Jobname.xfm (UtilityRunJob): Job Failed: JOB_5
2 - JobName.Dummy.dummy_out: nls_map_buffer_out() - NLS mapping error, row 1 (approx), row = ""JOB_5=3"

JOB 5 (Job Failed)

This job is a simple extract job using the output values of JOB 1 as params to extract the data.

ABAP EXTRACT ----> Transform ---> Seq File

Parameters defined:
PAR_1 = Left the value field blank since it has to be COL_A value
PAR_2 = Left the value field blank since it has to be COL_B value
Output_File

In the Director
Jobname: All Job Parameters must have values

Thanks and sincerely appreciate your patience

us1aslam1us · Post by **us1aslam1us** » Fri May 12, 2006 5:08 pm

UtilityRunJob ('JOB_5', "Par_1=" : COL_A : "Par_2=" : COL_B, 0,0)

Code: Select all

UtilityRunJob (JOB_5, "Par_1=" : COL_A : "Par_2=" : COL_B, 0,0)

Sam

chulett · Post by **chulett** » Fri May 12, 2006 5:12 pm

Seems to be more about the fact that the 'parameter value pairs' must be pipe delimited as gateleys showed in his post... and DS_MJ is not doing that.

DS_MJ · Post by **DS_MJ** » Mon May 15, 2006 10:32 am

Hello chulett

chulett wrote:Seems to be more about the fact that the 'parameter value pairs' must be pipe delimited as gateleys showed in his post... and DS_MJ is not doing that. ...

The params

UtilityRunJob ('JOB_5', "Par_1=" : COL_A : "Par_2=" : COL_B, 0,0) is working fine without the "|" in my job . I am able to pick the param values and put it in the dummy file when I run JOB2

This is what I see in the director log:
JOB2 (UtilityRunJob): Setting Job Param - COL_A Setting to 20050906 COL_B = 72000

However how do I get JOB5 to read the param values that JOB2 puts in the dummy file.....? JOB2 finishes with warning that JOB5 failed.

In JOB5 when I look in director it says in the director log "JOB5.All Job Parameters must have values"

I want the JOB2 that picks the COL_A and COL_B values to be put into JOB5 and not just in the dummy file. Its not putting the values in JOB5 hence JOB5 is failing.

Thanks in advance.

DSguru2B · Post by **DSguru2B** » Mon May 15, 2006 10:42 am

Job5 is not able to pickup the parameters because it is not in the format that it requires. It should be pipe delimited. Gateleys provided you with the correct format.

gateleys wrote: Notice that you must create and string to define all your parameter as :

" Paremeter = value | parameter2 = value | paramenter3 = value | ...| ParamenterN = ValueN"

In order to bring your parameters in that format, you have to use the command that gateleys provided

Code: Select all

UtilityRunJob('Job5',"Par1=":C1:"|Par2=":C2, 0,0)