Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23

    Unanswered: Partition group removal from a bufferpool?

    Version DB2 LUW V8.1
    Partitions: 3 default ibmcatgroup, ibmdefaultgroup, and ibmtempgroup. No other nodes.

    I know a partition group can be added to a bufferpool, but can it be removed?

    In an effort to isolate the syscatspace in its own bp, I created a new bp for it (assigning it to the ibmcatgroup only), then altered the tbspace to use this new bp. I also thought it would be a good idea to alter the ibmdefaultbp by adding partitions ibmdefaultgroup and ibmtempgroup - leaving out the ibmcatgroup. I have since changed my mind and want to put ibmdefaultbp back to the "all dbpartitions" state if possible.

    Since the alter bufferpool statement does not have a "drop partition" clause, I figured I had to build a new bp, reassign the tablespaces, then drop the old bp. This worked fine for the catalog bp I created, but ibmdefaultbp cannot be dropped, being a system object.

    Looking at the sysibm.sysbufferpool table I see that ibmdefaultbp has 3 records. The first has an NGNAME of NULL (this is the original record) + 2 more records with NGNAMEs of the partition groups added. I want to remove the 2 partition records, but this is a system table that can't be edited.

    So, does anyone out there know how to remove these extra nodes (partitions) from the ibmdefaultbp? I have a feeling I am out of luck. I know that I can rebuild the database from scratch as a last resort, but I want to avoid that option if possible.

    Thanks much,

    Steve - DBA

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure about the exact answer to your question, but if you want to move the System Catalog to another bufferpool, you only need to assign the syscat tablespace (SYSCATSPACE is the default) to the bufferpool you want. The Partition Group does not need to be changed unless you are using a partitioned database.
    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
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Yes, I realized I'd made a mistake almost immediately.

    I find it weird though that IBM would allow one to assign nodes (partitions), yet not allow the removal of these nodes later on. Why can we add nodes, but not remove them?

    What I should have done is leave the ibmdefaultbp alone, but I got carried away. I guess I'm in a pickle and have to drop and recreate the database. MS SQL Server is looking bettter every day

    If anyone has a resolution other than a db drop and recreate, please let me know.

    Thanks,

    -- Steve

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    Partitions can be removed from dbpartition groups.

    Ensure that there's no data on the partition which you are removing. Always preceded this activity with a full offline backup.

    Capture all config and have new files ready for db2nodes.cfg and .rhosts with the partitions removed.

    alter database partitiongroup <name1> drop dbpartitonnums (n1, n2,..)

    redistribute database partition group <name1> uniform

    drop dbpartitionnum verify

    db2stop drop nodenum n1

    This gives a message asking whether you wish to continue. If you hit yes, it actually stops, then starts then stops the instance on it's own. During the start, it says node "n1" is not being used by any database.

    when I did this I did it one at a time since I was doing this in prod. Your situation may vary.

    HTH

  5. #5
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Thanks HTH for the tips, but dropping partitions is not my issue. I'm trying to remove the NDNODE references to the 3 default partition groups assigned to the ibmdefaultbp listed in the sysibm.sysbufferpool table.

    I mistakenly assigned the 3 default partition groups (ibmcatgroup, ibmdefaultgroup, and ibmtempgroup) to the ibmdefaultbp explictly instead of the "normal" way of implicitly assigning them with the "all partitions" command. Normally when you query the sysibm.sysbufferpool table for the ibmdefaultbp, you would see one row with page information, along with a column NDNODE with a value = NULL. My issue is I have not only this record for ibmdefaultbp, but also one for each of the default partition groups.

    These extra assignments in the sysbufferpool table are not causing functional trouble, but I would rather not have them since it is not "clean."

    I've developed a repair solution, but it is cumbersome and time comsuming. I have built a completely empty database with the correct bufferpool structure. I can export all data from the "bad" database, load this data into the empty db, run a backup, then perform a redirected restore over the bad database. It's all tested and ready to go, but will take about 4 hours to complete. It would have been nice for IBM to provide a "DROP" partition group from the bufferpool along with the "ADD".

    SO, beware everyone. Do not add partition groups to the ibmdefault bufferpool. It is very difficult to remove them.

    -- Steve

Posting Permissions

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