db2 LIST COMMAND OPTIONS
牛新庄的，当我们commit事务时，我们的事务将会被记录在日志中(db2采用提前写入日志的方式)，但对数据做的更改不一定写入磁盘，有可能在缓冲池中，此时，用户将会收到commit成功的消息。还有一种情况，当用户在一个事务中对数据库做大规模操作时，有可能数据已经写入了磁盘，但是commit并没有结束。如果在这两种情况下，系统突然断电，或因其他原因崩溃，那么数据库将会处于一种不一致的状态，当下次启动db2时，db2将会自动启动崩溃恢复，它将根据日志文件，回滚那些没有提交，但数据库已经写入磁盘的事务，重新提交那些已经提交，但数据并没有写入磁盘的事务，以此来保证数据库的一致性。
2.
db2 "select * from table(snap_get_dbm(-1)) as snap”
db2 get snapshot for dbm
3.
db2pd 命令工具
用于监控和DB2疑难分析
非侵入式工具，对DB2的性能影响小
-applications -agents -transactions -bufferpools -logs locks
-tablespaces -dynamic -static -fcm -mempools -memsets -dbmcfg
-dbcfg -catalogcache -sysplex -tcbstats -reorg -recovery -reopt -osinfo
4、修改 bufferpool 大小为1G。
首先我们必须确定数据库使用的页大小。 例如如果是4K。
那么 可以先检查当前数据库已有的buffer pool 和大小
db2 "select * from syscat.bufferpools "
结果显示系统存在默认的bufferpool IBMDEFAULTBP, 大小为4K（1*4096)，这个数值明显太小;
直接修改IBMDEFAULTBP的大小:
db2 " alter bufferpool IBMDEFAULTBP immediate size 262144 "
现在bufferpool的大小为1024M ( 262144*4096)
5. db2 "select tabname,indname from syscat.indexes where tabname=<tb_name>'"
db2 "select tabname,indname from syscat.indexes where indname=<in_name> "
6.Check invalid package
db2 "select char(BOUNDBY,15)boundby, char(PKGSCHEMA, 15)pkgschema,char(PKGNAME, 15)pkgname from syscat.packages where valid='N' and pkgschema='NULLID'"
7.
db2 list history dropped table all for db sample
=============
db2 rollforward db db_name query status --> to check the next log file and if we after restore db also can check if the db need to roll-forward recovery .
手动归档 ：
db2 archive log for db db_name;
＝＝＝
db2 rollforward to specific time?
1--------80000.
=======恢复一个已删除的表
在空间中创建表，可以选用空间级备份和数据库级备份
在删除表之前，一定要做个备份。
1.
db2 alter tablespace tbsName dropped table recovery on
2.
db2 restore db dbName tablespace ( tbsName ) online incremental auto from <path> taken at 2011090*****
3. db2 list history dropped table all for tbname
4. db2 restore db name to end of logs tablespace online recover dropped table
dropped_table_id to export_directory
5.connect to db
create table
6.
db2 import from /***/data.txt of del insert into ***
7. 监控
==================================================
==================================================
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 "Package Cache
Size",pkg_cache_lookups as
"Lookups",pkg_cache_inserts as "Inserts",pkg_cache_num_overflows as "Overflows",
100*pkg_cache_size_top /(pckcachesz*4096) as
"%PKG 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 "Elapsed Min.",appl_status as "Status ",substr(authid,1,10) as
auth_id,
substr(inbound_comm_address,1,15) as
"IP Address",substr(stmt_text,1,30) as
"SQL Statement"
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 "SQL_Text"
from
sysibmadm.query_prep_cost where
average_execution_time_s>0 order by
prep_time_precent desc;
#监控执行次数最多的sql语句
Select
num_executions "Num
Execs",average_execution_time_s
as "Avg
Time(sec)",stmt_sorts as
"Num Sorts",sorts_per_execution as "Sorts Per Stmt",
substr(stmt_text,1,35) as
"SQL Stmt" from sysibmadm.top_dynamic_sql where
num_executions>0 order by
1 desc
fetch first 5 rows only;
Select
num_executions "Num
Execs",average_execution_time_s
as "Avg
Time(sec)",stmt_sorts as
"Num Sorts",sorts_per_execution as "Sorts Per Stmt",
substr(stmt_text,1,35) as
"SQL 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;
#监控LOCKWAIT时间
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 "Total
Wait(s)",(ap.lock_wait_time/ap.lock_waits) as
"Avg 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
"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,10)
as "TabName",substr(lw.tabschema,1,10) as
"Schema",
timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time))
as "waiting(s)"
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 "%Lock
List",dec((lock_list_in_use/(locklist*(maxlocks/100))*100),4,1)
as "% to
Maxlock",
appls_cur_cons
as "Number of
Cons",lock_list_in_use/appls_cur_cons as
"Avg 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 "#Locks",
int(ap.lock_escals)
as "Escalations",int(ap.lock_timeouts)
as "Lock
TimeOuts",int(ap.deadlocks)
as "Deadlocks",
int(ap.int_deadlock_rollbacks) as "Dlock Victim",substr(inbound_comm_address,1,15) as
"IP 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 "% Steals
",dec((chg_pg_thrsh/total_clns)*100,4,1) as "%Threshold",dec((softmax/total_clns)*100,4,1) as "%
softmax" 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",dec(100*
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
"Log Used (Mb)",int(total_log_available/1024/1024) as
"Log Space Free(Mb)",
int((float(total_log_used)/float(total_log_used+total_log_available))*100)
as "Pct Used",int(tot_log_used_top/1024/1024) as
"Max Log Used (Mb)",
int(sec_log_used_top/1024/1024) as
"Max Sec. Used (Mb)",int(sec_logs_allocated) as "Secondaries" from
sysibmadm.snapdb;
#监控占有日志空间最旧的交易
select
substr(ai.appl_status,1,20) as
"Status",substr(ai.primary_auth_id,1,10) as
"Authid",substr(ai.appl_name,1,15) as
"Appl Name",
int(ap.UOW_LOG_SPACE_USED/1024/1024) as
"Log Used (M)",int(ap.appl_idle_time/60) as
"Idle for(min)",ap.appl_con_time as "Connected Since"
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 "select
substr(tbsp_name,1,18),tbsp_type,tbsp_free_size_kb/1024 as
"tbsp_free_size(MB)",tbsp_utilization_percent from
sysibmadm.tbsp_utilization;
1023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
