Dropping buffer pools

When you no longer need a buffer pool, you can drop it. Another alternative is to set it to automatic tuning, so it will not take up unneeded space.

Getting ready

Ensure no table spaces have been assigned to this buffer pool.

How to do it...

1. Create a fictitious buffer pool to be dropped later.

   In this example, this buffer pool should have been created in another database:

   [db2inst1@nodedb21 nav]$ db2 "create bufferpool pos_bpt32k pagesize 32 K"
   DB20000I  The SQL command completed successfully.

2. View the table space association list.

3. If the mistake has not been detected early enough, we may want to check if a table space has been associated with it:

   [db2inst1@nodedb21 nav]$ db2 "select substr(ts.tbspace,1,20),substr(bp.bpname,1,20)
     from sysibm.systable spaces ts right outer join
     sysibm.sysbufferpools bp
     on bp.bufferpoolid = ts.bufferpoolid"
   1                    2
   -------------------- --------------------
   SYSCATSPACE          IBMDEFAULTBP
   TEMPSPACE1           IBMDEFAULTBP...