Fetch Data Based on List of Keys

[asitagrawal]

Hi All,


I have a case like this:

Db2 Stage > Transformer > SequentiaFile

From the DB2 database, I want to fetch the data from the below query:

SELECT SETID, RECNAME,BUSINESS_UNIT, LEDGER
FROM PS_MYTABLE
WHERE SETID = ? AND RECNAME = ?

Now, the list of keys I have is:

SETID RECNAME
----- ---------
SHARE RECNAME1
US001 RECNAME2
AUS01 RECNAME3

For each pair of key, the result can be multiple rows.

One way to achieve this could be to frame a dynamic clause, set it as the job parameter and call the job using DSRunJob etc.
Eg.

SELECT SETID, RECNAME,BUSINESS_UNIT, LEDGER
FROM PS_MYTABLE
WHERE #WHERE_CLAUSE#


#WHERE_CLAUSE# will be like SETID = 'SHARE' AND RECNAME ='RECNAME1' and call the job, after setting its job parameter and so on...

What would be a better way to achieve this ??

[rafik2k]

One approach is create a hash file for the list of keys.
select all data from db2 database as it is and then in the transformer join this data with hash file using setid and recname, then pull the data into seq file

[asitagrawal]

Ya, I agree with this approach too.. but in my actual case, the volume of data to be fetched initially and then join with the hashed file in the transformer, is too much. for instance 1 million !!
so this can be a costly affair.

[asitagrawal]

Ya, I agree with this approach too.. but in my actual case, the volume of data to be fetched initially and then join with the hashed file in the transformer, is too much. for instance 1 million !!
so this can be a costly affair.

[rafik2k]

Code: Select all

 is too much. for instance 1 million !! 
so this can be a costly affair. 

If your list of keys is in the table and then perform join at the database level itslef.
That will reduce the overhead of bring entire data into staging area.

[chulett]

I'm a little lost on your requirements. This 'list of keys' in your example - are you saying you'd want all three to be fed to your job, one at a time so their results are in three separate output files? Or do you want all of them run at once?

For the former, that sounds like a LOOPing Sequence job where UserVariables control the iteration. For the latter, I'd suggest taking your 'list', loading it into a work table in your database and then joining the two tables together.

Either that or take another stab at explaining your situation.

[ray.wurlod]

One million keys can be easily handled by a hashed file stage.

It is true that three keys can also easily be handled by a hashed file stage. In this case the million rows are streaming through, do not have to be kept in memory other than one at a time or a buffer's worth at a time.

As Craig notes, your actual requirement is unclear. Please describe your expected result set.

[baglasumit21]

One more thing u can do is select all the rows and restrict the rows at transformer by adding a constraint.

[ray.wurlod]

It's probably fair to say that U doesn't want to.

asitagrawal posted the original question.

The correct spelling of the second person personal pronoun is "you".