#!/bin/sh

# Syntax : 
#				  wDB2TblSpLog
#	Purpose:
#					To log all existing DB2 tablespaces' statistics (used/free pages) to a logging database called $LOG_DB_NAME.
# Version:
#					01.2.2008.1
# Writer :
#					Walker - WalkerNews.net
# Change :
#					01Feb08 v01.2.2008.0 - Initial code
#					22Feb08 v01.2.2008.1 - Record all tablespaces, including the logging database (WTBLSPST) tablespaces.	

source /home/walker/sqllib/db2profile

declare LOG_DB_NAME="WTBLSPST"
declare TGT_DB_NAME_L='WalkerNews.net'
declare TGT_DB_NAME_U='WalkerNews.net'
declare TBL_SP_NAME='WalkerNews.net'
declare VERS1='1'
declare VERS2='2'
declare VERS3='2008'
declare VERS4='0'
declare LOG_DATE='01-01-1970'					# Using DB2 'date(current timestamp)' function (not Linux date format) 
declare TMP1=.tmp1.$$
declare	TMP2=.tmp2.$$
declare DB_LIST=.tmp3.$$							# To store all DB names except the $DBNAME
declare DB_NAME='Walker'
declare -i LOG_DB_CHK=`db2 list database directory | grep $LOG_DB_NAME | grep -v grep | wc -l`
declare -i TBL_SP_ID=0
declare -i TOTAL_PAGE=0
declare -i TOTAL_USEABLE_PAGE=0
declare -i USED_PAGE=0
declare -i FREE_PAGE=0
declare -i TODAY_CNT=999999999
declare -i TOTAL_REC=0
declare -i TOTAL_TBL_SP=0

#-------------------------------------------------------------------------------------------------------------------
WriteStats()
{
	declare -i j=1

	db2 connect to $LOG_DB_NAME

	TOTAL_REC=`cat $TMP2 | wc -l`
	TOTAL_TBL_SP=`expr $TOTAL_REC / 6`   # Each Tablespace with 6 statistic records

	until [ $j -gt $TOTAL_TBL_SP ]
	do
  	a=$j
  	b=`expr $j + $TOTAL_TBL_SP \* 1`
   	c=`expr $j + $TOTAL_TBL_SP \* 2`
   	d=`expr $j + $TOTAL_TBL_SP \* 3`
   	e=`expr $j + $TOTAL_TBL_SP \* 4`
   	f=`expr $j + $TOTAL_TBL_SP \* 5`

    TBL_SP_ID=`sed -n "${a},${a}p" $TMP2`
    TBL_SP_NAME=`sed -n "${b},${b}p" $TMP2`
    TOTAL_PAGE=`sed -n "${c},${c}p" $TMP2`
    TOTAL_USEABLE_PAGE=`sed -n "${d},${d}p" $TMP2`
    USED_PAGE=`sed -n "${e},${e}p" $TMP2`
    FREE_PAGE=`sed -n "${f},${f}p" $TMP2`

		db2 +c "insert into TBL_SP_STAT (TGT_DB_NAME, TBL_SP_ID, TBL_SP_NAME, TOTAL_PAGE, TOTAL_USEABLE_PAGE, USED_PAGE, FREE_PAGE) values ( '$TGT_DB_NAME_U', $TBL_SP_ID, '$TBL_SP_NAME', $TOTAL_PAGE, $TOTAL_USEABLE_PAGE, $USED_PAGE, $FREE_PAGE )"
    j=`expr $j + 1`
	done

	db2 connect reset
}

