DB2 V9.7 FP3 on linux - DPF environment

Can some one help me out with a query which gives bufferpool name, Npages and its pages size per each logical database partition ?

For example if there are 8 database partitions - I need to know what bufferpools are associated with each parition and what is the value of Npages and pagesize for each bufferpool

Any help on this regard will be greatly appreciated.

select * from syscat.bufferpools

Each logical DPF partition has bufferpools of the size indicated in the above query. This is becasue each logical DPF partition has its own db2sysc process running.

I don't think that does the job.

For example, if I have a 8 logical partitions and one partition group [PG1] is spanned across 4 logical partitions [5,6,7,8] and another partition group [PG2] is spanned across two logical partitions [1,2]. I have a bufferpool BP1 which is associated with partition group PG1.
If I run select * from syscat.bufferpools, it gives all the bufferpools this also includes BP1. But BP1 is not associated with partition 1. So I need a query which gives bufferpools for each partition.

If you decided to have different bufferpool sizes per partition (this is not necessary and not the default), then you can see that info here:

SYSCAT.BUFFERPOOLDBPARTITIONS

If there are rows in the above view, then bufferpools are associated with DBPARTITIONNUM. Even though DATABASE PARTITION GROUP can be specified when creating bufferpools, it apparently is just used as a shorthand to populate the bufferpool size for each DBPARTITIONNUM in the SYSCAT.BUFFERPOOLDBPARTITIONS catalog view.

The DB2 Catalog Tables are described in Appendix D. System catalog views, of the SQL Reference Vol 1.

Well I am not talking about different bufferpool sizes per partition. I am talking about bufferpools associated with database partitions.

For example, if we take first partition, I wanted the query which gives bufferpools associated only with partition 1.

Consider that there are 6 bufferpools for the entire database. Only four out of six bufferpools are associated to patition 1. I wanted that query which gives the same result.

All of my bufferpools are the same for all DPF partitions, so there are no entries in SYSCAT.BUFFERPOOLDBPARTITIONS for my DPF system. However, if bufferpools are specific to one or more partitions (and not the same for all partitions), then I assume they should show up in SYSCAT.BUFFERPOOLDBPARTITIONS. Did you try and query that catalog view?

Yes I quried that. Even though all my bufferpools are not for all the DPF partitions, that catalog view is still empty.

What proof do you have that your bufferpools are not for all partitions?

I guess you could try the db2look command and see what it generates for create/alter bufferpool.

you can try db2top and select the option b. It gives how many partitions a bufferpool is spanned across.

Even you can try db2look to see the partition group name specified while creating the bufferpool. If no partition group is specified, by default BP spans across all the partitions.

So what happened when you tried it?


Yes, that is what I posted above. What happened when you ran db2look? I didn't use partition group when specifying bufferpools, so I don't have any question. But I thought you have the question?.

I would like to know if you have some bufferpools that are only in some partitions (according to db2look), what shows up in SYSCAT.BUFFERPOOLDBPARTITIONS catalog view?

db2top only gives the total number of partitions which a bufferpool is allocated to.

When I ran db2look, it shows up that the bufferpools are assigned to specific partition groups. Even then when I try querying syscat.bufferpooldbpartitions, it is empty.

My specific question here is to find out how much bufferpool memory is allocated to each logical partition. So I need a query where in I input the dbpartition number and it gives me the bufferpool names associated with it and it Npages and pagesize.

db2pd -bufferpools or snapshot for bufferpools should have this info. If it has to be a query, try snap_get_bp_part table function.

So why is syscat.bufferpooldbpartitions empty if there are bufferpools assigned to specific partition groups?

I don't remember for sure, but it's sort of like an exception table (ie. when a bufferpool is created or altered (?) with except clause).

I tried this with 1 physical / 3 logical partitions.