Max and Min value as input to DB2 stage

[Nicole]

Hi everyone. I want to be able to query for a max and min value each time a job is run, and then pass the max and min value into the DB2 stage as input parameters to the query for that stage, but of course, I can't create an input link into a DB2 stage so what would be the best way to go about this? Am I thinking about this in the wrong way? Help. Thanks!!

Nicole

[DSguru2B]

Use it as a subselect sql statement. Something like

Code: Select all

Select a, b, c
from mytable
where a between (select max(x) from yourtable) and (select min(x) from your table);

Something like that.

[Nicole]

That is a good idea...BUT the max and min values are on a table which is on a totally different platform. Of course, the obvious option is to query for the max and min and plug them in before running the job, but we were trying to find a way to do it within the one job.

Use it as a subselect sql statement. Something like

Code: Select all

Select a, b, c
from mytable
where a between (select max(x) from yourtable) and (select min(x) from your tab ...[/quote]

[Sainath.Srinivasan]

Create the sql into a file in the pre-job and use external SQL file in the job

[Nicole]

I don't think this one will work because we still have a SQL statement in the DB2 Stage, but we want to run the first sql to get the max and min to pass as values in the where clause of the sql in the DB2 Stage.

Thank you for the suggestion.

Create the sql into a file in the pre-job and use external SQL file in the job

[DSguru2B]

How huge is your source data? If its not that huge, you can get your min and max value and store it in a hashed file and instead of using it in the where clause, use the min and max to filter the data in the constraint section of the transformer.

[ray.wurlod]

In a parallel job? Please elaborate.

[DSguru2B]

Totally missed that. Argh...

[ray.wurlod]

Actually it could be done using an External Source stage, but I wondered what you had in mind. But there's no advantage in a parallel job in using a hashed file - a sequential file would do as well - because the entire reference data set is imported into memory.

[DSguru2B]

I was going to say, if the min and max values are numerical values, then the OP could get them and create the values in between and load it into a lookup file set or perhaps a dataset. Then do a lookup/join and filter the records.