DB2学习笔记

DB2 V9性能监控脚本 脚本, 性能脚本, 性能 DB2 V9性能监控脚本 #监控缓冲池命中率 select substr(db_name,1,30) as db_name,substr(bp_name,1,30) as bp_name,data_hit_ratio_percent,index_hit_ratio_percent,total_hit_ratio_percent from sysibmadm.bp_hitratio where bp_name not like 'IBMSYSTEM%'; select * from sysibmadm.bp_hitratio #监控PACKAGE_CACHE大小

with dbcfg1 as (select int(value) as pckcachesz from sysibmadm.dbcfg where name='pckcachesz') select pckcachesz as \as \

100*pkg_cache_size_top /(pckcachesz*4096) as \Cache alloc\from dbcfg1,sysibmadm.snapdb;

#监控执行成本最高的sql语句

select agent_id,rows_selected,rows_read from sysibmadm.snapappl fetch first 10 rows only;

#监控运行最长的sql语句

select substr(appl_name,1,15) as Appl_name,elapsed_time_min as \\

substr(inbound_comm_address,1,15) as \ from sysibmadm.long_running_sql order by 2 desc;

select * from sysibmadm.long_running_sql order by 2 desc;

#监控sql准备和预编译时间最长的sql语句 average_execution_time_s sql语句平均执行时间 prep_time_ms 最长的sql预编译时间

prep_time_precent 预编译时间占整个执行时间的百分比 select

num_executions,average_execution_time_s,prep_time_ms,prep_time_precent,substr(stmt_text,1,40) as \ from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_precent desc;

#监控执行次数最多的sql语句

Select num_executions \\

substr(stmt_text,1,35) as \Stmt\from sysibmadm.top_dynamic_sql where num_executions>0 order by 1 desc fetch first 5 rows only;

Select num_executions \\

substr(stmt_text,1,35) as \Stmt\from sysibmadm.top_dynamic_sql where num_executions>0 order by 2 desc fetch first 5 rows only;

#监控排序次数最多的sql语句

select stmt_sorts,sorts_per_execution,substr(stmt_text,1,60) as stmt_text from top_dynamic_sql order by stmt_sorts fetch first 5 rows only;

#监控LOCK WAIT时间 select substr(ai.appl_name,1,20) as appl_name,substr(ai.primary_auth_id,1,10) as auth_id,ap.lock_waits as lock_waits,ap.lock_wait_time /1000 as \

(ap.lock_wait_time/ap.lock_waits) as \Wait(ms)\from sysibmadm.snapappl_info ai,sysibmadm.snapappl ap

where ai.agent_id=ap.agent_id and ap.lock_waits>0;

#监控LOCK chain 可以定位谁持有锁，谁在等待锁

select substr(ai_h.appl_name,1,10) as \App\as \

substr(lw.authid,1,10) as \as \

timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time)) as \ from sysibmadm.lockwaits lw,sysibmadm.snapappl_info ai_h where lw.agent_id_holding_lk=ai_h.agent_id;

#监控锁内存使用

with dbcfg1 as (select float(int(value)*4096) as locklist from sysibmadm.dbcfg where name='locklist'),dbcfg2 as (select float(int(value)*4096) as maxlock from sysibmadm.dbcfg where name='maxlock') select dec((lock_list_in_use/locklist)*100,4,1) as \List\

appls_cur_cons as \of Cons\as \Lock Mem Per Con (bytes)\

from dbcfg1,dbcfg2,sysibmadm.snap.db;

#监控锁升级、死锁和锁超时 select substr(ai.appl_name,1,10) as Application,substr(ai.primary_auth_id,1,10) as AuthID,int(ap.locks_held) as \

int(ap.lock_escals) as \as \TimeOuts\as \

int(ap.int_deadlock_rollbacks) as \Victim\as \Address\

from sysibmadm.snapappl ap,sysibmadm.snapappl_info ai where ap.agent_id=ai.agent_id;

#监控全表扫描的SQL

select substr(authid,1,10) as authid,substr(appl_name,1,20) as appl_name,percent_rows_selected from sysibmadm.appl_performance;

#检查PAGE CLEANERS 是否足够

with db_snap as (select float(pool_drty_pg_steal_clns) as pg_steal,float(pool_drty_pg_thrsh_clns) as chg_pg_thrsh,float(pool_lsn_gap_clns) as softmax,

float(pool_drty_pg_steal_clns + pool_drty_pg_thrsh_clns +pool_lsn_gap_clns) as total_clns from sysibmadm.snapdb)

