DB2 History Runstats

*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