A basic question on Database stage

vnspn · Post by **vnspn** » Thu Jul 19, 2007 3:46 pm

Hi,

We have a basic question on the way a Database stage works. Suppose we have a SQL on a source database stage that returns 2 million rows, how does DataStage handles it?

Does DataStage executes the SQL and holds the 2 million rows in its memory and passes a chunk by chunk of rows to the next stage or does it process it some way different? If the former happens, then it requires a lot of memory to hold the 2 million records in its memory, right...

Thanks.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 19, 2007 5:39 pm

Typically the database server will establish the result set and the DataStage job will establish a cursor into the result set, fetching an array of rows at a time. There are variations on this theme, but in no case (in server jobs) does DataStage cache the entire data set in memory.

vnspn · Post by **vnspn** » Fri Jul 20, 2007 6:42 am

Thanks for the information Ray!

You say that, in Server Jobs, DataStage does not cache the entire data set in memory. Is it the same way in Parallel Job as well or different?

ray.wurlod · Post by **ray.wurlod** » Fri Jul 20, 2007 10:36 pm

Yes. No. Maybe. Depends on the stage type.

Your question was in the server forum, so got a server job answer.

vnspn · Post by **vnspn** » Mon Jul 23, 2007 8:30 am

Ok, thanks.

I would post this in Parallel job forum to get the answer for the parallel job stages.