How can I do a dynamic select?

[tiagogen]

This is my problem:

I have the following file(sequencial or hashed):

cod:
1
2
3

And I need to do a select based on these numbers..
for instance:

select * from table where cod = ?

<cod> comes from my file (it's an index).

and so, I will put these results in a file.

How can I built somethig to do that??

NOTES:
*I can't unload my database to a hashed file cause its's too big(more than 200 million).

I tried:

DATABASE
ACCESS
TRANSFORMER FILE

FILE


But it seems don't work....

Anyone have an idea to do it or somothing like it (with a good performance)?

[gateleys]

You have mentioned that your Code can be in a sequential or hashed file. Assuming it is in a hashed file (as reference), you can stream your database input and do a lookup in the Xfmr. All you need to do is put the constraint Not(IsNull(HashLink.Code)) in your output link.

gateleys

[DSguru2B]

I think what he wants to do gateleys is to pass that cod as a job parameter to the select statement to just select those records which satisfy the where clause.
Tiagogen:
You need to build a unix script to read those numbers one by one and pass it as a job parameter to the dsjob call.
That will give you the best performance as you are just selecting the records you need.
Make sure that the job parameter is parth of your target file name as well. This way for each run of the select, a different file will be created, either prefixed or suffixed by the cod.

[tiagogen]

No DSGuru,

gateleys is right. That's I want to do...

I don't want use job parameteres...

I am trying do what he said at this moment!
thanks!

[rony_daniel]

I think what he wants to do gateleys is to pass that cod as a job parameter to the select statement to just select those records which satisfy the where clause.
Tiagogen:
You need to build a unix script to read those numbers one by one and pass it as a job parameter to the dsjob call.
That will give you the best performance as you are just selecting the records you need.
Make sure that the job parameter is parth of your target file name as well. This way for each run of the select, a different file will be created, either prefixed or suffixed by the cod.

DSguru What if i want to pass all the values in the file at one shot to the query like

select * from table where cod in (1,2,3...)

[DSguru2B]

DSGuru haan. You promoted me
Well if my server was on unix then i would read that file and build a parameter file. In the job , where i am doing a select, pass the 1,2,3.. as a job parameter.
If you want to do it via a routine and job sequence, then i would write a routine that would just opens the file containing 1,2,3...., read it and pass it as a job parameter to the next job that contains the select.
and my sql select would be

Code: Select all

select * from table where cod in (#ValRange#)
 

where ValRange is the job parameter that will have the values 1,2,3...
Hope i Answered your question.

[rony_daniel]

In my case I want to implement all these in one job. I have a db2 stage as the source which will have this query in it and the result i need to pass to the output stage. So what is the routine i should have which will populate the variable (#ValRange#) ? My job is in PX.

[DSguru2B]

Well, you really should have started a new post. Because this is a server forum and your question is for a PX job.
Anywho, If you want to all this in one job then you can do it utilizing a little bit of scripting.

Code: Select all

$JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt`
....dsjob -run $JOB_PARAM <ProjectName> <JobName>

the parameterfile.txt will have the value
-param ValRange=1,2,3....

Basically you will already have the parameterfile.txt with the value for ValRange. You will pass that as a parameter to the dsjob command. And it will dynamically select values from the table.

About the routine, can have a simple routine that just opens the file containing the values 1,2,3....
basically the routine will be something like

Code: Select all

      DEFFUN ReadVal(Arg1) CALLING "DSU.ReadParameter"
      Ans = ""
      ValRangeFile = ""
      ValRangeFile = Arg1

      OpenSeq ValRangeFile To fCfg
         Else Call DSLogFatal("Error opening file ":ValRangeFile)
      ReadSeq Line From fCfg then 
      Ans = Line
      CloseSeq fCfg
      RETURN(Ans)

You can add more error handling to the code. Arg1 will be the complete absolute path of the file that contains the values 1,2,3...
Hope it helps.
Regards,

[rony_daniel]

Sorry about that. Since tiagogen has already done with (i guess ) I thought we will continue in the same thread as it will be easier to communicate the idea. Yes but then the forum is totaly different...

My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?

I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.[/code]

[rony_daniel]

Sorry about that. Since tiagogen has already done with (i guess ) I thought we will continue in the same thread as it will be easier to communicate the idea. Yes but then the forum is totaly different...

My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?

I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.

[DSguru2B]

well you will need to get that value from a dataset and put it in a sequential file.
As per the routine, you can do that in a sequence job. In the properties of job activity, click on the Value Expression of the Parameter ValRange and click on Routine and there you can call your routine. The output of that routine will automatically be the value of the job parameter.

[rony_daniel]

well you will need to get that value from a dataset and put it in a sequential file.
As per the routine, you can do that in a sequence job. In the properties of job activity, click on the Value Expression of the Parameter ValRange and click on Routine and there you can call your routine. The output of that routine will automatically be the value of the job parameter.

But what if the job is not a seqence job?

[DSguru2B]

Then make one. A simple job sequence that executes one job and goes to a notification activity or something.
From within the job you cannot set and use a job parameter. You will have to set that explicitly from outside the job.
Thats the limitation.
Regards,

[kumar_s]

If needed the dataset can be 'Dump'ed into a sequential file in the same script.