HowTo parametrize dataset?
Moderators: chulett, rschirm, roy
HowTo parametrize dataset?
Hi All
I have job parameter as list. How I can put dataset with onest colum to my job_parameter?
Thenx.
I have job parameter as list. How I can put dataset with onest colum to my job_parameter?
Thenx.
Alex
Re: HowTo parametrize dataset?
Kadet,KadetG wrote: I have job parameter as list.How I can put dataset with onest colum to my job_parameter?
Thenx.
Do you mean that you have a parameter for the Data Set defined as a "List" in the job properties? And did you mean a Data Set with one column?
If you want to pick up the parameter values from a list - then you need to add the (parameter) values to the list when you define it - and then choose the correct value from the list.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It is not legally possible other than manually.
You seek to load values from a Data Set into the list values associated with a List type job parameter. That has to be done at design time. Do you have the Data Set available at that time?
Which of the values (if any) becomes the default value?
Changing job parameters at run time is explicitly prohibited.
You seek to load values from a Data Set into the list values associated with a List type job parameter. That has to be done at design time. Do you have the Data Set available at that time?
Which of the values (if any) becomes the default value?
Changing job parameters at run time is explicitly prohibited.
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.
Hi
Ok. So... I have very big table and I want to copy records from table which correspond my parameters. I want to put my parameters in SQL request (select a, b, c from d where a = #param#) for reducing number extracted records and reducing time of job. But parameters is not constant value. It should be fill from other job. About that job I asked in previos message.
I want to create job sequence with 2 jobs. First job is fill parameters and second job is use it in SQL. But I don`t know how I can put dataset to parameter Is it possible?
Hope I cleaned my problem.
Thenx.
Ok. So... I have very big table and I want to copy records from table which correspond my parameters. I want to put my parameters in SQL request (select a, b, c from d where a = #param#) for reducing number extracted records and reducing time of job. But parameters is not constant value. It should be fill from other job. About that job I asked in previos message.
I want to create job sequence with 2 jobs. First job is fill parameters and second job is use it in SQL. But I don`t know how I can put dataset to parameter Is it possible?
Hope I cleaned my problem.
Thenx.
Alex
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What you envisage is not possible in the manner you envisage it.
But you might be able to load one parameter with a string that is a comma-delimited list of values and somehow get that into an IN clause in the WHERE part of your extraction SQL. Something like:
But you might be able to load one parameter with a string that is a comma-delimited list of values and somehow get that into an IN clause in the WHERE part of your extraction SQL. Something like:
Code: Select all
SELECT column_list FROM table WHERE colname IN (#ValuesList#);
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.
Parameters are stored in 2 places. I have thought about this too. Lists are stored I think with tabs between each value. I would have to dig into the code to be sure. DS_JOBOBJECTS stores the first set of values. You would have to understand a lot about the DataStage engine to update these values. The second place is for runt time values. This gets populated normally when you compile a job. Both of these are dangerous to update.
Mamu Kim
I think a better and safer approach would be to load that particular column in a temp table and do a sql select with a sub select.
Code: Select all
SELECT column_list FROM table WHERE colname IN
(SELECT colname from TempTable);
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
May be you can do something like this...
1) LISTFILE= <parameter values created by 1st job>
2) Shell Script: Prepare comma delimited parameter string
SWITCH=0
while read NextValue
do
if [ $SWITCH -eq 1 ]
then
ParameterX="$ParameterX,"
fi
SWITCH=1
ParameterX="$ParameterX\'$NextValue\'"
done < $LISTFILE
3) Run the datastage job:
dsjob -run project DSJOb -<parameterX>
4) Inside SQL, now you can use #parameterX#
Hope this would helps...!
1) LISTFILE= <parameter values created by 1st job>
2) Shell Script: Prepare comma delimited parameter string
SWITCH=0
while read NextValue
do
if [ $SWITCH -eq 1 ]
then
ParameterX="$ParameterX,"
fi
SWITCH=1
ParameterX="$ParameterX\'$NextValue\'"
done < $LISTFILE
3) Run the datastage job:
dsjob -run project DSJOb -<parameterX>
4) Inside SQL, now you can use #parameterX#
Hope this would helps...!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: