To pass the parameter to oracle stage in where class

[srividhya]

Hi ,
I have two jobs. My first job output is a DATE. I want to pass this date as a parameter to my second job to a oracle stage .
In the second job oracle stage i have a user defined query "select * from the XYZtable where the date ='#date_Param#'".

I tried various options
1.Iam working in parallel jobs , but i designed only the first job as server job and stored the data in a hash file with two columns date and dummykeyvalue(always 1). Then in my job sequence i have routine activity for ulitilityhashlookup. ---It is always throwing error "Table not found". -
2.all the jobs are parallel jobs . The result of first job in a sequential file. in my second job select * from the XYZ table. and a lookup stage with the seq file from first job for the date .
I think it will affect the performance , because of select all and then doing lookup.

Please help in doing this .

Thanks
Sri

[tardifma]

Hi.
I have a quick solution for you, but before, I just want to make sure that you cannot do it by using a SQL statement... Is you Date from the first job is in a different database as the SQL used in the second job??? Because, if not, there is probably a way to do it directly in a SQL statement with a Subselect....

Here is an easy way to do it...
In you first job, produce a Sequential file that contain only the date (without any column name).

In the job sequencer, use an Execute command activity and use the command cat as the command and the name of the file produced in the first job as parameter... The output of this command should be your date.

In your datastage job, define a date parameter.

In the job activity that call your that datastage job, use <name of execute command activity job>.$CommandOutput as value for your date parameter.

That's it.

Hope this will help.

[srividhya]

Hi tardifma,
Thanks for your quick response.
I tried this but iam getting the following error

checkroutine..JobControl (@Coordinator): Summary of sequence run
11:27:49: Sequence started (checkpointing on)
11:27:49: Previous_date (COMMAND cat /tmp/test) started
11:27:49: Previous_date finished, reply=0
11:27:49: Job_Activity_6 (JOB Copy_Unformatted_Deal_Job_test) started
11:27:49: Exception raised: @Job_Activity_6, Error calling DSSetParam(Previous_Date_Param), code=-4 [ParamValue/Limitvalue is not appropriate]
11:27:49: Sequence failed (restartable)

My output from the command line is
Executed: cat /tmp/test
Reply=0
Output from command ====>
10-31-2005


Thanks
Sri

[tardifma]

Hi.
Make sure that your parameter is set to a Date data type.

Also, make sure that the default format of your job is really %mm-%dd%yyyy....

If it still does not work after, try to pass a string data type... just to make sure that the parameter is well passed.

I did the exact samething for a number... and it worked well...

Thanks

[srividhya]

Hi,

My parameter type is string.
date format is also correct mm-dd-yyyy
in my query iam having select * from xyz where date = to_date ('date_param','mm-dd-yyyy')

when iam executing the job and passing the parameter its working fine. but when iam executing in the sequence iam again getting the same Error calling DSSetParam(Previous_Date_Param), code=-4 [ParamValue/Limitvalue is not appropriate]

Thanks
Sri

[srividhya]

Hi

It worked.
Convert(@FM,"",Execute_Command1.$CommandOutput)

Thanks
Sri.

[aramachandra]

I see it is working but pardon my question

Just trying to understand what the @FM system variable really does..

The help document and the parellel developers guide do not seem to have much on it

Is it setting the field delimiter...?


Arvind

[ray.wurlod]

When DataStage Engine picks up a multi-line entry or result it converts all the line termination characters to a special character called a "field mark". This means that you do not need to be concerned whether you are processing a UNIX or a DOS line terminator, and it also means that there are no line terminator characters in the resultant string.

Usually a "field mark" is a single-byte character whose ASCII value is 254. There are some circumstances where it may be mapped to something else, for example when accented European characters need to be handled. The value of a field mark character is always able to be obtained through the system variable @FM.

[srividhya]

Hi,

I have to give the file name as a parameter, but in execution command activity, i cannot give parameter.
cat /tmp/test --- working, but i need like this
cat #directory#/test.

please suggest me some other way.

Thanks.
Srividhya

[ray.wurlod]

Put cat as the command and #directory#/test as the command line arguments.

[daniel0623]

Hi ,
I have two jobs. My first job output is a DATE. I want to pass this date as a parameter to my second job to a oracle stage .
In the second job oracle stage i have a user defined query "select * from the XYZtable where the date ='#date_Param#'".

I tried various options
1.Iam working in parallel jobs , but i designed only the first job as server job and stored the data in a hash file with two columns date and dummykeyvalue(always 1). Then in my job sequence i have routine activity for ulitilityhashlookup. ---It is always throwing error "Table not found". -
2.all the jobs are parallel jobs . The result of first job in a sequential file. in my second job select * from the XYZ table. and a lookup stage with the seq file from first job for the date .
I think it will affect the performance , because of select all and then doing lookup.

Please help in doing this .

Thanks
Sri

Hi,Pls do as follows:
1.create a server routine which pass your first job's DATE to variable $userstatus.You may create server routine as follows:
Ans= DATE
Call DSSetUserStatus (DATE)
you can do this by Transformer Stage.
2.use job sequence,and pass 1st job's $userstatus to 2nd job's parameter:#date_Param#.Or you can do it by job control.

[srividhya]

Hi

Thanks Ray, It worked,

Put cat as the command and #directory#/test as the command line arguments'

Thanks
Sri