MVSFORUMS.com

[nivedita]

Hi,

I have 1 million records in a table, when I make a select * on the table using cursors in my application a resultant table is created, how can a buffer pool hold so many rows? If there is no space in the buffer pool were will it store this result?

Any more details on FETCH and OPEN cursor?

[kolusu]

Nivedita,

Use the Prefetch mechanism for reading a set of pages, usually 32, into the buffer pool with only one asynchronous I/O operation instead of the entire result set.

Check this link for detailed explanation of Interpreting data prefetch

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[CZerfas]

It is a common missunderstanding, that opening a cursor and fetching the rows leads to a physical result table in the bufferpool, from which the rows are returned to the requesting program.

This materialization only happens when the system uses "view materialization" for you (rare) or you are doing sorts (if you are using ORDER BY, DISTINCT, ...). In that case the sort tablespaces (in DSNDB07 database) are filled with the resulting rows and the fetches grab their rows from these tablespaces.

Therefor the bufferpools don't have to keep millions of rows.

regards
Christian

[nivedita]

What if ORDER BY clause is not used? Then how will it work?

[CZerfas]

If you don't use a sort, then no materialization occurs. That means, that a data page is loaded into the buffer pool, scanned for qualifiying rows and those are fetched one by one by the program. If the page is exhausted, the next page is examined in the buffer pool, and the already searched page is marked as "stealable" in the buffer pool.
After that, the used space by that page is free for the buffer pool manager to be overwritten.

regards
Christian