select dec((pg_steal/total_clns)*100,4,1) as \\ from db_snap;

#检查PREFETCHER 是否足够 with bp_snap as (select substr(bp_name,1,30) as bp_name,unread_prefetch_pages,pool_async_data_reads+pool_async_index_reads as async_reads,

pool_temp_data_p_reads+pool_temp_index_p_reads as total_reads from sysibmadm.snapbp where bp_name not like 'IBMSYSTEM%')

select bp_name,unread_prefetch_pages,dec(100*(total_reads - async_reads)/total_reads,5,2) as \Synch Reads%unread_prefetch_pages/total_reads,5,2) as %unread pages\from bp_snap;

#监控数据库内存使用 select pool_id,pool_secondary_id,pool_cur_size,pool_watermark from sysibmadm.snapdb_memory_pool

#监控日志使用情况

select int(total_log_used/1024/1024) as \Used (Mb)\as \

int((float(total_log_used)/float(total_log_used+total_log_available))*100) as \Used\

int(sec_log_used_top/1024/1024) as \Sec. Used (Mb)\as \

#监控占有日志空间最旧的交易 select substr(ai.appl_status,1,20) as \as \

int(ap.UOW_LOG_SPACE_USED/1024/1024) as \Used (M)\as \

from sysibmadm.snapdb db,sysibmadm.snapappl ap,sysibmadm.snapappl_info ai where

ai.agent_id=db.APPL_ID_OLDEST_XACT and ap.agent_id=ai.agent_id;

监控存储路径

select substr(type,1,20) as type ,substr(path,1,50) as path from sysibmadm.dbpaths order by type;

监控表空间使用情况 db2 \substr(tbsp_name,1,18),tbsp_type,tbsp_free_size_kb/1024 as \

DB2 怎么样在不停止的情况下进行数据备份 1、查看数据归档方式：

命令为：db2 get db cfg for

第一个日志归档方法 (LOGARCHMETH1) = OFF OFF表示非归档，需要打开归档参数 2、修改归档参数

db2 update db configuration for using logarchmeth1 disk:/u/dbuser/archived_logs 3、重启数据库 db2 force application all db2 stop db2 start

4、进行全备份（在做联机备份之前必须要做一次全备份） db2 backup db to /data_back 备份期间可以通过下面命令查看备份信息： DB2 LIST UTILITIES SHOW DETAIL

5、进行在线备份，归档日志模式下联机备份时最好同时备份日志 db2 BACKUP DATABASE ONLINE … INCLUDE LOGS

一次DB2数据库内存耗尽故障处理过程

问题现象

P595服务器中有一个DB2数据库，在连接时，系统PAGING SPACE资源耗尽，从而宕机。

问题分析

问题初步分析

1.如果非计算内存太多，计算内存太少，有可能是这个问题导致计算内存不够用，使用到PAGING SPACE。可以使用vmo -L maxperm%命令来检查文件系统缓存参数的设置

2.如果maxperm%设置为20%左右(对于DB2环境来说，比较适合)，那么是不是DB2参数/对象本身分配的内存过多造成计算内存不够用。需要仔细检查实例参数、数据库参数是否设置不当。

3.如果上述原因排除，那么分析db2mtrk得到的数据，分析DB2数据库在运行过程中是否分配了过多的计算内存(内存参数、缓冲池可能使用了AUTOMATIC设置；在这种情况下，内存是按需分配的，理论上可以无限大)，如果是，那么分析是由于DB2实例、数据库占用的内存过多，还是代理进程(应用程序)占用的内存过多(比如使用动态数组，在死循环中不停的填充动态数组，导致动态数组无限增长，无论多少内存都能耗光)，如果存在这个问题，那么继续分析. 可以使用如下命令跟踪DB2数据库使用内存的情况。 db2mtrk -i -d -p -r 300 500 >> db2mtrk.out

#每5分钟收集一次，收集500次

4.如果上述原因排除，那么结合ipcs得到的数据，分析AIX操作系统中除了DB2使用的内存段之外，还有没有其他的非DB2进程在使用大内存段占用过多的内存，如果有，那么继续分析是否是它导致的. 服务器内存使用 内存：8G

文件系统缓存设置：maxperm%=30

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库DB2学习笔记在线全文阅读。