Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: bufferpool allocation query

    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.
    Last edited by blazer789; 11-10-11 at 09:04.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    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
    Jun 2009
    Posts
    272
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 11-10-11 at 09:43.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by blazer789 View Post
    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?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2009
    Posts
    272
    Yes I quried that. Even though all my bufferpools are not for all the DPF partitions, that catalog view is still empty.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by blazer789 View Post
    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.
    Last edited by Marcus_A; 11-10-11 at 13:17.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2009
    Posts
    272
    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.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by blazer789 View Post
    you can try db2top and select the option b. It gives how many partitions a bufferpool is spanned across.
    So what happened when you tried it?


    Quote Originally Posted by blazer789 View Post
    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.
    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?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jun 2009
    Posts
    272
    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.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2pd -bufferpools or snapshot for bufferpools should have this info. If it has to be a query, try snap_get_bp_part table function.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    So why is syscat.bufferpooldbpartitions empty if there are bufferpools assigned to specific partition groups?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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).

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2girl View Post
    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.
    Attached Files Attached Files

Posting Permissions

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