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

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.

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

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

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