#!/bin/ksh93
#set tabstop=4 to display correctly in vi
#
#-------------------------------------------------------------------------------
# File Name:    Track_Dbsize.ksh (ksh93)
#               Based on existing copyleft code at website http://www.sustainablesoftware.net - please respect these terms.
#
# Purpose:      Track database growth. Record database space utilisation for all V9.7+ automatic storage tablespaces over time.
#               Take a holistic view of database 'size' - also include metrics for transaction and archive log space on disk, diagnostic path and $INSTHOME.
#               This allows reports of database size by tablespace, by raw data plus index, or by filesystem utilisation over time and by database partition.
#               Exclude local backups to disk and any rotating or alternate diagnostic paths.
#               Also exclude archive logs handled by TSM or vendor packages, since these are typically staged to external tape.
#
#	*** HEALTH WARNING: This has NOT been tested on a DPF MPP setup, due to temporary lack of an appropriate test system. 
#						It will probably work on MPP, but I cannot guarantee that.
#						The code is nevertheless non-destructive. Please report any MPP issues to feedback@sustainablesoftware.net
#	***
#
# Usage:        Track_Dbsize.ksh $1 instance_name $2 database_name
#               Assumes runner is the instance owner.
#               Further assumes, if run on a DPF system, that the catalog partition is 0, the first data partition is 1 and that partitions 0 and 1 are on the same physical server.
#
# Out of scope: pre-V9.7 databases; tablespaces not under automatic storage.
#
# File dependencies:
#               ${INSTHOME}/sqllib/db2profile
#               ${INSTHOME}/sqllib/userprofile
#               ${INSTHOME}/FUNCTIONS            - the userprofile exports this as $FPATH.
#               ${INSTHOME}/MAINTENANCE          - the userprofile exports this as $MAINTENANCE_DIR.
#               ${INSTHOME}/MAINTENANCE/log      - the userprofile exports this as $MAINTENANCE_LOGS.
#               ${INSTHOME}/MAINTENANCE/log/work - the userprofile exports this as $MAINTENANCE_WORK.
#
# File dependencies for partitioned databases:
#               None - this script operates on all partitions.
#
# Calls:        ${MAINTENANCE}/Exit.ksh on error
#
# Setup:        1. You must first set up the DB2 Maintenance Suite and DB2 Function Library from www.sustainablesoftware.net/downloads.htm
#                  and follow the readme files for local site customisations.
#               2. All metrics are recorded in the following table, which you must create before the first run:
#                   create table dba.dbsize (
#                   when_snap       timestamp not null with default current timestamp,
#                   what_snap       char(10)  not null,
#                   dbpartition     smallint  not null with default,
#                   tbspace         char(18)  not null with default,
#                   snap_val_Mb     integer   not null
#                   ) in single_partition_tablespace_ofyourchoice index in single_partition_tablespace_ofyourchoice ;
#                   alter table dba.dbsize add constraint dbsize_c1 check (what_snap in ('TABLESPACE','TRANLOG','ARCHLOG','DIAGPATH','INSTHOME'));
#                   create unique index dba.dbsize_pk on dba.dbsize (when_snap asc, what_snap asc, dbpartition asc, tbspace asc) cluster allow reverse scans;
#                   alter table dba.dbsize add constraint dbsize_pk primary key (when_snap ,what_snap ,dbpartition, tbspace);
#
# Customisation:You may wish to include local backups to disk and disk staging directories for archive logs used by vendor extensions.
#               If so, alter the check constraint on what_snap.
#
# Date:         July 2013.
#
# Author:       Alex Levy t/a http://www.sustainablesoftware.net
# Credits:
#-------------------------------------------------------------------------------

# Functions in $FPATH never exit, they only return.
# We can either test each return code or use a trap. If we do use a trap, and we wish to test the return code first,
# then the test must be part of the same statement, e.g. F_CONNECT $DBNAME $DEBUG_MODE || die.
# The trap statement and the 2 inline functions 'FatalError' and 'die' can handle the exit for all error conditions.
# The 'die' function is called on any error detected by the program logic.
# The FatalError function is triggered by any non-zero signal, including the exit code from 'die'.

trap FatalError ERR

