Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    20

    Question Unanswered: Bufferpool configuration change to use STMM on one partition

    Hello,

    I recently ran into situation, when I had to change configuration for a partitioned database which included an alteration of all bufferpools.
    There are 4 partitions in the database. (DB2 ESE 9.7.0.7 on Linux 64bit).
    Old configuration was:
    Bufferpools on main partition (0) had their sizes set to a fixed value.
    Bufferpools on all other partitions(1,2,3) had their sizes set to automatic.

    New configuration is:
    Bufferpools on main partition (0) are set to automatic.
    Bufferpools on other partitions are set to fixed values.

    The sequence of change was:
    alter all bufferpools to set them to automatic;
    alter all bufferpools for dbpartitionnum 1,2,3 to set their size to a fixed value.

    This sequence created exception entries in syscat.bufferpooldbpartitions table for bufferpools on partitions 1,2,3. However, there were 'old' entries for partition 0, which were not removed (I assumed they would get removed after altering the bufferpools to set their size to automatic). I have deactivated/db2stop/db2start/activated the database - nothing changed..

    How can I remove the entries from this bufferpool exception table for partition 0? Dropping and recreating bufferpools is an option, but that does not apply to IBMDEFAULTBP bufferpool..

    Thanks and Regards,

    Tomas

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Did you try this sequence?

    Code:
    An ALTER BUFFERPOOL statement that specifies the size of a buffer pool on a particular database partition will create an exception entry (or update an existing entry) for that buffer pool in the SYSCAT.BUFFERPOOLDBPARTITIONS catalog view. If an exception entry for a buffer pool exists, that buffer pool will not participate in self-tuning operations when the default buffer pool size is set to AUTOMATIC. To remove an exception entry so that a buffer pool can be enabled for self tuning:
    Disable self tuning for this buffer pool by issuing an ALTER BUFFERPOOL statement, setting the buffer pool size to a specific value.
    Issue another ALTER BUFFERPOOL statement to set the size of the buffer pool on this database partition to the default.
    Enable self tuning for this buffer pool by issuing another ALTER BUFFERPOOL statement, setting the buffer pool size to AUTOMATIC.
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  3. #3
    Join Date
    Jan 2013
    Posts
    20
    Thanks for replying!

    To be honest I didn't find this when I was searching for a solution.
    However, it did not work. I tried many different sequences, including:
    1. Alter bufferpool IBMDEFAULTBP size 60
    2. Alter bufferpool IBMDEFAULTBP size -1
    3. Alter bufferpool IBMDEFAULTBP size automatic

    and

    1. Alter bufferpool IBMDEFAULTBP size 60
    deactivate/activate db
    2. Alter bufferpool IBMDEFAULTBP size -1
    deactivate/activate db
    3. Alter bufferpool IBMDEFAULTBP size automatic
    deactivate/activate db

    I have checked the SYSCAT.BUFFERPOOLDBPARTITIONS view after every sequence - exceptions were still there..

    I hoped that IBM documentation will surely solve this issue, but.. Could it be that I am misinterpreting something here?

    Regards,

    Tomas

  4. #4
    Join Date
    Jan 2013
    Posts
    20
    update:

    I have run the following sequence:

    1. Alter bufferpool IBMDEFAULTBP size 60
    2. Alter bufferpool IBMDEFAULTBP DBPARTITIONNUM 0 size -1
    Alter bufferpool IBMDEFAULTBP DBPARTITIONNUM 1 size -1
    Alter bufferpool IBMDEFAULTBP DBPARTITIONNUM 2 size -1
    Alter bufferpool IBMDEFAULTBP DBPARTITIONNUM 3 size -1
    3. Alter bufferpool IBMDEFAULTBP size automatic

    After this:
    select bpname,npages from syscat.bufferpools

    BPNAME NPAGES
    IBMDEFAULTBP -2

    select * from SYSCAT.BUFFERPOOLDBPARTITIONS order by 1

    BUFFERPOOLID DBPARTITIONNUM NPAGES
    1 0 -1
    1 1 -1
    1 2 -1
    1 3 -1

    And the size of this bufferpool is still the default one (1000 pages).

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Attaching results of the test someone else had done. May be you will find some of the info helpful.
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2013
    Posts
    20

    Smile

    worked like a charm

    Many thanks, db2girl!

    Kind Regards,

    Tomas

Tags for this Thread

Posting Permissions

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