Walker News

DB2 SQL To Query Tablespace Free Pages Statistics

I haven’t tried the DB2 version 9, but the version 8 offers DB2 CLP command to query or list the tablespaces statistics (particularly the used pages and free pages).

While this CLP command is simple and straightforward, the tablespaces statistics is not output in a tabulation or CSV format. To certain extent, the output is not friendly for importing to MS Excel or spreadsheet for trend analysis.
With this in mind, I tried to search for a DB2 SQL statement to query the database system tables for tablespace related figures. However, there is not one simple SQL statement I could find.

Thus, I wrote a Linux shell scripts for this purpose, and run it under cronjob on daily basis. The shell scripts, I called it wDB2TblSpLog, is coded for these tasks:
  • Create a logging database with a simple table, if it’s not exists during execution,
  • Execute db2 list tablespaces show detail for existing databases govern by the DB2 instance account (the scripts will have to run under the same account, otherwise use a sudo command),
  • Extract some of the important figures (e.g. used pages, free pages, etc) from the CLP output and insert them into a logging database with one simple table.

The shell scripts will not insert tablespace statistics to the table, if there are records found on the calendar day when the script is executed. So, the disk space overhead and difficulty of retrieving data is minimized.

As soon as the tables created with records, you could run a simple SQL SELECT statements to query the tablespace statistics into a tabulated form (which is easier to import into Excel for trend analysis), e.g.
db2 +c "select * from TBL_SP_STAT"

db2 +c "select * from TBL_SP_STAT where TGT_DB_NAME = 'WALKERDB'"

db2 +c "select * from TBL_SP_STAT where TGT_DB_NAME = 'WALKERDB' and TBL_SP_NAME = 'WNTBL1'"

db2 +c "select * from TBL_SP_STAT where TGT_DB_NAME = 'WALKERDB' and TBL_SP_NAME = 'WNTBL1' and LOG_DATE > '01/01/2008' AND LOG_DATE < '01/01/2009'"

The version 2 of wDB2TblSpLog is tweaked to include tablespaces statistics of the logging database itself as well. Click here to download or copy the scripts code below (Please be reminded to change the path of db2profile to your environment):

#!/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


Custom Search

  1. Dummy Guide: Introduce Some Simple MySQL Commands – Walker News 05-07-08@23:46

    […] is one of the relational database management systems that released to end user under the GNU GPL license. Since the first release that happened on 1995, […]

  2. Mario Roberto 15-04-10@01:16

    Excelent!!!!

    Thanks a lot.

2017  •  Privacy Policy