DB2 檢視緩沖池真實大小

DB2中，修改緩沖池大小的指令有兩個選項，預設的是IMMEDIATE，也就是立刻生效。 另一種是DEFERRED， 它隻是在系統表中做了修改，實際上沒有生效，需要重新激活資料庫生效，也就是使用視圖SYSCAT.BUFFERPOOLS查到的，并不一定是真實大小，那麼如何檢視緩沖池真實大小呢？

If the statement is executed as deferred, the following is true: Although the buffer pool definition is transactional and the changes to the buffer pool definition will be reflected in the catalog tables on commit, no changes to the actual buffer pool will take effect until the next time the database is started. The current attributes of the buffer pool will exist until then, and there will not be any impact to the buffer pool in the interim. Tables created in table spaces of new database partition groups will use the default buffer pool. The statement is IMMEDIATE by default when that keyword applies.

>>-ALTER BUFFERPOOL--bufferpool-name---------------------------->

     .-IMMEDIATE-.                                                                                        

>--+-+-----------+--+-------------------------------------+--SIZE--+-number-of-pages----------------+-+-><

   | '-DEFERRED--'  '-DBPARTITIONNUM--db-partition-number-'        '-+-----------------+--AUTOMATIC-' |   

   |                                                                 '-number-of-pages-'              |   

   +-ADD DATABASE PARTITION GROUP--db-partition-group-name--------------------------------------------+   

   +-NUMBLOCKPAGES--number-of-pages--+----------------------------+-----------------------------------+   

   |                                 '-BLOCKSIZE--number-of-pages-'                                   |   

   '-BLOCKSIZE--number-of-pages-----------------------------------------------------------------------'   

答案是使用MON_GET_BUFFERPOOL表函數。

參考測試如下：

$ db2 "create bufferpool bffpl1 immediate size 1000 PAGESIZE 4k"    

DB20000I  The SQL command completed successfully.

$ db2 "select substr(BPNAME, 1, 30) as BPNAME, BUFFERPOOLID, NPAGES, PAGESIZE from SYSCAT.BUFFERPOOLS"

BPNAME                         BUFFERPOOLID NPAGES      PAGESIZE   

------------------------------ ------------ ----------- -----------

IBMDEFAULTBP                              1          -2        4096

BFFPL1                                    2        1000        4096

  2 record(s) selected.

1.）

$ db2 "alter bufferpool bffpl1 size 1500"

DB20000I  The SQL command completed successfully.

$ db2 "select substr(BPNAME, 1, 30) as BPNAME, BUFFERPOOLID, NPAGES, PAGESIZE from SYSCAT.BUFFERPOOLS"

BPNAME                         BUFFERPOOLID NPAGES      PAGESIZE   

------------------------------ ------------ ----------- -----------

IBMDEFAULTBP                              1          -2        4096

BFFPL1                                    2        1500        4096

  2 record(s) selected.

$ db2 "select substr(BP_NAME, 1, 30) as BP_NAME, BP_CUR_BUFFSZ from table(MON_GET_BUFFERPOOL('BFFPL1',-1))"

BP_NAME                        BP_CUR_BUFFSZ       

------------------------------ --------------------

BFFPL1                                         1500

  1 record(s) selected.

采用預設的方式，發現系統表裡發生了變化，實際大小也改變了。

2.）

$  db2 "alter bufferpool bffpl1 DEFERRED size 2000" 

DB20000I  The SQL command completed successfully.

$ db2 "select substr(BPNAME, 1, 30) as BPNAME, BUFFERPOOLID, NPAGES, PAGESIZE from SYSCAT.BUFFERPOOLS"

BPNAME                         BUFFERPOOLID NPAGES      PAGESIZE   

------------------------------ ------------ ----------- -----------

IBMDEFAULTBP                              1          -2        4096

BFFPL1                                    2        2000        4096

  2 record(s) selected.

$ db2 "select substr(BP_NAME, 1, 30) as BP_NAME, BP_CUR_BUFFSZ from table(MON_GET_BUFFERPOOL('BFFPL1',-1))"

BP_NAME                        BP_CUR_BUFFSZ       

------------------------------ --------------------

BFFPL1                                         1500

  1 record(s) selected.

采用了DEFERRED，雖然系統表發生了變化(1500->2000)，但緩沖池實際大小沒變(1500->1500)，必須重新激活資料庫才生效。

$ db2 terminate

DB20000I  The TERMINATE command completed successfully.

$ db2 connect to qsmiao

   Database Connection Information

 Database server        = DB2/AIX64 9.7.6

 SQL authorization ID   = E97Q6C

 Local database alias   = QSMIAO

$  db2 "select substr(BP_NAME, 1, 30) as BP_NAME, BP_CUR_BUFFSZ from table(MON_GET_BUFFERPOOL('BFFPL1',-1))"

BP_NAME                        BP_CUR_BUFFSZ       

------------------------------ --------------------

BFFPL1                                         2000

  1 record(s) selected.