function FatalError {
# Single point of exit on any error condition.
# This relies on a signal trap. ERR is any non-zero exit status and is Korn shell only.
# See 'man trap' for further detail.
# Can also call this function by 'kill -s ERR $$ 2>/dev/null'

RC=$?
if   (( RC == 99 ))
then # error has already been trapped by function 'die', do nothing more except exit
     ${MAINTENANCE_DIR}/Exit.ksh 99
else
     print ERR signal received
     if   [[ $LOG == "NOLOG" || ! -f $LOG || ! -s $LOG ]]
     then F_MESSAGE "$script Ends with error(s), see stdout above." PLAIN I +x
     else F_RESET_OUTPUT
          cat $LOG
          F_MESSAGE "$script ends with error(s). Please review log file $LOG . " PLAIN I +x | tee -a $LOG
          F_MESSAGE "The log file is displayed above for ease of reference. " PLAIN I +x | tee -a $LOG
     fi
     ${MAINTENANCE_DIR}/Exit.ksh 4
fi
}

function die {
# Single point of exit on any error condition caught by the program logic.
# Restore stdout and stderr if required, before flowing to FatalError.

LOG=${LOG:=NOLOG} # use $LOG if set, otherwise assign a value of NOLOG to $LOG.
LINE=${1:=unknown}
LOCATION=${2:=unknown}

if   [[ $LOG == "NOLOG" || ! -f $LOG || ! -s $LOG ]]
then F_MESSAGE "$script Ends with error(s) at line $LINE, location $LOCATION; see stdout above." PLAIN I +x
else F_RESET_OUTPUT
     cat $LOG
     F_MESSAGE "$script ends with error(s) at line $LINE, location ${LOCATION}. Please review log file $LOG . " PLAIN I +x | tee -a $LOG
     F_MESSAGE "The log file is displayed above for ease of reference. " PLAIN I +x | tee -a $LOG
fi

# trigger FatalError
exit 99
}

#-------------------------------------------------------------------------------
# Declare and test variables.
#-------------------------------------------------------------------------------
. ~/.profile
set -u                              # assert all variables are defined and set.
set -e                              # reject 'simple' o/s errors
typeset script=$(basename $0 .ksh)  # Strip off path and '.ksh' extension.
DEBUG_MODE="+x"                     # Default value - disable debugging.
typeset -i RC=0                     # return code from function calls and o/s
typeset -i counter=999
now=$(date +%Y%m%d%H%M%S)
typeset LOG=NOLOG                   # runtime log, set later
typeset diagpath=                   # set later
typeset tranlogpath=                # "
typeset archlogpath=                # "
typeset -i num_parts num_servers    # number of database partitions, and for MPP systems, number of host servers.
typeset -i num_data_parts           # number of database partitions with non-catalog data
typeset insthome_fs diagpath_fs     # filesystem names
typeset -i insthome_Mb diagpath_Mb tranlog_Mb archlog_Mb size_Mb    # metrics in megabytes

