DB2常用脚本整理
DB2常用脚本整理
--表空间扩容
ALTER TABLESPACE BAS_DW_ENT
ADD (Device '/dev/vx/rdsk/n01datadg3/dwentlv_100g_01_01' 3276800) ON DBPARTITIONNUM (1)
ADD (Device '/dev/vx/rdsk/n02datadg3/dwentlv_100g_02_02' 3276800) ON DBPARTITIONNUM (2)
ADD (Device '/dev/vx/rdsk/n03datadg3/dwentlv_100g_03_03' 3276800) ON DBPARTITIONNUM (3)
ADD (Device '/dev/vx/rdsk/n04datadg3/dwentlv_100g_04_04' 3276800) ON DBPARTITIONNUM (4)
ADD (Device '/dev/vx/rdsk/n05datadg3/dwentlv_100g_05_05' 3276800) ON DBPARTITIONNUM (5)
ADD (Device '/dev/vx/rdsk/n06datadg3/dwentlv_100g_06_06' 3276800) ON DBPARTITIONNUM (6)
ADD (Device '/dev/vx/rdsk/n07datadg3/dwentlv_100g_07_07' 3276800) ON DBPARTITIONNUM (7)
ADD (Device '/dev/vx/rdsk/n08datadg3/dwentlv_100g_08_08' 3276800) ON DBPARTITIONNUM (8)
ADD (Device '/dev/vx/rdsk/n09datadg3/dwentlv_100g_09_09' 3276800) ON DBPARTITIONNUM (9)
ADD (Device '/dev/vx/rdsk/n10datadg3/dwentlv_100g_10_10' 3276800) ON DBPARTITIONNUM (10)
ADD (Device '/dev/vx/rdsk/n11datadg3/dwentlv_100g_11_11' 3276800) ON DBPARTITIONNUM (11)
ADD (Device '/dev/vx/rdsk/n12datadg3/dwentlv_100g_12_12' 3276800) ON DBPARTITIONNUM (12)
ADD (Device '/dev/vx/rdsk/n13datadg3/dwentlv_100g_13_13' 3276800) ON DBPARTITIONNUM (13)
ADD (Device '/dev/vx/rdsk/n14datadg3/dwentlv_100g_14_14' 3276800) ON DBPARTITIONNUM (14);
db2 list tablespaces show detail;
db2 list tablespace containers for 42
--表空间扩容
ALTER TABLESPACE BAS_MK_APP
ADD (Device '/dev/n0datavg6/rmkapplv_100g_0_01/' 3276800) ON DBPARTITIONNUM (0)
ADD (Device '/dev/n1datavg6/rmkapplv_100g_1_02/' 3276800) ON DBPARTITIONNUM (1)
ADD (Device '/dev/n2datavg6/rmkapplv_100g_2_03/' 3276800) ON DBPARTITIONNUM (2)
ADD (Device '/dev/n3datavg6/rmkapplv_100g_3_04/' 3276800) ON DBPARTITIONNUM (3);
--查看表空间使用情况
db2 "select substr(tbsp_name,1,20) tbsp_name,tbsp_type,substr(tbsp_state,1,10) tbsp_state,tbsp_total_size_kb/1024 tot_size,
tbsp_free_size_kb/1024 free_size,tbsp_utilization_percent used_pect,TBSP_USING_AUTO_STORAGE,TBSP_PAGE_SIZE/1024 PGSIZE,DBPARTITIONNUM
from sysibmadm.tbsp_utilization where tbsp_total_size_kb/1024>1024 and tbsp_utilization_percent>90 order by DBPARTITIONNUM with ur"
--检查表空间状态
db2 "select tbsp_id,substr(tbsp_name,1,30) tbsp_name,substr(tbsp_state,1,10) tbsp_state,tbsp_utilization_percent,dbpartitionnum from sysibmadm.tbsp_utilization with ur"
db2 list tablespaces show detail --在单分区上查看表空间的状态,正常返回0x0000
db2_all "db2 list tablespaces show detail" --在所有分区上查看表空间的状态
db2tbst 0x0000
State = Normal
--查看表空间信息
db2 list tablespaces
show detail
db2 list tablespace containers for 0
--查看DMS表空间中是否还有可用页
$db2 list tablespaces show detail --在单分区上查看表空间的是否还有可用页
$ db2_all ";db2 connect to qhbidb;db2 list tablespaces show detail" --在所有分区上查看表空间是否还有可用页
--获取表空间的高水位信息
db2 "SELECT varchar(tbsp_name, 16) as tbsp_name,RECLAIMABLE_SPACE_ENABLED,TBSP_USED_PAGES,
TBSP_FREE_PAGES, TBSP_PAGE_TOP from TABLE (MON_GET_TABLESPACE('',-2)) AS t
where t.TBSP_PAGE_TOP > t.TBSP_USED_PAGES"
>>-ALTER TABLESPACE--tablespace-name---------------------------->
>----+-REDUCE--+-------------------------------+--+-----------------------------+-+
+-
database-container-clause
-+ '-
on-db-partitions-clause
-'
+-
all-containers-clause
-----+
+-MAX---------------------------+
+-STOP--------------------------+
'-integer--+---------+----------'
+-K-------+
+-M-------+
+-G-------+
'-PERCENT-'
+-LOWER HIGH WATER MARK--+------+--------------------------------------------+
'-STOP-'
'----------------------------------------------------------------------------'
展示了与回收表空间存储器相关的两个子句及其语法。其中MAX参数可以指定最大限度的降低高水位标记来释放空间。在运行REDUCE命令后，在数据块移动的过程中，
也可以使用STOP参数来停止表空间移动。如果是DMS表空间，则需要先运行LOWER HIGH WATER MARK子句降低高水位标记，再运行REDUCE子句释放表空间。
如果是启用了自动存储的表空间，直接运行REDUCE子句即可。
清单 7. 自动存储表空间
ALTER TABLESPACE REDUCE 10 M清单 8. DMS 表空间
ALTER TABLESPACE LOWER HIGH WATER MARK
ALTER TABLESPACE REDUCE (ALL CONTAINERS 10 M)
--数据库大小
db2 "call get_dbsize_info(?,?,?,-1)"
--数据库load历史信息文件清理
--建议定期对/dbhome/qhbiinst/NODE0000/SQL00001/db2rhist.asc清理，该文件过大会导致LOAD操作是存在性能问题。
--例如：清除节点1，2014年10月之前的历史文件记录
export DB2NODE=1
db2 terminate
db2 connect to qhbidb
db2 prune history 201410
--故障日志抓取
cd /db2diaglog/db2dump
--将FODC_Trap_YYYY_MM_DD-HH.MI.SS.MS文件夹全部内容取下来
db2diag -t 2014-11-30-23.00.00:2014-12-01-03.00.00 >diag_201441201.log
db2diag -t 2015-8-18 -level "Severe,Error"
--查看load历史文件大小
du -sm /db2data/qhbiinst/NODE0000/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
du -sm /db2data/qhbiinst/NODE000[1-8]/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
du -sm /db2data/qhbiinst/NODE000[9-16]/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
du -sm /db2data/qhbiinst/NODE000[17-24]/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
du -sm /db2data/qhbiinst/NODE000[1-8]/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
du -sm /db2data/qhbiinst/NODE000[1-8]/SQL00001/db2rhist*
awk '{a+= $0}END{print a"MB"}'
--检查各个点数据库的运行状态
$db2_all "db2pd –db qhbidb -"
--如果部分节点状态不正常，可以通过如下方式去启动数据库：
$db2start dbpartitionnum nodenum
--查看表的状态
db2 "select tabname, colcount, status from syscat.tables where tabschema not like 'SYS%' order by tabname"
db2 load query table test
--DB2状态监控
$db2_ps
$db2gcf -u -p 0 -i qhbiinst
Instance : qhbiinst
DB2 Start : Success
Partition 0 : Success
db2 deactivate database 数据库名称 -------------去激活数据库
db2 activate database 数据库名称 -------------激活数据库
connect reset -------------断开当前数据库连接
db2 restart database databasename -------------重新启动数据库
--两种可以备份方式
---脱机数据库备份，需要至数据库的独占连接，它将备份数据库中的所有表空间；
backup db sample to c:\backup with 3 buffers buffer 1000 without prompting
---联机数据库备份，可以备份整个数据库或者单个表空间。
backup db sample tablespace (userspace1) online to /dev/rmt0 without prompting
--要执行联机备份，确保数据库参数 logretain = on 或 设置了logarchmetd1支持联机备份
--查看仓库的transaction
db2pd -d qhbidb -transactions
db2pd -d qhbidb -alldbp -apinfo 59898(applid)
ps -ef
grep "Application PID"
--停止数据库
db2 force application all
db2 terminate
db2stop
db2start
db2_all "db2 connect to qhbidb"
--经使用db2 activate db qhbidb，再使用db2_all "db2 connect to qhbidb"查看表是否可以查询。
--查看当前节点回滚情况
db2pd -d qhbidb -reco
--查看当前主机所有分区的回滚情况
db2pd -d qhbidb -reco -alldbp
db2_all "db2 list utilities show detail"
--查看不确定事务
export DB2NODE=0
db2 terminate
db2 connect to qhbidb
db2 values current dbpartitionnum
db2 list indoubt transactions
--重组表检查
db2 "call reorgchk_tb_stats('T','ALL')"
--重组索引检查
db2 "call reorgchk_ix_stats('T','ALL')"
--统计信息检查
db2 "select date(STATS_TIME),count(1) from syscat.tables where type='T' group by date(STATS_TIME) with ur"
--缓冲池命中率
db2 "select * from sysibmadm.bp_hitratio"
db2 get snapshot for bufferpools on qhbidb global
--锁升级、锁等待
db2 "select lock_waits,deadlocks,lock_escals,lock_timeouts,dbpartitionnum from sysibmadm.snapdb"
--排序溢出
db2 "select total_sorts,sort_overflows,dbpartitionnum from sysibmadm.snapdb"
--SQL语句分析
db2 "select * from sysibmadm.snapdyn_sql"
--表状态检查
db2 "select substr(tabschema,1,10) tabschema,substr(tabname,1,30) tabname,status,type from syscat.tables where status<>'N'"
--有效索引读
db2 "select rows_read/(rows_selected+1),dbpartitionnum as IREF from sysibmadm.snapdb"
--平均结果集大小
db2 "select rows_selected/(select_sql_stmts+1) as avg_result_set,dbpartitionnum from sysibmadm.snapdb"
--同步读取比例
db2 "select 100-(((pool_async_data_reads+pool_async_index_reads*100)/(pool_index_p_reads+1)) as SRP from sysibmadm.snapdb where DB_NAME='ODSDB'"
--脏页偷取
db2 "select pool_dirty_pg_steal_clns from sysibmadm.snapdb"
--缓冲区读写IO响应时间
db2 "select tbsp_name,(pool_read_time/(pool_data_p_reads+pool_index_p_reads+pool_temp_data_p_reads+pool_temp_index_p_reads+1)) as tsorms from sysibmadm.snaptbsp
order by tsorms desc fetch first 10 rows only"
--平均每条交易的排序次数
db2 "select total_sorts/(commit_sql_stmts+rollback_sql_stmts),dbpartitionnum from sysibmadm.snapdb"
--总的事务数量
db2 "select commit_sql_stmts + rollback_sql_stmts,dbpartitionnum from sysibmadm.snapdb"
--每个事务包括查询SQL的数量
db2 "select select_sql_stmts,(commit_sql_stmts + rollback_sql_stmts),dbpartitionnum from sysibmadm.snapdb"
--每个事务包括增删改的语句数量
db2 "select uid_sql_stmts,(commit_sql_stmts + rollback_sql_stmts),dbpartitionnum from sysibmadm.snapdb"
--每个事务返回的结果集行数
db2 "select row_selected,(commit_sql_stmts + rollback_sql_stmts),dbpartitionnum from sysibmadm.snapdb"
--每个事务返回读的行数
db2 "select rows_read,(commit_sql_stmts + rollback_sql_stmts),dbpartitionnum from sysibmadm.snapdb"
--检查Package状态
db2 "select valid,count(1) from syscat.packages group by valid with ur"
--监控表的使用情况
select substr(tabname,1,50) tabname,count(*)
from sysibmadm.snaptab a,syscat.tables b
where a.tabschema=b.tabschema
and a.tabname=b.tabname
and b.tbspace like 'TBS_AGG'
group by substr(a.tabname,1,50) having count(*)=1;
--查看表所属的表空间
db2 "SELECT TABLES.TABSCHEMA, TABLES.TABNAME, TBSPACES.TBSPACE FROM SYSCAT.TABLES AS TABLES, SYSCAT.TABLESPACES AS TBSPACES
WHERE TABLES.TBSPACEID = TBSPACES.TBSPACEID AND TABNAME = 'SALES'"
-- 查找存储过程所对应的程序包的名称
SELECT
CHAR(PROCSCHEMA,20) AS PROCSCHEMA, -- 模式名称
CHAR(PROCNAME,20) AS PROCNAME, -- 存储过程名称
CHAR(B.BNAME,20) AS PKGNAME -- 绑定包名称
FROM SYSCAT.PROCEDURES A
JOIN SYSCAT.ROUTINEDEP B ON A.SPECIFICNAME = B.ROUTINENAME
WHERE PROCSCHEMA='KF2' AND PROCNAME='EXPLAINPLAN_TEST' WITH UR;
--查看表的相关信息
db2 reorgchk update statistics on table NWH.CUST_BLACK
db2_all ";db2 connect to qhbidb;db2 reorg table NWH.CUST_BLACK"
本文永久更新链接地址：
评论暂时关闭