Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2794729
  • 博文数量: 423
  • 博客积分: 7770
  • 博客等级: 少将
  • 技术积分: 4766
  • 用 户 组: 普通用户
  • 注册时间: 2006-11-09 11:58
个人简介

Oracle/DB2/Postgresql/Mysql/Hadoop/Greenplum/Postgres-xl/Mongodb

文章分类

全部博文(423)

文章存档

2019年(3)

2018年(6)

2017年(27)

2016年(23)

2015年(30)

2014年(16)

2013年(31)

2012年(73)

2011年(45)

2010年(14)

2009年(30)

2008年(30)

2007年(63)

2006年(32)

分类: DB2/Informix

2017-01-01 18:14:42

*This Tip is to create a history table for runstats
*Populate the table with details as start_time,END_TEIM
* Ability to query as a later point in time for information as below
& when was RUNSTATS run for a particular table?
 & Did it successfully complete ?
& HOW long it has taken to complete RUNSTATS?
& Track for a given table the time taken for completing RUNSTATS over a period of time 
& Tabel with highest RUNSTATS execution time;

一、生成历史记录表:

create table runstats_history
(
start_time timestamp,end_time timestamp,
operation varchar(50),
schemaname,varhcar(50), tabname varchar(50),
status char(1),
duration decimal(26,12) GENERATED ALWAYS AS 
(end_time-start_time)


)



二、查看指定schmas表生成 RUNSTAT 语句


[db2inst2@db2 bash]$ db2 -x "select 'db2 RUNSTATS ON TABLE '||RTRIM(Ltrim(TABLE_SCHEMA))||'.'||RTRIM(Ltrim(TABLE_NAME))||' AND INDEXES ALL ALLOW READ ACCESS ' from SYSIBM.TABLES WHERE TABLE_SCHEMA='GHACEPS1 ' and TABLE_TYPE='BASE TABLE'" > ghepsdb1.sql


三、执行运行脚本:


[db2inst2@db2 bash]$ ./myeps.sh 

[db2inst2@db2 bash]$ more myeps.sh 
db2 connect to dhepsdb1
file=/home/db2inst2/tip01/bash/ghepsdb1.sql
while read line
do
status=F
echo $status at start time
start_time=`date +"%Y-%m-%d-%H.%m.%S"`
echo $start_tim
${line}
if [ "$?" -eq 0 ]
then
status=S

fi
end_time=`date +"%Y-%m-%d-%H.%m.%S"`
echo $end_time
echo $status at end time
echo $line | awk -v st="$start_time" -v et="$end_time" -v ss="$status" '{ split($5,s,".");print st,et,$2,s[1],s[2],ss;}' >> /home/db2inst2/tip01/bash/ddd.out

done < "$file"

db2 connect reset


四、处理输出脚本将空格转换成 ‘,’


[db2inst2@db2 bash]$ sed -e 's/\s/,/g' ddd.out  >> ddd.del

五、将输出导入到runstats_history表中
[db2inst2@db2 bash]$ db2 import from ddd.del of del insert into   runstats_history


六、查看RUNSTATS纪录

[db2inst2@db2 bash]$ db2 "select start_time,end_time,operation,schemaname,tabname,status, day(duration)|| ' Days '|| hour(duration)||' Hours '||minute(duration)||' Minutes '||second(duration) ||' Second ' as duration from runstats_history"



点击(此处)折叠或打开

  1. es 1 Second
  2. 2016-12-31-09.12.15.000000 2016-12-31-09.12.15.000000 RUNSTATS GHACEPS1 GMS_EPSWH4_20151021 S 0 Days 0 Hours 0 Minutes 0 Second
  3. 2016-12-31-09.12.15.000000 2016-12-31-09.12.16.000000 RUNSTATS GHACEPS1 STMT_SQL_TRACE_FILE S 0 Days 0 Hours 0 Minutes 1 Second
  4. 2016-12-31-09.12.16.000000 2016-12-31-09.12.16.000000 RUNSTATS GHACEPS1 CONNHEADER_SQL_TRACE_FILE S 0 Days 0 Hours 0 Minutes 0 Second
  5. 2016-12-31-09.12.16.000000 2016-12-31-09.12.16.000000 RUNSTATS GHACEPS1 CONTROL_SQL_TRACE_FILE S 0 Days 0 Hours 0 Minutes 0 Second
  6. 2016-12-31-09.12.16.000000 2016-12-31-09.12.30.000000 RUNSTATS GHACEPS1 CLD_PICKUPPARTS_HIS S 0 Days 0 Hours 0 Minutes 14 Second
  7. 2016-12-31-09.12.30.000000 2016-12-31-09.12.33.000000 RUNSTATS GHACEPS1 TMS_ALW143P_HIS S 0 Days 0 Hours 0 Minutes 3 Second
  8. 2016-12-31-09.12.33.000000 2016-12-31-09.12.33.000000 RUNSTATS GHACEPS1 EXPLAIN_INSTANCE S 0 Days 0 Hours 0 Minutes 0 Second




阅读(1307) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~