Browsing through a table

[Jurgen Haan]

Hello all you Gurus.

I'm trying to find out how to fetch sequential blocks of data from a
table in a most efficient way.

Let's suppose the following:
I have a reasonably large table (say 1Milion records) containing part
information (parts of machinery (eg. the SPJ database from C.J. Date :P)
) placed in a DB2 database (stinger).

I have a client which uses a grid to browse through the data.
Let's say I want to use the grid to browse through all the parts stored
in that table (don't ask why I just wanna). Obviously I don't want to
send the entire table to the client, so I want to browse through the
data using blocks (lets say each of 100 records).

When I reach the end of one block the other block should be loaded and
displayed.

During the day data is stored in the database.
The primary key is a part number.

One way to do this is by selecting the first (blocknr * 100) rows,
reverse sort it and then select the first 100 rows of that resultset.
using this method on a rather large table can be painful, especially
when you get to the blocks near the end of the table.

I'm experimenting with a Multidimensional Table (MDC).
Using a special blocknumber column in the table I can fysically store
the parts in groups of 100 records, but during the administration window
the blocknumbers need to be redistributed (and REORG-ed) to keep the
blocksize near 100.

In short:
I just want to be able to browse through a table using blocks, but I
don't know how to do this in the most efficient way.
Does anyone have any opinion on this.

(BTW, it's not my idea to browse through a huge table, and I really
don't want to, but since it is not my descision to make.)

-R-

[Serge Rielau]

Jurgen Haan wrote:

Hello all you Gurus.

I'm trying to find out how to fetch sequential blocks of data from a
table in a most efficient way.

Let's suppose the following:
I have a reasonably large table (say 1Milion records) containing part
information (parts of machinery (eg. the SPJ database from C.J. Date :P)
) placed in a DB2 database (stinger).

I have a client which uses a grid to browse through the data.
Let's say I want to use the grid to browse through all the parts stored
in that table (don't ask why I just wanna). Obviously I don't want to
send the entire table to the client, so I want to browse through the
data using blocks (lets say each of 100 records).

When I reach the end of one block the other block should be loaded and
displayed.

During the day data is stored in the database.
The primary key is a part number.

One way to do this is by selecting the first (blocknr * 100) rows,
reverse sort it and then select the first 100 rows of that resultset.
using this method on a rather large table can be painful, especially
when you get to the blocks near the end of the table.

I'm experimenting with a Multidimensional Table (MDC).
Using a special blocknumber column in the table I can fysically store
the parts in groups of 100 records, but during the administration window
the blocknumbers need to be redistributed (and REORG-ed) to keep the
blocksize near 100.

In short:
I just want to be able to browse through a table using blocks, but I
don't know how to do this in the most efficient way.
Does anyone have any opinion on this.

(BTW, it's not my idea to browse through a huge table, and I really
don't want to, but since it is not my descision to make.)

You can use a scrollable cursor. This feature is supported through
various client interfaces such as JDBC and CLI.
The scrollable cursor will handle the metadata of the numbering and
bookmarking internally. No need to update your fact able.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jurgen Haan]

Serge Rielau wrote:

You can use a scrollable cursor. This feature is supported through
various client interfaces such as JDBC and CLI.
The scrollable cursor will handle the metadata of the numbering and
bookmarking internally. No need to update your fact able.

Cheers
Serge

We're using a custom application using the DB2 development client libs.
these libs have a function in the API for using a scrollable cursor?
Didn't know something like that existed. Very nice.

Thank you
-R-

[Jurgen Haan]

Serge Rielau wrote:

You can use a scrollable cursor. This feature is supported through
various client interfaces such as JDBC and CLI.
The scrollable cursor will handle the metadata of the numbering and
bookmarking internally. No need to update your fact able.

Cheers
Serge

Woops one proble though.
We're using thin clients connecting to loadbalanced application servers
which connect to the database. Because of the fact that the a call can
be directed to any application server, the calls are stateless. (each
call is a seperate connection with its own cursor)

So it has to be done in a higher level.

Is it possible to place a cursor on a given position? (guess not).
And lets say I cannot influence the cursor anymore, the only thing I can
do is write some fancy SQL and change some of the algorithms used by the
grid. In that situation, is there anything I can do to improve
performance over the "select first(100) from (select first(blocknumber *
100) * from very_large_table order by PK descending)".

-R-

[Serge Rielau]

Jurgen Haan wrote:

Serge Rielau wrote:

You can use a scrollable cursor. This feature is supported through
various client interfaces such as JDBC and CLI.
The scrollable cursor will handle the metadata of the numbering and
bookmarking internally. No need to update your fact able.

Cheers
Serge

Woops one proble though.
We're using thin clients connecting to loadbalanced application servers
which connect to the database. Because of the fact that the a call can
be directed to any application server, the calls are stateless. (each
call is a seperate connection with its own cursor)

So it has to be done in a higher level.

Is it possible to place a cursor on a given position? (guess not).
And lets say I cannot influence the cursor anymore, the only thing I can
do is write some fancy SQL and change some of the algorithms used by the
grid. In that situation, is there anything I can do to improve
performance over the "select first(100) from (select first(blocknumber *
100) * from very_large_table order by PK descending)".

-R-

OK if you order by PK this is easy:
SELECT pk, ... FTOM table WHERE pk > ?
ORDER BY pk FETCH FIRST 100 ROWS ONLY

Start of with ? being some less than minimum values (like -1, or '')
Then for subsequent calls just feed in the last pk fetched.
The primary key index will get you quickly to the start and
FETCH FIRST 100 ROWS will ensure you get "early out".

Things would be more messy with an arbitrary order...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab