Fetch Data Based on List of Keys
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Fetch Data Based on List of Keys
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 ??
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 ??
Share to Learn, and Learn to Share.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Code: Select all
is too much. for instance 1 million !!
so this can be a costly affair.
That will reduce the overhead of bring entire data into staging area.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Fetch Data Based on List of Keys
One more thing u can do is select all the rows and restrict the rows at transformer by adding a constraint.
SMB
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".
asitagrawal posted the original question.
The correct spelling of the second person personal pronoun is "you".
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.