Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: DB2 for Linux/Unix/Windows - Bufferpool question

    Hi,

    My apologies if this has already been addressed in another thread - I first performed a search and did not find anything. My question is: is DB2 for Linux/Unix/Windows such that "there is no partitioning avaiable in DB2 LUW and therefore all processing must go through the same bufferpool. As a result of this, scalability is impacted since the application is throttled by a single bufferpool." Does this sound correct or did I misinterpret something (this is what I was told - I did not read it in a manual)? - I'm guessing it's incorrect myself and I am therefore looking for clarification.

    Thanks!
    Last edited by Grumpy; 06-30-04 at 17:19.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Partitioning is available in DB2 (But, you may have to explain what you mean by the term ) .. Search for DPF/EEE ...

    In 32 bit architecture, each partiontion is constrained by a 2Gig (normally 1.5 - 1.75 gig) limit for bufferpools ... This can be multiple bufferpools also, but the total is 2 Gigs ... You can, of course , use the extended pool ....
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The statement, "there is no partitioning avaiable in DB2 LUW and therefore all processing must go through the same bufferpool. As a result of this, scalability is impacted since the application is throttled by a single bufferpool." contains two unrelated claims that are not true anyway.

    1) There is partitioning in DB2 for LUW (EEE edition is required).
    2) You can have multiple bufferpools.
    3) There's no relation between partitions and bufferpools (except that each partition has its own bufferpools).

    The person who made that statement may benefit from learning more about DB2.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To expand on what was said before:

    1. DB2 with partitioning in version 7 is called DB2 EEE. In version 8 it is called DB2 ESE with DPF (data partitioning feature).

    2. You can have multiple bufferpools in DB2, however the ability to have many bufferpools is not directly related to scalability. Most people create too many bufferpools, when a fewer number of larger bufferpools is often better. When using multiple partitions, each partition has its own bufferpools.

    The ability do have a scalable architecture depends on inter-partition parallelism (DB2 ESE with DPF) and/or intra-partition parallelism (any version of DB2), when the database is set up properly to exploit parallel operations.
    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
    Jan 2004
    Posts
    9
    Thanks everyone for your prompt replies. Tell me, how is the data order in DB2 LUX - is it the same as it is in MVS - namely, by the clustering INDEX?

    Thanks.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is a little different.

    Insert Rule
    If you define a clustering index, then it works pretty much the same way as DB2 for z/OS (previously known as OS/390). If you don't define a clustering index in LUW, new rows are inserted at the end of the table (in DB2 for z/OS, the first index is the clustering index by default if no clustering index is defined). In either version, rows are inserted at the end if the APPEND option is used on the table (create or alter).

    Reorgs
    For reorgs on DB2 for z/OS, the clustering index (or first index if no clustering index is defined) is always used to order the rows. For DB2 LUW, you can specify an index to be used to order the rows during the reorg if no clustering index has been defined. If a clustering index has been defined you can ignore the index specification in the reorg command, but if you do include the index name it must be the clustering index.

    The main reason for the differences between DB2 z/OS and DB2 LUW is that the original version of LUW did not have clustering indexes, although one could always reorg by a particular index.
    Last edited by Marcus_A; 06-30-04 at 19:58.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Reorgs: I use db2 v7.2 and if cluster index is specified on table and also a regular index. Table can be reorged by bouth indexes!!! I created test table and inserted rows into it and then reorg table and then "select * from table". Table is sorted according of index specified in reog statement.

    So if table has cluster index it can be also sorted according to regular index.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Grofaty, thank you for the correction. Actually, even in version 8, what I said only applies to an INPLACE reorg. From the Command Reference:

    "For an inplace table reorg, if a clustering index is defined on the table and an index is specified, it must be clustering index. If the inplace option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster 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

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    What is INPLACE reorg? Is its marketing name: "On-line reorg"?

    Grofaty

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    According to the manual, INPLACE means reorganizing the table while permitting user access. The INPLACE keyword is the actual syntax used in version 8. I would not blame the marketing people for that one.

    I think INPLACE is actually more descriptive becasue an INPLACE reorg is different from an ONLINE reorg on DB2 for z/OS where the table is written to another dataset in the order of the clustering index, and then the old dataset is renamed, and the DB2 logs are applied for any changes made during the reanme process.

    I believe that for an INPLACE reorg, the rows are just moved around within the same file.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Mar 2004
    Posts
    448
    I am little confused with the grofaty reply,correct me ,a table cann't be reorg
    based on two seperate indexes( there should be only one clustered index on a table). so when we talk about reorg of table in the context of index , we
    generally mean the physcial reordering of the table based on that index sorting order.

    This is what I have from IBM manual
    Part 1 :
    The table option reorganizes a table by reconstructing the rows to eliminate
    fragmented data, and by compacting information.


    Part 2:
    INDEX index-name
    Specifies the index to use when reorganizing the table. If you
    do not specify the fully qualified name in the form:
    schema.index-name, the default schema is assumed. The schema
    is the user name under which the index was created. The
    database manager uses the index to physically reorder the
    records in the table it is reorganizing.
    For an inplace table reorg, if a clustering index is defined on
    the table and an index is specified, it must be clustering index.
    If the inplace option is not specified, any index specified will
    be used. If you do not specify the name of an index, the
    records are reorganized without regard to order. If the table
    has a clustering index defined, however, and no index is
    specified, then the clustering index is used to cluster the table.
    You cannot specify an index if you are reorganizing an MDC
    table.


    from these parts you will find that a table can be reorg without any indexes.
    Indexes can be reorg seperatly.


    regards,

    mujeeb

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think he meant that he reorged once with one index, and then later with another index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    That is correct.

    I just meant: table can be reorged according cluster index at one time and can be also reorged (when finised first reorg) with regular index.

    There is no limitation that table containing cluster index can't be reorged according to regular index, when bouth cluster and regular index are defined on table.

    Hope this helps,
    Grofaty

  14. #14
    Join Date
    Mar 2004
    Posts
    448
    just going through this discussion.

    if you reorg your table first on cluster index and then on another index, the first reorg is lost or become useless.

    After your second reorg the new data that will be inserted into the table will follow the cluster index ordering sequence, so now you have two different ordering divided at the reorg path.

    regards,

    mujeeb

  15. #15
    Join Date
    Jan 2003
    Posts
    1,605
    Hi bmujeeb,

    You missunderstood me.
    Reorg one and the same table according to two indexes is useless. The first ordering will be lost. The last ordering is applied.

    My reply to Marcus_A was: If regular index and cluster index are defined on table, then reorg can be done with regular OR!!!!!! cluster index. You have the choice!!! You can reorg table according to regular or cluster index. Marcus said: "... if you do include the index name it must be the clustering index." This statement is false!!! This is only true for inplace reorg. So: if regular and cluster index are defined on table, then reorg can be done with regular or cluster index.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 07-12-04 at 08:27.

Posting Permissions

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