# Check the call
(( $# == 2 )) || F_ERROR_MESSAGE " Usage! I said:  $0 <instance_name> <database_name>" $DEBUG_MODE
(( $# == 2 )) || die $LINENO 1
typeset -l  INSTNAME=$1
typeset -l  DBNAME=$2
if   [[ $INSTNAME == $USER ]]
then :
else
     F_ERROR_MESSAGE "$0 : The runner must be the DB2 instance owner. Exiting ..." $DEBUG_MODE
     die $LINENO 2
fi

# Assert all required directories are in place and are accessible or executable;
# the first function call tests for $INSTHOME and the db2profile and executes db2profile.
F_SETUP_DB2_ENVIRONMENT $HOME $INSTNAME $DBNAME $DEBUG_MODE || die $LINENO 3
for dir in  $FPATH                  \
            $MAINTENANCE_DIR        \
            $MAINTENANCE_LOGS       \
            $MAINTENANCE_WORK
do
    F_DIR_CHECK $dir $DEBUG_MODE || die $LINENO 4
done

# With these basic checks out of the way, direct all output to a timestamped log file.
LOG=${MAINTENANCE_LOGS}/${script}.${DBNAME}.${now}.log
F_INIT_OUTPUT_FILE $INSTNAME $LOG $DEBUG_MODE || die $LINENO 6
export LOG
F_REDIRECT_OUTPUT $LOG $DEBUG_MODE || die $LINENO 7

# Check no offline or online backup is running before connecting.
# This script could run concurrently against an online backup, but that would suggest a scheduling error.
db2 -v attach to $INSTNAME || die $LINENO 8
while (( counter > 0 ))
do
    counter=$(db2 list utilities | grep -ic offline) || : #suppress trap of grep return code 1 if no match found
    (( counter > 0 )) && F_MESSAGE "Backup(s) are running." PLAIN I $DEBUG_MODE
    (( counter > 0 )) && sleep 300
done

export DB2NODE=0 # only meaningful in DPF
F_TEST_DBNAME  $DBNAME $DEBUG_MODE && F_CONNECT $DBNAME $DEBUG_MODE || die $LINENO 9

# Disable trap from here on, since we want explicit return code checks for DB2 commands and SQL queries.
trap " " ERR

# STEP 1: INSTANCE HOME
insthome_fs=$(df -m $INSTHOME | tail -1 | awk '{print $1}' )   # filesystem
insthome_Mb=$(df -m $INSTHOME | tail -1 | awk '{print $2}' )   # filesystem size in Mb
F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'INSTHOME',DEFAULT,DEFAULT,${insthome_Mb})" "-xnq" 0 $DEBUG_MODE
RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize insthome size value, please review stdout." || die $LINENO 10

# STEP 2: DIAGNOSTIC PATH/ FFDC directory
# Do not double-count the diagpath if it is mounted on the $INSTHOME filesystem:
# Published IBM best practice for storage is to have a separate filesystem for FFDC, but by default it is located at $INSTHOME/sqllib/db2dump.
F_RUN_SQL "select value from  sysibmadm.dbmcfg where name = 'diagpath'" "-xnq" 0 $DEBUG_MODE
RC=$?; F_TEST_DB2_RETURNCODE $RC 0 $0 11 "Error querying sysibmadm.dbmcfg for DIAGPATH value." || die $LINENO 10
diagpath=${SQLVAR1%\/*} # remove any trailing '/'
diagpath_fs=$(df -m $diagpath | tail -1 | awk '{print $1}')    # filesystem name
diagpath_Mb=$(df -m $diagpath | tail -1 | awk '{print $2}')    # filesystem size in Mb
if   [[ $diagpath_fs == $insthome_fs ]]
then :
else
     F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'DIAGPATH',DEFAULT,DEFAULT,${diagpath_Mb})" "-xnq" 0 $DEBUG_MODE
     RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize diagpath size value, please review stdout." || die $LINENO 10
fi

# STEP 3: LOG DIRECTORIES
# Get the number of partitions for this instance: the transaction and archive log directories are per partition and may well be on separate filesystems.
# On DPF MPP systems, they will also be on separate physical servers, in which case forget about rsh/ssh/dsh calls to query filesystem size:
# just make the simplifying assumption that log data is more or less evenly distributed across servers.
# Also allow for the fact that the catalog partition usually consumes significantly less log space than data partitions.
F_RUN_SQL "select num_dbpartitions from sysibmadm.env_inst_info where inst_name = '${INSTNAME}'" "-xnq" 0 $DEBUG_MODE
RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 14 "Query error against sysibmadm.env_inst_info." || die $LINENO 10
num_parts=${SQLVAR1}
(( num_parts == 1 )) && num_servers=1 && num_data_parts=1
(( num_parts > 1  )) && num_servers=$(awk '{print $2}' $INSTHOME/sqllib/db2nodes.cfg | sort -u | wc -l) && (( num_data_parts = num_parts - 1 ))
if  (( num_parts == 1 ))
then
    F_RUN_SQL "select value from  sysibmadm.dbcfg where name = 'logpath'"  "-xnq" 0 $DEBUG_MODE
    RC=$?; F_TEST_DB2_RETURNCODE $RC 0 $0 11 "Error querying sysibmadm.dbcfg for LOGPATH value." || die $LINENO 10
    tranloggpath=${SQLVAR1%\/*} # remove any trailing '/'
    tranlog_Mb=$(df -m $tranlogpath | tail -1 | awk '{print $2}') # filesystem size in Mb
    F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'TRANLOG',DEFAULT,DEFAULT,${tranlog_Mb})" "-xnq" 0 $DEBUG_MODE
    RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize tranlog size value, please review stdout." || die $LINENO 10
	set +e # allow for return code 1 from circular logging databases
    F_RUN_SQL "select replace(value,'DISK:','') from  sysibmadm.dbcfg where name = 'logarchmeth1' and value like 'DISK:%'"  "-xnq" 1 $DEBUG_MODE
    RC=$?; F_TEST_DB2_RETURNCODE $RC 1 $0 11 "Error querying sysibmadm.dbcfg for LOGARCHMETH1 value." || die $LINENO 10
    if  (( RC == 0 ))
    then
        archlog_Mb=$(df -m ${SQLVAR1} | tail -1 | awk '{print $2}')
        F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'ARCHLOG',DEFAULT,DEFAULT,${archlog_Mb})" "-xnq" 0 $DEBUG_MODE
        RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize archlog size value, please review stdout." || die $LINENO 10
    fi
	set -e 
else
    # DPF logic
    F_RUN_SQL "select value from  sysibmadm.dbcfg where name = 'logpath' and dbpartitionnum = 0"  "-xnq" 0 $DEBUG_MODE
    RC=$?; F_TEST_DB2_RETURNCODE $RC 0 $0 11 "Error querying sysibmadm.dbcfg for LOGPATH value at node 0." || die $LINENO 10
    tranlogpath=${SQLVAR1%\/*} # remove any trailing '/'
    tranlog_Mb=$(df -m $tranlogpath | tail -1 | awk '{print $2}') # filesystem size in Mb
    size_Mb=tranlog_Mb
    F_RUN_SQL "select value from  sysibmadm.dbcfg where name = 'logpath' and dbpartitionnum = 1"  "-xnq" 0 $DEBUG_MODE
    RC=$?; F_TEST_DB2_RETURNCODE $RC 0 $0 11 "Error querying sysibmadm.dbcfg for LOGPATH value at node 1." || die $LINENO 10
    tranlogpath=${SQLVAR1%\/*} # remove any trailing '/'
    tranlog_Mb=$(df -m $tranlogpath | tail -1 | awk '{print $2}') # filesystem size in Mb
    (( size_Mb += tranlog_Mb * num_data_parts ))
    F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'TRANLOG',DEFAULT,DEFAULT,${size_Mb})" "-xnq" 0 $DEBUG_MODE
    RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize tranlog size value, please review stdout." || die $LINENO 10
    F_RUN_SQL "select replace(value,'DISK:','') from  sysibmadm.dbcfg where name = 'logarchmeth1' and value like 'DISK:%' and dbpartitionnum = 0"  "-xnq" 1 $DEBUG_MODE
    RC=$?; F_TEST_DB2_RETURNCODE $RC 1 $0 11 "Error querying sysibmadm.dbcfg for LOGARCHMETH1 value at node 0." || die $LINENO 10
    if  (( RC == 0 ))
    then
        archlog_Mb=$(df -m ${SQLVAR1} | tail -1 | awk '{print $2}')
        size_Mb=archlog_Mb
        F_RUN_SQL "select replace(value,'DISK:','') from  sysibmadm.dbcfg where name = 'logarchmeth1' and value like 'DISK:%' and dbpartitionnum = 1"  "-xnq" 1 $DEBUG_MODE
        RC=$?; F_TEST_DB2_RETURNCODE $RC 1 $0 11 "Error querying sysibmadm.dbcfg for LOGARCHMETH1 value at node 1." || die $LINENO 10
        archlog_Mb=$(df -m ${SQLVAR1} | tail -1 | awk '{print $2}')
        (( size_Mb += archlog_Mb * num_data_parts ))
        F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) values(current timestamp,'ARCHLOG',DEFAULT,DEFAULT,${size_Mb})" "-xnq" 0 $DEBUG_MODE
        RC=$? ; F_TEST_DB2_RETURNCODE $RC 0 $0 10 "Insert error for dba.dbsize archlog size value, please review stdout." || die $LINENO 10
    fi
fi

# STEP 4: TABLESPACES
# (the next SQL call only uses multiply_alt to avoid shell metacharacter expansion of '*')
F_RUN_SQL "insert into dba.dbsize(when_snap,what_snap,dbpartition,tbspace,snap_val_Mb) select current timestamp, 'TABLESPACE', dbpartitionnum, substr(tbsp_name,1,18), multiply_alt(tbsp_used_pages,TBSP_PAGE_SIZE) / multiply_alt(1024,1024) from table (mon_get_tablespace('',-2)) as zzz where  tbsp_content_type in ('ANY','LARGE') and tbsp_using_auto_storage = 1 and tbsp_type = 'DMS'"  "-xnq" 0 $DEBUG_MODE
RC=$?; (( RC > 0 )) && F_MESSAGE "SQL runtime error, please review this job log." PLAIN I $DEBUG_MODE && die $LINENO 13

F_TERMINATE $DEBUG_MODE

# Restore stdout and stderr to terminal
F_RESET_OUTPUT
cat $LOG
exit $RC
