1、如何快速得到數據庫所占存儲空間的大小(zz)
select tbspace,sum(aa.total_pages*bb.pagesize)/1024/1024 "size(m)"
from table(snapshot_container('sdncbi',-1)) AS AA,
syscat.tablespaces bb
where aa.tablespace_id=bb.tbspaceid
group by tbspace;
db2pd -d sdncbi -tablespace
表空間： Number of pending free pages = 781248 出現後
db2 force application all
運行 db2_all "db2 connect to sdncbi;db2 list tablespaces show detail;db2 connect reset"
---老：db2_all ";db2 update db cfg for sdncbi using LOGFILSIZ 51200 LOGPRIMARY 50 LOGSECOND 40"
db2_all ";db2 update db cfg for sdncbi using LOGFILSIZ 65536 LOGPRIMARY 80 LOGSECOND 16"
2、查看鎖狀態
SELECT * FROM SYSIBMADM.LOCKS_HELD
Select a.DB_NAME,a.AGENT_ID,a.APPL_NAME,b.APPL_ID,b.APPL_STATUS,a.LOCK_MODE,a.LOCK_STATUS,a.TABSCHEMA,a.TABNAME,a.DBPARTITIONNUM
from sysibmadm.locks_held a,sysibmadm.applications b
where a.agent_id=b.agent_id;
3、工具目錄數據庫
創建： DB2 ? CREATE TOOLS CATALOG
刪除：db2 "drop tools catalog toolscat in database sdncbi"
4、清除數據
import from /dev/null of del replace into table_name
alter table tblname activate not logged initially with empty table
5、建表的時候要注意分區鍵的選擇（不建議用月份做為分區鍵）
主要考慮的是數據的均勻分布，以後HASH JION 盡量減少TQ
6、DB2的編目的配置文件
\SQLLIB\DB2目錄：SQLNODIR （節點編目）和 SQLDBDIR （數據庫編目）
7、查看數據庫表的死鎖
還有參數locksize和maxlocks都會對鎖的產生有關。
查看鎖用db2 list indoubt transactions
解除鎖用db2 list indoubt transactions with prompting
db2pd -locks -transactions -agents -alldbs
db2pd -db sdncbi -lock -transactions -agents -applications -file lock_info.txt
--查找那個節點的出現鎖等待
db2pd -alldbp -db sdncbi -locks showlocks wait
--到指定的節點查詢鎖相關的信息
db2pd -dbpartitionnum 0,1 -db sdncbi -locks -transactions -applications -dynamic -file locklog
首先,將監視開關打開
db2 update dbm cfg using dft_mon_lock on
db2 update dbm cfg using DFT_MON_TABLE on
快照
db2 get snapshot for Locks on sdncbi >>snap.log
db2 get snapshot for tables on sdncbi >>snap.log
db2 get snapshot for bufferpools on sdncbi >>snap.log
db2 get snapshot for tablespaces on sdncbi >>snap.log
db2 get snapshot for database on sdncbi >>snap.log
然後再看snap.log中的內容即可。對Lock可根據Application handle（應用程序句柄）
看每個應用程序的鎖的情況。監視完畢後，不要忘了將監視器關閉
db2 update dbm cfg using dft_mon_lock off
select * from table(snapshot_lock('sdncbi',-1)) snapshot_lock
------------------------------判斷是鎖等待
db2 update monitor switches using lock on
db2 get snapshot for locks on sdncbi>snap.log
db2 update monitor switches using lock off ----也可以不用關
鎖信息得到鎖表的sql:
1.執行:
db2pd -db sdncbi -lock -transactions -reopt -applications -file c:/lock_info.log
2.在文件裡關聯字段找到引起鎖的SQL語句:
通過Locks(TranHdl)--->Transactions(AppHandl)--->Applications(C-AnchID,C-StmtUID)--->dynamic statement--->sql
8、查看表空間占用情況
select tbsp_name,sum(tbsp_total_pages) as "total",
sum(tbsp_used_pages) as "use pages",
sum( tbsp_free_pages) as "free pages",
sum(tbsp_used_pages)*1.0/sum(tbsp_total_pages)*100 as "use precent"
from "SYSIBMADM"."TBSP_UTILIZATION"
where tbsp_total_pages>0
group by tbsp_name
9、檢查數據分布是否均勻（按表空間)
nohup sh CheckPartitionnum.sh USR_TBSP_MINING >check.log &
10、檢查SQL語句
SELECT elapsed_time_min, SUBSTR (AUTHID, 1, 10) AS auth_id, agent_id,
appl_status, SUBSTR (stmt_text, 1, 60) AS sql_text
FROM sysibmadm.long_running_sql
WHERE elapsed_time_min > 0
ORDER BY elapsed_time_min DESC
11、SQL0668N 由於表 "ATOM.T_ST_USAGE_DAY" 上的原因碼為 "3"，所以不允許操作
db2 "load from /dev/null of del terminate into
atom.t_st_usage_day PARTITIONED DB CONFIG OUTPUT_DBPARTNUMS (10,36,14,17,21,22,27,30,31,32,33,34,35)";
12、取得維表的遞歸：
with n(level,schemaname,tname) as
(
select distinct 1,REFTABSCHEMA,REFTABNAME
from SYSCAT.REFERENCES
where REFTABSCHEMA not in ('DB2INFO','SYSTOOLS')
union all
select d.level+1,c.refTABSCHEMA,c.refTABNAME
from SYSCAT.REFERENCES as c,n as d
where c.tabname =d.tname and d.level < 10 ---限制運行10次
)
select schemaname,tname,max(level) flag from n group by schemaname,tname order by flag desc
13、歸檔日志處理
db2 connect to DBNAME
db2 get db cfg | awk -F= '$1 ~ /First active log file/ {print $2}' | read firstlog
db2 prune logfile prior to $firstlog
db2 terminate
14、導出存儲過程
SELECT 'db2 "EXPORT TO '||rtrim(procschema)||'.'||rtrim(procname)||'.sql of del MODIFIED BY LOBSINFILE SELECT ''SET CURRENT SCHEMA '
||rtrim(procschema)||' @''||chr(10)|| '' SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,'
||rtrim(procschema)||' @''||chr(10)||'||'text'||'||chr(10)||'' @''||chr(10) from syscat.procedures where procschema='''||rtrim(procschema)||''' and procname ='''||rtrim(procname)||'''"'
FROM SYSCAT.PROCEDURES where procschema not in ('DB2INFO','IDMMX','SQLJ','SYSFUN','SYSIBM','SYSPROC','SYSIBMADM')
15、查找非法存儲過程
SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname , ' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS ( SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y' ) ) ORDER BY spname;
16、事務日志滿
1． 在TOAD上用下面的句子查詢事務的哪個節點滿了
select
int(total_log_used/1024/1024) as "Log Used (Meg)",
int(total_log_available/1024/1024) as "Log Space Free (Meg)",
int(tot_log_used_top/1024/1024) as "Max Log Used (Meg)",
int(sec_log_used_top/1024/1024) as "Max Sec. Used (Meg)",
int(sec_logs_allocated) as "Secondaries",
int(float(total_log_used)/float(total_log_used+total_log_available)*100) as "Pct Used",
t.DB_NAME,
t.DB_PATH
from sysibmadm.snapdb t
order by int(float(total_log_used)/float(total_log_used+total_log_available)*100) desc
with ur
2. 登陸db2_dw_5執行
db2 terminate
export DB2NODE=13
pid=`db2 get snapshot for database on hebdw|grep -p log | grep oldest | cut -d= -f2`
echo $pid
db2 "force application ($pid)"
作者 myamor
