To pass the parameter to oracle stage in where class
Moderators: chulett, rschirm, roy
To pass the parameter to oracle stage in where class
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
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.
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.
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.
The Brute
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
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
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
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
The Brute
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
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
-
- Participant
- Posts: 55
- Joined: Tue Sep 20, 2005 10:58 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 34
- Joined: Tue May 31, 2005 8:17 pm
- Location: ShangHai,China
Re: To pass the parameter to oracle stage in where class
Hi,Pls do as follows:srividhya wrote: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
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.