Professional Web Applications Themes

Design ideas for DB2 Parallel Server - IBM DB2

Dear Group. I am trying to implement DB2 parallel server on two nodes (may expand later). I have a few questions on the design phase. Our application is similar to an ASP model. We have one small table which has one entry for each of our clients. table: MTAB ( client# int primary key, name contact .) -- main entry point for every clients We have few large tables and many medium to small tables. 95% of the tables will have client# as the part of the primary key. (the tables are say, CTAB1 CTAB2 etc). PKeys are composite of ...

  1. #1

    Default Design ideas for DB2 Parallel Server

    Dear Group.

    I am trying to implement DB2 parallel server on two nodes (may expand
    later). I have a few questions on the design phase.

    Our application is similar to an ASP model.

    We have one small table which has one entry for each of our clients.

    table: MTAB ( client# int primary key,
    name
    contact .) -- main entry point for every clients

    We have few large tables and many medium to small tables. 95% of the
    tables will have client# as the part of the primary key. (the tables
    are say, CTAB1 CTAB2 etc). PKeys are composite of the form client#,
    record#

    We have some tables which are common to all our clients. Such as our
    broadcast messages etc. (say MTAB1, MAB2)

    The goal is, I want the data from the same client to be on the same
    node. So, even if one code is inaccessible, atleast others can be
    online.

    I have defined a partition group to have all the nodes and will have
    some tablespaces.

    Now,

    Is it better to define a single node partition group and put all the
    non-client related data on this node? If so. which node would I choose
    to put these tables?

    Also, even if the tables are tiny, should I spread them across all the
    nodes, if they are client related? (ie, client# is part of the key)

    Where do I place the table MTAB1? Will it be on a single node or on
    the partitioned one? This will be accessed by all clients. And, this
    table is the entry point to any client access?

    To access the data, whether DB2 has to go to the primary node every
    time it access?

    How do I keep the data on the other nodes as well, if they don't have
    the partitioning key (ie, no client#). I want them to be ted to
    all nodes. How would I achieve this? What kind of data group,
    tablespace is needed here?

    Any ideas? Any pitfalls?

    Yes, I have been reading the planning/implementaion manuals!

    Thanks,
    Prince Guest

  2. #2

    Default Re: Design ideas for DB2 Parallel Server

    3 thoughts:

    1. Limit yourself to two partitions groups - 1 that spans all partitions
    (for your large tables) and 1 that restricts itself to one partition
    (for small tables). Implementations I have heard of with a more
    complicated partition group set up have added complexity without a
    commensurate gain in performance

    2. consider ting small tables using the Materialized Query Table
    (also known as ted summary tables) support, to give maximum
    chances for collocated joins

    3. There's an article/router for Websphere with a DB2 partitioned
    database here: http://www.alphaworks.ibm.com/tech/db2connectionrouter
    The idea is to have a client connect to the partition where the desired
    data is likely to be.

    Prince Kumar wrote:
     

    s (may expand 

    .. So, even if one code is inaccessible, atleast others can be 

    Blair Guest

  3. #3

    Default Re: Design ideas for DB2 Parallel Server

    FWIW - if you have all logical partitions, it is generally considered good practice to separate your catalog partition from all database partitions ... generally from a backup and recoverability point of view.

    --

    Bob
    IBM Toronto Lab
    IBM Software Services for Data Management
    "Blair Adamache" <> wrote in message news:bm4pgs$c4h$torolab.ibm.com...
    3 thoughts:

    1. Limit yourself to two partitions groups - 1 that spans all partitions
    (for your large tables) and 1 that restricts itself to one partition
    (for small tables). Implementations I have heard of with a more
    complicated partition group set up have added complexity without a
    commensurate gain in performance

    2. consider ting small tables using the Materialized Query Table
    (also known as ted summary tables) support, to give maximum
    chances for collocated joins

    3. There's an article/router for Websphere with a DB2 partitioned
    database here: http://www.alphaworks.ibm.com/tech/db2connectionrouter
    The idea is to have a client connect to the partition where the desired
    data is likely to be.

    Prince Kumar wrote:
     

    s (may expand 

    . So, even if one code is inaccessible, atleast others can be 

    Bob Guest

  4. #4

    Default Re: Design ideas for DB2 Parallel Server

    Thank you very much Blair and Bob, for your inputs.

    The information is very helpful. I have couple of followup questions.

    Blair,
    You recommened to te/materialize the small tables on every
    node. Logically the table (say Table1) resides on the SAME database
    (though it has many nodes). Now, how do I te/materialize it?
    What name the materilaized tables will be? Any pointers, showing how
    it is done?

    Also, some of my small tables may be client related with very few rows
    per client. So, if there are 3000 clients, I may have a total of 3000
    to 15000 rows. Is it better to be put these tables on the multi
    partition data group or just put them on a single partition group and
    te them.

    Again, coud you please explain how this materialization is done in
    this particular case? Doents or URLS would be fine as well.

    Connection Router, is the one I have been looking for days. Thanks for
    the URL. I will read the docs.

    Bob,
    Does that mean, create a partition group and put the SYSCATSPACE under
    the group? Do I have to put anyother tablespace in that group?

    To all,
    Is there any plan for DB2 to have Range Partition on open systems?
    Initially we were thinking that we would have say 1000 clients on each
    partition. when the limit is reached we would add another node and so
    on. Though the distribution is not that good as "hash", I have few
    advantages in this model. I don't have to redistribute when I add
    another node (even if there is, it will be a minor one). I control,
    how many clients on any node etc.

    Regards,
    Prince.

    "Bob [IBM]" <com> wrote in message news:<bm52ca$iik$btv.ibm.com>... 
    >
    > s (may expand 
    >
    > . So, even if one code is inaccessible, atleast others can be 
    > choose 
    > the 
    > have 
    > to 
    >
    > --[/ref]
    Prince Guest

  5. #5

    Default Re: Design ideas for DB2 Parallel Server

    answers inline

    Prince Kumar wrote:
     

    You create a materialized table over an existing table. Use the keyword
    TED and the option for creating a table with a subquery under
    CREATE TABKE in the SQL Reference:
    ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s2e80.pdf
     
    Sounds like they should only be ted if the data in them is almost
    always read-only.
     
    >>
    >> s (may expand 
    >>
    >> . So, even if one code is inaccessible, atleast others can be 
    >> choose 
    >> the 
    >> have 
    >> to 
    >>
    >>--[/ref][/ref]

    Blair Guest

  6. #6

    Default Re: Design ideas for DB2 Parallel Server

    Thanks Blair.

    I read the redbooks on MQT and is very clear now.

    Blair Adamache <> wrote in message news:<bm6uji$9e3$torolab.ibm.com>... 
    >
    > You create a materialized table over an existing table. Use the keyword
    > TED and the option for creating a table with a subquery under
    > CREATE TABKE in the SQL Reference:
    > ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s2e80.pdf

    > Sounds like they should only be ted if the data in them is almost
    > always read-only.
    > [/ref]
    > choose [/ref]
    > the [/ref]
    > have [/ref]
    > to [/ref][/ref]
    Prince Guest

  7. #7

    Default Re: Design ideas for DB2 Parallel Server

    Prince ... what I meant was ensure that no data exists on the partition (logical or physical depending on your setup) other than the catalog data. So you would by default have a catalog partition as the partition on which you issue the CREATE DATABASE command. Other data would go into table spaces defined in other partition groups. You had the ability to define which partitions those partitions groups include... my thought was to not have the catalog partition included in those partition groups.

    --

    Bob
    Consulting I/T Specialist
    IBM Toronto Lab
    IBM Software Services for Data Management
    "Prince Kumar" <com> wrote in message news:google.com...
    Thank you very much Blair and Bob, for your inputs.

    The information is very helpful. I have couple of followup questions.

    Blair,
    You recommened to te/materialize the small tables on every
    node. Logically the table (say Table1) resides on the SAME database
    (though it has many nodes). Now, how do I te/materialize it?
    What name the materilaized tables will be? Any pointers, showing how
    it is done?

    Also, some of my small tables may be client related with very few rows
    per client. So, if there are 3000 clients, I may have a total of 3000
    to 15000 rows. Is it better to be put these tables on the multi
    partition data group or just put them on a single partition group and
    te them.

    Again, coud you please explain how this materialization is done in
    this particular case? Doents or URLS would be fine as well.

    Connection Router, is the one I have been looking for days. Thanks for
    the URL. I will read the docs.

    Bob,
    Does that mean, create a partition group and put the SYSCATSPACE under
    the group? Do I have to put anyother tablespace in that group?

    To all,
    Is there any plan for DB2 to have Range Partition on open systems?
    Initially we were thinking that we would have say 1000 clients on each
    partition. when the limit is reached we would add another node and so
    on. Though the distribution is not that good as "hash", I have few
    advantages in this model. I don't have to redistribute when I add
    another node (even if there is, it will be a minor one). I control,
    how many clients on any node etc.

    Regards,
    Prince.

    "Bob [IBM]" <com> wrote in message news:<bm52ca$iik$btv.ibm.com>... 
    >
    > s (may expand 
    >
    > . So, even if one code is inaccessible, atleast others can be 
    > choose 
    > the 
    > have 
    > to 
    >
    > --[/ref]
    Bob Guest

Similar Threads

  1. run 2 CF Server parallel
    By mike2004 in forum Coldfusion Server Administration
    Replies: 1
    Last Post: July 19th, 05:14 PM
  2. Web design, server set-up..help!!!
    By therobisino webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: October 24th, 11:52 AM
  3. template design ideas incorporating dynamic text fields
    By MB in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: September 16th, 10:55 PM
  4. Editing Server Side Includes in Design View
    By BurnsyNWMS webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 25th, 11:53 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139