Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    30

    Red face Unanswered: Physical Storage of Data

    I am having very basic doubt about db2 physical storage,

    In planning pdf on page 109 or page 95 of the content.

    It is mentioned there is maximum of 255 rows per page.

    My Question is what does it mean ?

    Does it mean that I cant have more than 255 rows in a page, if it is right.

    How you can prove it.

    I am taking the example of smallint as it takes least bytes to store.
    ================================================== ====================
    create regular tablespace dms_4k1 pagesize 4k managed by database using (file 'e:\testdb\test.dbf4' 200) extentsize 4

    create table test1 (sno smallint) in dms_4k1

    smallint takes 2 Bytes of space + 1 Byte ( as it is nullable) + 10 Bytes (row overhead)
    = 13 Bytes.

    records per page =4028/13 = 309

    i.e it will grab 2 pages to store 309 records.

    which means 255 records will be placed in first page
    and remaining (309 -255) 54 records will be placed in second page.

    But when I am testing, It is grabbing second page on 181 record
    i.e till 180 page it is inserting in first page,
    and for 181th record it is grabbing second page.

    Please correct me where I am wrong.

    ================================================== =====================

    or can you site me an example where db2 store 255 records in one page and for 256th record it
    grabs second page.


    I appreciate your Quick response.

    Regards,
    Pushp

    NB I am working with db2 8 for W2K and AIX

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How do you know when the second page is "grabbed"? Need more specific information on the SQL you are using and the DDL of the table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    30
    create regular tablespace dms_4k1 pagesize 4k managed by database using (file 'e:\testdb\test.dbf4' 200) extentsize 4

    create table test1 (sno smallint) in dms_4k1

    insert into test1 values 1,2,3,4,5,6,7 ...

    open the second CLP session and run the command reorgchk for the table (test1)

    By seeing the output of the reorgchk you will be knowing how table data grabs the pages in the extents.


    Regards,
    Pushp

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How about if you post the output of the reorgchk? Please specifically tell us what in the output makes you think you there are less than 255 rows per page.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2003
    Posts
    343
    By the way, if you are inserting 309 integers in ascending order then at some point you are exceeding the number of bytes per row that you are taking into consideration. Unless you are only inserting single digits.

    You have excluded page overhead, extent overhead and Tablespace overhead as well as Free Space Control Records.

    There is a section on this in the Admin Guide : Performance Under chapter 2, Arcitecture and Process Review, Tablespaces.

    Like Marcus, I am a little curious about what in the reorgchk o/p is telling you how many rows there are on each page.

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Smile

    It is very common for a DBMS to have a physical upper limit on the number of rows that can be placed in a given storage-page. This number is usually "a convenient size for an internal bitmap," i.e. some power of two.

    But... you should regard these as a theoretical maximum only, not an accurate divisor for actual planning purposes. The simple reason is that "there is always a tradeoff of space vs. speed/efficiency." During normal database operation, storage blocks will be "split" as a new record is inserted, or will develop "cavities" as records are removed. The computer will never attempt to minimize storage-space because that's unproductive extra work.

    Therefore, I suggest that you should expect storage blocks to be "about 50% full." Your results may vary and I've seen estimates all over the map.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Aug 2003
    Location
    India
    Posts
    30
    Hi All,

    Thanks for your Inputs.

    The actual ddls and the o/p of the reorgchk I am pasting below.

    One more thing I have observed that, for initial 180 rows it takes 1 page, thereafter for every 251 records (rows) it takes 1 Page i.e
    No of rows -------- Pages
    180 -----------------> 1
    181 -----------------> 2
    432 -----------------> 3
    683 -----------------> 4
    ================================================== ====================================
    cchattoraj: I believe the overhead of extents are deducted from the Tablespaces and not from the usable pages.

    About page overhead yes I agree that there will be some page overhead for a pageheader, but the max no of records 255 exclueds the pages overhead.

    FSCR is again on 500th page which is very far and it takes whole page.

    CORRECT ME IF I AM WRONG.
    ================================================== =====================================

    connect to sample

    drop tablespace dms_4k1

    create regular tablespace dms_4k1 pagesize 4k managed by database using (file 'e:\testdb\test.dbf4' 200) extentsize 4

    create table test1 (sno smallint) in dms_4k1

    insert into test1 values 0,1,2,3,4,5,6,7,8,9

    export to test1.ixf of ixf select * from test1

    insert into test1 select * from test1

    insert into test1 select * from test1

    insert into test1 select * from test1

    insert into test1 select * from test1

    import from test1.ixf of ixf insert into test1

    import from test1.ixf of ixf insert into test1
    ================================================== =========
    select count(*) from test1 ===> 180 Records.



    ================================================== =========
    OPEN ANOTHER CLP SESSION.

    connect to sample

    reorgchk on table pushp.test1

    (Observe the NP)

    Doing RUNSTATS ....


    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    PUSHPEND> TEST1 180 0 1 1 - 2340 0 - 100 ---
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
    F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

    ================================================== ====================================
    Now Inserting one record
    insert into test1 values (1)

    Now again run the reorgchk from second clp session

    db2 => reorgchk on table pushp.test1

    Doing RUNSTATS ....


    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    PUSHPEND> TEST1 181 0 2 2 - 2353 0 57 100 -*-
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
    F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    Here's an illustration of what I mean... "Those records you're inserting; how are they sorted?" If the records are in sequential-order by the primary key, they are likely to create a rather unrealistically-compact arrangement in storage, because the DBMS never had to split any blocks in that index-tree.

    Try randomizing the order of the records you're importing and observe how the results you obtained have now changed.

    For example: if the keys were 1 2 3 4 5 6 7 then the computer's just gonna pour 'em right in. But if the keys were 1 2 4 5 6 3 7 then (yes, this is a simplistic example) the computer happily pours-in 1 2 4 5 ... oops, here comes a 3, gotta split a block to make room, and so-on.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I observed the same results with a test I conducted on DB2 for Linux V8 fixpak 4.

    The test that was used does not involve any indexes on the table. The same results are obtained with or without "APPEND ON" and obviously there is no clustering since there is no index. For my tests I also threw in some reorgs just to make sure, but results were the same. I tried both SMS and DMS tablespaces with the same result.

    There are two issues:

    1). The first page, which only holds 180 rows before the number of pages used goes from 1 to 2.

    2). Subsequent pages, which after inserting 251 rows (not 255) will increase the number of pages from 2 to 3 (and all subsequent pages).

    Regarding issue 1: It is possible that DB2 stores some space map information in the first page. I am not sure about that.

    Regarding issue 2: It is also possible that DB2 formats a new page (and includes it in the count of pages used) before the previous page is completely full. It is possible that DB2 does this sooner when going from page 1 to 2, than it does for subsequent pages.

    In either case, issue number 1 is not significant. Issue number 2 does suggest that only 251 rows per page can be used for a 4K page, but I did not try it for larger page sizes (8K, 16K, 32K), which may allow for up to 255 rows in some circumstances. I would suggest that the same test be tried with 32K pages.

    In the early days of DB2 for MVS, the maximum was 127 rows per page before it was increased in more recent releases. I don’t recall if DB2 for Unix/Windows/OS2 had a similar restriction in its early releases.

    As suggested, DB2 does use a bit bitmap to identify the start of each row on the page. On DB2 for MVS, that bitmap is at the bottom of the page (last time I checked).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Mar 2003
    Posts
    343
    This might help a little though there obviously seems to be something on the first page of the table that does not exist on the other pages

    "Each data page has the following format: A page header begins each data page. After the page header there is a slot directory. Each entry in the slot directory corresponds to a different record on the page. The entry itself is the byte-offset into the data page where the record begins. Entries of minus one (-1) correspond to deleted records."

    Also I do not think that Free Space Control Records take a whole page after every 500 pages - I think there are records on every 500th page. So if slot directory ( of I which I could find no further description) and FSCR are taken into account aside from the page overhead, that might explain the first page (considering that FSCR's are forward looking). However, It does not explain the others. Maybe there is a minimum space that is alloted to a row. Maybe for a slightly larger rowsize, the counts will remain the same.

    Hope this helps.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The slot directory is the bit-map that I and others were refering to.

    I still wonder if the 255 rows per page maximum varies a little depending on page size.
    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
  •