query should fetch values from file
Moderators: chulett, rschirm, roy
query should fetch values from file
Hi guys,
I need to desing a job where i need to fetch some data from db2 database but only for selected values and those values would be stored in a file.
Ex: select * from orders where orders_id={These values are in a file} with ur;
I cannot use an IN clause coz there are some 30,000 orders for which i need to fetch data from the database.
Any help would be appreciated. Thanks.
I need to desing a job where i need to fetch some data from db2 database but only for selected values and those values would be stored in a file.
Ex: select * from orders where orders_id={These values are in a file} with ur;
I cannot use an IN clause coz there are some 30,000 orders for which i need to fetch data from the database.
Any help would be appreciated. Thanks.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: query should fetch values from file
Put the file values (order id's I presume...) into a Hash file and make it as a key column. Then do a lookup based on the Order_id field and send in all the columns where the values are found!DSbox61 wrote:Hi guys,
I need to desing a job where i need to fetch some data from db2 database but only for selected values and those values would be stored in a file.
Ex: select * from orders where orders_id={These values are in a file} with ur;
I cannot use an IN clause coz there are some 30,000 orders for which i need to fetch data from the database.
Any help would be appreciated. Thanks.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
Thanks Vivek. Let me put forward my understanding of your suggestion. You're saying, get the order_id into hash file and get everything from orders table (select * from orders) in db2 stage and then do look up on key columns(order_id) in the transformer stage. But, the problem here is if i do {select * from orders} in db2 stage, it would fetch me few millions of rows. Whereas, i need to fetch only a few thousands of orders.
DB2----->Tx----->SeqFile
^^
||
||
HashFile
is there any table i can create within datastage which i can use in my query within db2 stage?
DB2----->Tx----->SeqFile
^^
||
||
HashFile
is there any table i can create within datastage which i can use in my query within db2 stage?
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
You understood it right. If you are fetching/selecting data from table without any conditions, it should not take much time. When you are looking up with a Hash file, it should be fairly quick especially when you are loading into a Seq. File.DSbox61 wrote:Thanks Vivek. Let me put forward my understanding of your suggestion. You're saying, get the order_id into hash file and get everything from orders table (select * from orders) in db2 stage and then do look up on key columns(order_id) in the transformer stage. But, the problem here is if i do {select * from orders} in db2 stage, it would fetch me few millions of rows. Whereas, i need to fetch only a few thousands of orders.
DB2----->Tx----->SeqFile
^^
||
||
HashFile
is there any table i can create within datastage which i can use in my query within db2 stage?
I am suggesting you this because we use something like this design in our shop and the jobs are fairly responsive and quick.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
That's just a silly and stupid restriction. A 'connection' account can easily have its own 'schema' (whatever the DB2 equivalent is called) where work tables can be created without affecting the 'real' tables or tablespace. Your ability to work is really hobbled by an arbitrary restriction like that. [shakes head]DSbox61 wrote:Thanks Craig. But I'm not allowed to create/drop/delete any table on production database using datastage. Is there any other work around?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
I agree with Craig. There should be a WorkDB schema for you to create a table. If not, your DBA can create one for you and you can always truncate and load the data into it.chulett wrote:That's just a silly and stupid restriction. A 'connection' account can easily have its own 'schema' (whatever the DB2 equivalent is called) where work tables can be created without affecting the 'real' tables or tablespace. Your ability to work is really hobbled by an arbitrary restriction like that. [shakes head]DSbox61 wrote:Thanks Craig. But I'm not allowed to create/drop/delete any table on production database using datastage. Is there any other work around?
However, if that kind of restriction should exist, then design the job as per my earlier suggestion and look at the performance!
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm