Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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 |
