Hi,
I was just looking through the DB2 pages sizes.

IBM Informix Dynamic Server v10 Information Center

This states:
"DB2 supports 4K, 8K, 16K, and 32K page sizes."

I would have thought this was pretty small.

Windows OS defaults page size to be equal to the amount of memory on your PC.

I am curious as to why the page size is small for databases. Or is it that the page size associate with and OS and a database are two entirely different concepts?

how can a page size be equal to memory size
you mean something else - swap file - paging size .. ????

Sorry, I am bit confused

Basically, I do not understand excatly what is meant by "page size" in DB2 lexion? And I do not understand why it is so small?

I did a wikipedia on page size Page (computer memory) - Wikipedia, the free encyclopedia and it gave the windows example.

Any advice appreciated.

page is an entity used to save data/indexes.. on
they are chained together in a table / index
all pages on any system are of these sizes
see also
pagesize - Database default page size

it might be a good idea to have a look at
FREE ebook - Database fundamentals - Wikis

That book is pretty basic. I'm not bad

What exactly is the difference between page size and buffer pools? And why is page size so small? The page size setting for db2 are far smaller than say what your memory (i.e. RAM) would be.

Cheers.

Ok. Some more googling...

Good simple definition here:
Database Page Layout

Essentially saying a table / index is stored as an index of pages.

Similar a good analogy here where one poster describes them as just been like pages of a book:
database - MySQL: What is a page? - Stack Overflow

This is a good analogy as pages are of fixed size. And you can imagine if a pagesize was too big, there'd be plenty of wasted space. If page size was too small, the book would be too difficult to read / search etc.

So my questions are:
1. What are page sizes generally so small? Why can't I have a page size of say 1Meg or 100 Meg?
2. What happens if you have a very large rows (not to hard to beat size of 32KB) ? I presume your row is stored in multiple pages.
3. Also, I see DB2 has a concept of leaf pages and sequential pages and just pages. I can make a guess at what these mean, but would really like to know exactly what they mean and have a deep understanding.

So determines if a page is leaf / sequential or not? How do I check?
Do pages have children and a leaf just has no children?

Cheers.

Here is some info on leaf / non - leaf pages for indexes: IBM Information Management Software for z/OS Solutions Information Center.

It explain the usual tree / leaf structure - widespread in Computer science. But
the question is why are leafs even needed?

page size is not something invented by db2 - it is common for all applications on each platform and yes 4K.. is a regular size as it always used to be
leaf pages are being used in index structures.. these are the pages at lowest level, where the index values are being saved
there is no difference between data/index pages
sequential pages : never seen this terminology : pages are chained in a table / index and there are special pages, keeping info about pages
long objects are saved in long tablespaces where many pages are chained to create a large page.
another page would be
IBM developerWorks : DB2 9 Fundamentals certification 730 prep series
or have a look at the dba certification handbook to understand db2..
or just attend some courses..

Yes, I get pagesize is not something invented by DB2. I've read through the DB2 fundamentals links. The stuff I am looking is a bit more advanced.

Cheers.

before going to advanced stuff it is better to understand the basics. at that time you might have a better look at the advanced chapters.

There's nothing in the DB2 fundamentals book about the level of information I am looking for regarding page sizes.

Cheers.

You are confusing Page File size in Windows with Page Size in a database. Page File is the Windows name for the swap file size of the entire operatiing system, not the page size used by an application running on that application.

MS SQL Server database (which only runs on Windows) has similar page sizes as DB2.

I get that.

What are page sizes generally so small? Why can't I have a page size of say 1Meg or 100 Meg?

You mean why doesn't the database allow that? Several reasons:

1. Most operating systems move data from disk to memory in 4K or 8K chunks at a time. This may be changing with newer operating systems that support Huge Pages, but that is the original reason for it.
2. It is unlikely to make your database run any faster, especially if you have enough memory such that data stays in memory, and is not constantly being moved from disk to memory.
3. When you want only one row returned via an SQL statement, and it happens to be on disk, you don't want to have to read from disk and move to memory a huge page size that could hold thousands or rows when you only need the one row.