db2缓冲池维护.docVIP

DB2性能监控和调优Bufferpool篇 1，打开监视器记录开关db2 update monitor switches using BUFFERPOOL ON 2,查看BUFFERPOOL相关指标(1- ((Buffer pool data physical reads + Buffer pool index physical reads) /(Buffer pool data logical reads + Buffer pool index logical reads))) * 100% 3，查看BUFFERPOOL大小db2 connect to db2 select * from SYSCAT.BUFFERPOOLS （select BPNAME,NPAGES,PAGESIZE from SYSCAT.BUFFERPOOLS）BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOC KSIZE NGNAME IBMDEFAULTBP 1 - 1000 4096 N 0 0 - BUF32K 2 - 1000 32768 N 0 0 - 1 条记录已选择。 看到，250 pages,每页4096 bytes(4k) 4，运行performer,记录Test Result 5,打开Windows任务管理器，注意当前内存使用情况 6，加大IBMDEFAULTBPdb2 alter bufferpool IBMDEFAULTBP immediate size 10000 7,注意Windows任务管理器显示的当前内存使用情况 8，运行performer,记录Test Result，与调整前比较。 要找出哪个缓冲池被分配给了表空间，请运行下面这个查询：SELECT TBSPACE, BUFFERPOOLID FROM SYSCAT.TABLESPACES 2、创建pagesize为32kb的bufferpool create bufferpool bp32k size 2000 pagesize 32k 3、更改缓冲池大小 alter bufferpool bp32k size 2000 db2 = alter bufferpool bp32k size 2000 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N BP32K is an undefined name. SQLSTATE=42704 4、创建数据页为32kb数据块为1024kb预取值为默认，且不使用文件系统缓存的大型DMS表空间 create large tablespace tbs_data pagesize 32k managed by database using (file /database/tbs_data/cont0 100M,file /database/tbs_data/cont1 100M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching 5、删除报错 db2 = delete from BILLINFO_HISTORY where WorkDate=2010-04-10 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor com