HowTo parametrize dataset?

[KadetG]

Hi All

I have job parameter as list. How I can put dataset with onest colum to my job_parameter?

Thenx.

[thebird]

I have job parameter as list.How I can put dataset with onest colum to my job_parameter?
Thenx.

Kadet,

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.

[KadetG]

Hi

Yes, I mean that I have a job parameter (job properties->Parameters...). And I have dataset with one colunm. I have it from db2_udb_api for example. And I want to add the values to the list from my dataset. Is it possible? Please tell me as.

[Kirtikumar]

your problem statement is not clear and its difficult what to comprehend what you want to achieve.

Can you please post it clearly?

[ray.wurlod]

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.

[KadetG]

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.

[ray.wurlod]

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:

Code: Select all

SELECT column_list FROM table WHERE colname IN (#ValuesList#);

[KadetG]

I think it is possible... But how I can do it? How I can to fill parameter from dataset? Can you say about that step by step?

[kduke]

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.

[DSguru2B]

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);

[bsreenu]

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...!

[ray.wurlod]

DS_JOBOBJECTS contains only the design-time default values of parameters, not the run-time defaults (set in Director) or the actual values used in a run.

[KadetG]

Hi All

Thank you for all.

2 DSguru2B:
I don`t must create and store any temporary data on database May be I can use something other?

2 bsreenu:
Hmm... Good idea. Are you mean job which store dataset in file in first point?