*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"
-
es 1 Second
-
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
-
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
-
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
-
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
-
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
-
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
-
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) |