DB2监控常用语句

1.数据库对象信息

   存档在SYSCAT系统编目视图中

   SELECT * FROM SYSCAT.TABLES;--表

   SELECT * FROM SYSCAT.ROUTINES WHERE ROUTINESCHEMA='SAFE_DEV' AND ROUTINETYPE='P' ORDER BY ROUTINENAME;--存储过程

   SELECT * FROM SYSCAT.BUFFERPOOLS;--缓冲池

   .

   .

   .

   .

2.权限相关的系统编目视图

查询和权限有关的系统编目视图

SELECT TABNAME FROM SYSCAT.TABLES FROM WHERE TABSCHEMA='SYSCAT' AND TABNAME LIKE '%AUTH';

3.SYSIBM管理视图

list tables for schema sysibmadm;

4.监控缓冲池命中率

  4个默认创建的隐含缓冲池排除

  SELECT SUBSTR(BP_NAME,1,30),DATA_HIT_RATIO_PERCENT,INDEX_HIT_RATIO_PERCENT,TOTAL_HIT_RATIO_PERCENT FROM SYSIBMADM.BP_HITRATIO WHERE BP_NAME NOT LIKE 'IBMSYSTEM%';

5.监控PACKAGE CACHE大小

6.监控执行成本最高的SQL语句

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

7.监控运行最长的SQL语句

 select substr(appl_name,1,15) as appl_name,appl_status, elapsed_time_min as elapsedMin,substr(authid,1,10) as auth_id,substr(inbound_comm_address,1,15) as address,substr(stmt_text,1,30) as sql_stmt from sysibmadm.long_running_sql order by appl_status desc;

8.监控SQL准备和预编译时间最长的SQL语句

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

9.监控执行次数最多的SQL语句

select num_executions,average_execution_time_s,stmt_sorts,sorts_per_execution ,substr(stmt_text,1,40) as sql_text from sysibmadm.top_dynamic_sql where num_executions>0  order by num_executions fetch first 5 rows only;

10.监控排序次数最多的SQL语句

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

11.监控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,ap.lock_wait_time/1000 as total_waits,(ap.lock_wait_time/ap.lock_waits) as avg_waits from sysibmadm.snapappl_info ai,sysibmadm.snapappl ap where ai.agent_id = ap.agent_id and ap.lock_waits > 0;

12.监控LOCK CHAIN

定位谁持有锁,谁在等待锁

select substr(ai_h.appl_name,1,10) as hold_app,substr(ai_h.primary_auth_id,1,10) as holder,substr(lw.appl_name,1,10) as wait_app,substr(lw.authid,1,10) as waiter,lw.lock_mode,lw.lock_object_type,substr(lw.tabname,1,20) as tabname,substr(lw.tabschema,1,10) as schema, timestampdiff(2,char(lw.snapshot_timestamp-lw.lock_wait_start_time)) as waiting_time from sysibmadm.lockwaits lw,sysibmadm.snapappl_info ai_h where lw.agent_id_holding_lk = ai_h.agent_id;

13.监控锁内存使用

14.监控锁升级，死锁和锁超时

select substr(ai.appl_name,1,10) as application ,substr(ai.primary_auth_id,1,10) as authid,int(ap.locks_held) as locks_held,int(ap.lock_escals) as lock_escals,int(ap.lock_timeouts) as lock_timeouts, int(ap.deadlocks) as deadlocks,int(ap.int_deadlock_rollbacks) as int_deadlock_rollbacks,substr(inbound_comm_address,1,15) as address from sysibmadm.snapappl ap,sysibmadm.snapappl_info ai where ap.agent_id = ai.agent_id;

15.监控全表扫描的SQL

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

16.检查PAGE CLEANERS是否足够

17.监控PREFETCHER是否足够

18.监控数据库内存使用

select pool_id,pool_secondary_id,pool_watermark from sysibmadm.snapdb_memory_pool;

19.监控日志使用情况

select int(total_log_used/1024/1024) as log_used,int(total_log_available/1024/1024) as log_free,int((float(total_log_used)/float(total_log_used+total_log_available))*100) as percent_used,int(tot_log_used_top/1024/1024) as max_log_used,int(sec_log_used_top/1024/1024) as max_sec_used,int(sec_logs_allocated) as secondaries from sysibmadm.snapdb;

20.监控占用日志空间最旧的交易

select substr(ai.appl_status,1,20) as status,substr(ai.primary_auth_id,1,10) as auth_id,int(ap.uow_log_space_used/1024/1024) as log_used,int(ap.appl_idle_time/60) as idle_for_min,ap.appl_con_time 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;

21.监控存储路径

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

22.监控表空间使用情况

select substr(tbsp_name,1,18) as tabspace_name,tbsp_type,tbsp_free_size_kb/1024 as size_m,tbsp_utilization_percent from sysibmadm.tbsp_utilization order by tbsp_type;

23.SYSSTAT

存放和统计信息相关的系统编目视图。

select tabname from syscat.tables where tabschema='SYSSTAT';

 TABNAME                                                                                                                         

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

COLDIST                                                                                                                         

COLGROUPDIST                                                                                                                    

COLGROUPDISTCOUNTS                                                                                                              

COLGROUPS                                                                                                                       

COLUMNS                                                                                                                         

FUNCTIONS                                                                                                                       

INDEXES                                                                                                                         

ROUTINES                                                                                                                        

TABLES

select COUNT(1) from sysstat.tables where tabschema='SAFE_DEV';

统计后的行数，理论占用页数，实际占用页数，溢出行数，总数比超过5%，那就应该进行碎片整理

SELECT CARD,NPAGES,FPAGES,OVERFLOW FROM SYSSTAT.TABLES WHERE TABNAME='TBL_DATAIMPDTLERR';