query should fetch values from file

[DSbox61]

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.

[chulett]

Load the file to a work/temp table and join it to your source.

[DSbox61]

Thanks Craig. But I'm not allowed to create/drop/delete any table on production database using datastage. Is there any other work around?

[vivekgadwal]

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.

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]

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?

[vivekgadwal]

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?

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.
I am suggesting you this because we use something like this design in our shop and the jobs are fairly responsive and quick.

[chulett]

Thanks Craig. But I'm not allowed to create/drop/delete any table on production database using datastage. Is there any other work around?

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]

[vivekgadwal]

Thanks Craig. But I'm not allowed to create/drop/delete any table on production database using datastage. Is there any other work around?

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]

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.
However, if that kind of restriction should exist, then design the job as per my earlier suggestion and look at the performance!

[DSbox61]

Thanks Guys. I'll try to convince our DBA for this. If not then i would go with the regular look up design.

[ray.wurlod]

A hashed file (note - it is not "hashed" file) solution would be very efficient.

[DSbox61]

Thanks guys. I convinced my DB on creating a temp table.

[vivekgadwal]

Thanks guys. I convinced my DB on creating a temp table.

Did you try the Hashed file approach?