#-------------------------------------------------------------------------------------------------------------------
QueryStats()
{
	db2 connect to $TGT_DB_NAME_L
	db2 list tablespaces show detail >$TMP1	
	db2 connect reset

	cat $TMP1 | sed -n '/Tablespace ID/p' | sed 's/ /^/g' | cut -c 41- > $TMP2
	cat $TMP1 | sed -n '/Name/p' | sed 's/ /^/g' | cut -c 41- >> $TMP2
	cat $TMP1 | sed -n '/Total pages/p' | sed 's/ /^/g' | cut -c 41- >> $TMP2
	cat $TMP1 | sed -n '/Useable pages/p' | sed 's/ /^/g' | cut -c 41- >> $TMP2
	cat $TMP1 | sed -n '/Used pages/p' | sed 's/ /^/g' | cut -c 41- >> $TMP2
	cat $TMP1 | sed -n '/Free pages/p' | sed 's/ /^/g' | cut -c 41- >> $TMP2

	sed 's/Not^applicable/-999999999/g' $TMP2 > $TMP1			# 'Not applicable' will be converted to dummy figure.
  cat $TMP1 > $TMP2																			# Keep tablespaces statistics
}

#-------------------------------------------------------------------------------------------------------------------
NoLog()
{
	db2 connect to $LOG_DB_NAME
	db2 +c "select * from TBL_SP_STAT where LOG_DATE = '$LOG_DATE'"
	db2 connect reset
	echo "********* TODAY LOG ALREADY EXIST!!! *********"	
}

#-------------------------------------------------------------------------------------------------------------------
CheckFlag()
{
	for DB_NAME in `cat $DB_LIST`
	do
		TGT_DB_NAME_L=`echo $DB_NAME | tr 'A-Z' 'a-z'`
		TGT_DB_NAME_U=`echo $DB_NAME | tr 'a-z' 'A-Z'`

		db2 connect to $LOG_DB_NAME
		db2 +c "select date(current timestamp) from TBL_SP_STAT" | head -4 | tail -1 > $TMP1

		LOG_DATE=`cat $TMP1`

		db2 +c "select count(*) from TBL_SP_STAT where LOG_DATE = '$LOG_DATE' AND TGT_DB_NAME = '$TGT_DB_NAME_U'" | awk '{if (NR == 4){print $1}}' >$TMP1
		db2 connect reset

		TODAY_CNT=`cat $TMP1`

		if [ $TODAY_CNT -eq 0 ]
		then
			QueryStats
			WriteStats		
		else
			NoLog	
		fi

	done
}

#-------------------------------------------------------------------------------------------------------------------
ListDBName()
{
	# Changed for v01.2.2008.1 - to include the logging database as well.
	# db2 list database directory |grep 'Database name' |grep -v $LOG_DB_NAME |grep -v grep |awk '{print $4}' >$DB_LIST
	db2 list database directory |grep 'Database name' |grep -v grep |awk '{print $4}' >$DB_LIST
}

#-------------------------------------------------------------------------------------------------------------------
Main()
{
	if [ $LOG_DB_CHK -eq 0 ]
	then
		db2 create database $LOG_DB_NAME 
		if [ $? -eq 0 ]
		then
			db2 connect to $LOG_DB_NAME
			db2 +c "create table TBL_SP_STAT (LOG_DATE date with default, LOG_TIME time with default, TGT_DB_NAME varchar(8), TBL_SP_ID smallint, TBL_SP_NAME varchar(15), TOTAL_PAGE integer, TOTAL_USEABLE_PAGE integer, USED_PAGE integer, FREE_PAGE integer)"
	db2 +c "insert into TBL_SP_STAT (TGT_DB_NAME, TBL_SP_ID, TBL_SP_NAME, TOTAL_PAGE, TOTAL_USEABLE_PAGE, USED_PAGE, FREE_PAGE) values ( 'WALKER', -32767, 'SCRIPT_VERS', $VERS1, $VERS2, $VERS3, $VERS4)"
			db2 connect reset
			echo "********* TABLES CREATED!!! *********"
			ListDBName
			CheckFlag
		else
			echo "********* TABLES CREATION FAILED!!! *********"
		fi
	else
			ListDBName
			CheckFlag
	fi
}

#-------------------------------------------------------------------------------------------------------------------
Main
rm -fr $TMP1 $TMP2 $DB_LIST