Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: DB2 page sizes

  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: DB2 page sizes

    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?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how can a page size be equal to memory size
    you mean something else - swap file - paging size .. ????
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    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.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it might be a good idea to have a look at
    FREE ebook - Database fundamentals - Wikis
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jan 2006
    Posts
    119
    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.

  7. #7
    Join Date
    Jan 2006
    Posts
    119
    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.
    Last edited by Breako; 07-19-11 at 08:27.

  8. #8
    Join Date
    Jan 2006
    Posts
    119
    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?

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Jan 2006
    Posts
    119
    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.

  11. #11
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  12. #12
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by przytula_guy View Post
    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.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jan 2006
    Posts
    119
    I get that.

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

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Breako View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •