RUNSTATS utility perform statistics in DB2. Should be perform Weekly or Monthly basis.
Following Script helps to gather statistics on all tables excluding XML columns and there associated indexes . Output will be emailed.
First, it will generate the RUNSTATS command-line output into [/dbawork/runstats.out], then same output file will be executed with command-line db2 -tvf
Script can be modified as per your need.
Script tested on created on AIX 7.1 (Bash) DB2 V 10.5.
Required Input
Provide schema name in query
Define email Id’s to mailgroup.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
#!/bin/bash # The following three lines have been added by IBM DB2 instance utilities. if [ -f /home/db2inst1/sqllib/db2profile ]; then . /home/db2inst1/sqllib/db2profile fi mailgroup="<provide email address>" echo "Type database name" read dbname clear echo "======================================" echo echo "Connecting $dbname" echo echo "======================================" db2 connect to $dbname db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' excludING XML COLUMNS and indexes all;'from syscat.tables where type = 'T' and tabschema='<PROVIDE SCHEMA NAME>'" | tee /dbawork/runstats.out db2 -tvf /dbawork/runstats.out |tee /dbawork/$dbname_statistics.out db2 connect reset mail -s "$dbname DB Statistics Executed" $mailgroup < /home/db2inst1/$dbname_statistics.out |
