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