Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Smile Unanswered: Database Design, table spaces ...buffer pools

    hi All,

    Please help me to design my db for the following

    I have around 50 tables, I divided them as follows

    Frequently accessed
    high: 20
    medium: 20
    low: 10

    Row length
    under 4k : 42
    up to and under 16k: 8

    No lob data

    All 8 tables with row length (up to and under 16k) are in medium(Frequently accessed) category

    --

    I have option of RAID 5 with 8 GB RAM. I will have my app server and db server mainly running on same RAID5, RHL 5 -64

    As far as my understanding/reading goes abt db2, i have the following suggestions

    Scenario1

    db default page size 4k

    buffer pools
    bp_table1 - 4k - size 1000 - (=IBMDEFAULTBP)
    bp_index1 - 4k - size 1000
    bp_table2 - 16k - size 1000
    bp_index2 - 16k - size 1000

    table spaces
    ts_sys_table_4k - bp_table1 - (=TEMPSPACE1)
    ts_sys_table_16k - bp_table2 -
    ts_reg_table_4k - bp_table1 - (=SYSCATSPACE)
    ts_reg_table_16k - bp_table2
    ts_reg_index_4k - bp_index1
    ts_reg_index_16k - bp_index2

    ---------------

    Scenario2

    db default page size 8k

    buffer pools
    bp_table1 - 8k - size 276285 - (=IBMDEFAULTBP)
    bp_index1 - 8k - size 1000
    bp_table2 - 16k - size 1000
    bp_index2 - 16k - size 1000

    table spaces
    ts_sys_table_8k - bp_table1 - (=TEMPSPACE1)
    ts_sys_table_16k - bp_table2 -
    ts_reg_table_8k - bp_table1 - (=SYSCATSPACE)
    ts_reg_table_16k - bp_table2
    ts_reg_index_8k - bp_index1
    ts_reg_index_16k - bp_index2

    IN this scenario 2, I am having default page size 8k, which will be used for tables
    having row length < 4k, will this be usefull?

    -------

    Now considering the frequently accessed tables
    high = 20 - tables with only 4k
    medium + low = 30 - tables with 4k and 16k

    and "All 8 tables with row length (up to and under 16k) are in medium(Frequently accessed) category"


    Scenario3

    db default page size 4k

    buffer pools
    bp_table1 - 4k - size 1000 - (=IBMDEFAULTBP)
    bp_index1 - 4k - size 1000
    bp_table2 - 16k - size 1000
    bp_index2 - 16k - size 1000

    table spaces
    ts_sys_table_4k - bp_table1 - (=TEMPSPACE1)
    ts_sys_table_16k - bp_table2 -
    ts_reg_table_high_4k - bp_table1 - (=SYSCATSPACE)
    ts_reg_table_medlow_4k - bp_table1 -
    ts_reg_table_medlow_16k - bp_table2
    ts_reg_index_high_4k - bp_index1
    ts_reg_index_medlow_4k - bp_index1
    ts_reg_index_medlow_16k - bp_index2

    ---------------


    Your suggestions are most welcome

    thanks
    Amol

  2. #2
    Join Date
    Mar 2009
    Posts
    6

    Smile

    Again, is this true for db295 that, a data page cannot hold more than 255 records?

Posting Permissions

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