Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
29 November 2008
Introduction
Have you ever run the db2 “call get_dbsize_info(?,?,?,0)” command to get the size of your DB2 LUW database and noticed that the size is much smaller than the space used on your UNIX file systems? Do you need to monitor database growth over time to help plan for future storage requirements?
This article describes a script that will calculate the size of a DB2 LUW database on a UNIX server using UNIX commands that will give you a more accurate measure of the space used by the database. This script also stores this information in an operational table that you may use as part of your DBA Monitoring Dash Board to help you monitor and manage space in relation to growth trends.
Pros and cons
The advantage of monitoring database sizes over time is that you can see trends and show evidence to make the case for future storage acquisitions. This information can also be helpful in showing you when significant spikes in data growth appear and you can confirm that these are expected and not due to operational problems with application jobs or programs.
A disadvantage to this approach is that it does not include the size of transaction log files on disk, archive log files if you keep them on disk or temporary tablespaces. If you wish to include that information in the database size you will have to enhance the script to do this.
Description
The DB2 list tablespace command and list tablespace container command can be used to obtain the path or file for tablespace containers in a database. The UNIX list command (ls) can be used to obtain the size of the tablespace container paths and files and accumulated by partition and summarized by database. This script captures partition and database size information and stores it in a table. If the script is run daily then the size information can be seen over time.
Having this partition and database size information available in a table named OPERATIONS.DATABASE_INFORMATION can show database size information for one day or over time.
In the two examples shown below, the call to the get_dbsize_info stored procedure returns a database size of roughly 1.36 terabytes and the db2extract_database_size.ksh script returns a database size of roughly 3.09 terabytes. The difference between these two approaches is significant and when it comes to terabytes of storage it is important to be accurate in your measurements for storage planning purposes. Also, if you notice significant size differences in the database partition sizes then this could indicate an out of balance condition that you may need to correct at the tablespace or table level in the database.
db2 "call get_dbsize_info(?,?,?,0)" Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2008-11-26-09.11.44.205752 Parameter Name : DATABASESIZE Parameter Value : 1367017938944 Parameter Name : DATABASECAPACITY Parameter Value : -1 Return Status = 0 db2extract_database_size_information.ksh prodin01 proddb01 Server Instance Database Datetime Size Partition prodsvr prodin01 PRODDB01 20081126091344 2186406912 0 prodsvr prodin01 PRODDB01 20081126091344 784969437184 1 prodsvr prodin01 PRODDB01 20081126091344 781603337216 2 prodsvr prodin01 PRODDB01 20081126091344 780613206016 3 prodsvr prodin01 PRODDB01 20081126091344 750529480704 4 prodsvr prodin01 PRODDB01 20081126091344 3099901868032
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. The extract script must be run under the DB2 instance id or an id that has the correct UNIX permissions to be able to run the UNIX list command (ls) against the database tablespace container paths and files.
Before you get started, a couple of words of caution:
- Practice running the script and verify the results.
- Create the table before running the script. The DDL for this is provided below.
- Create tablespaces for the table and it’s index.
- Make sure the id you use to run the script has privileges to run the UNIX list (ls) command against the tablespace container paths and files and to insert rows in the OPERATIONS.DATABASE_INFORMATION table. Running this under a DB2 instance id would be best.
- Schedule this script to run in cron or some other scheduling system daily under a DB2 instance id and you will have a history of the database size information for you to use over time.
- WARNING: If you keep store other files on file systems in paths where DB2 keeps tablespace container files, these could be picked up and counted as part of your database size.
- WARNING: If IBM changes the output format of the DB2 list tablespaces show detail or DB2 list tablespace containers for id commands then this script may have to be modified to work with the new format.
CRONTAB schedule example
00 22 * * * /db2util/scripts/db2extract_database_size_information.ksh prodin02 proddb02 >/dev/null 2>&1
The script
#!/bin/ksh
#----------------------------------------------------------------------t
# Program : db2extract_database_size_information.ksh
# Description : This script runs the DB2 list tablespace command
# : to identify the tablespace id's in a partition
# : and then runs the db2 list tablespace containers
# : for each id that is not a temporary tablespace
# : and extracts the name for each container path
# : or file and then runs a UNIX list (ls) command
# : and accumulates the size of the files to
# : calculate the size of the partition and
# : ultimately the size of the database.
# Author : Mark Mulligan
# Date : 04/07/2005
# Input Arguments : 1st Parameter = (Required) Instance.
# : 2nd Parameter = (Required) Database.
# Output : None.
# Required Files : None.
# Maintenance :
#-----------------------------------------------------------------------
ScriptVer='04/07/2005'
ScriptName='db2extract_database_size_information'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)"
echo "Parm 2 Database (required)"
echo 'Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports'
echo ""
return
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then echo ""
echo "Parm 2 Database (required)"
echo 'Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports'
echo ""
return
else Database=`echo $2 | tr '[a-z]' '[A-Z]'`
fi
if [ "$3" = "" ]
then OutputDir='/dbawork/reports'
else OutputDir=$3
fi
#.${HOME)/sqllib/db2profile
. ${HOME}/.${Server}_${Instance}
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Database}_${Datetime}
OutputFile=${FileName}.txt
NumberOfNodes=`cat $INSTHOME/sqllib/db2nodes.cfg | wc -l`
TotalFileSize=0
PartitionNodeSize=0
LastNode=0
echo "Server Instance Database Datetime Size Partition" | tee $OutputFile
cat $INSTHOME/sqllib/db2nodes.cfg | while read node1 server node2
do
export DB2NODE=$node1
db2 terminate >/dev/null 2>/dev/null
db2 connect to $Database >/dev/null 2>/dev/null
db2 list tablespaces | grep "Tablespace ID" | while read tablespace ID equals id
do
db2 -x "select tbspaceid,datatype from syscat.tablespaces where tbspaceid=$id and datatype!='T'" >/dev/null 2>&1
if [ "$?" = "0" ]
then
db2 list tablespace containers for $id | grep Name | while read name equals path
do
echo ${path} >> $OutputFile.detail
ls -l ${path} | while read permissions number id group size month day hourminute filename
do
if [ "$permissions" = "total" ]
then :
else
# if [ "$path" = "$filename" ]
# then echo "$filename $size" >> $OutputFile.detail
# else echo "$path $filename $size" >> $OutputFile.detail
# fi
if [ "$node1" = "$LastNode" ]
then :
else
echo "$Server $Instance $Database $Datetime $PartitionNodeSize $LastNode" | tee -a $OutputFile
db2 -v "insert into operations.database_information values \
('${Server}','${Instance}','${Database}','${LastNode}', \
${PartitionNodeSize},current timestamp)" \
>/dev/null 2>&1
LastNode=$node1
PartitionNodeSize=0
fi
((PartitionNodeSize=PartitionNodeSize+size))
((TotalFileSize=TotalFileSize+size))
fi
done
done
fi
done
done
echo "$Server $Instance $Database $Datetime $PartitionNodeSize $LastNode" | tee -a $OutputFile
db2 -v "insert into operations.database_information values \
('${Server}','${Instance}','${Database}','${LastNode}',${PartitionNodeSize},current timestamp)" \
>/dev/null 2>&1
echo "$Server $Instance $Database $Datetime $TotalFileSize" | tee -a $OutputFile
db2 -v "insert into operations.database_information values ('${Server}','${Instance}','${Database}',' \
',${TotalFileSize},current timestamp)" 2>/dev/null 2>&1
echo "Output located in" | tee -a $OutputFile
ls $OutputFile* | tee -a $OutputFile
The Table
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DATABASE_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DATABASE_INFORMATION" (
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ,
"DBPARTITIONNUM" CHAR(3) ,
"SIZE" BIGINT NOT NULL WITH DEFAULT ,
"SIZE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT )
DISTRIBUTE BY HASH("SIZE_TIMESTAMP")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The first two parameters for the script are required so that the common reusable DBA script will know what DB2 instance and database to use. The third parameter is optional.
db2extract_database_size_information.ksh Parm 1 Instance (required) Parm 2 Database (required) Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports
When you run the script in foreground and pass it a DB2 instance id and database, you should see output displayed that is similar to the example shown below.
db2extract_database_size_information.ksh prodin01 proddb01 Server Instance Database Datetime Size Partition prodsvr prodin01 PRODDB01 20081126091344 2186406912 0 prodsvr prodin01 PRODDB01 20081126091344 784969437184 1 prodsvr prodin01 PRODDB01 20081126091344 781603337216 2 prodsvr prodin01 PRODDB01 20081126091344 780613206016 3 prodsvr prodin01 PRODDB01 20081126091344 750529480704 4 prodsvr prodin01 PRODDB01 20081126091344 3099901868032 Output located in /dbawork/reports/db2extract_database_size_information_prodsvr_prodin02_PRODDB02_20081126125715.txt /dbawork/reports/db2extract_database_size_information_prodsvr_prodin02_PRODDB02_20081126125715.txt.detail
Select Examples
The output created by this script is stored in a DB2 table. You may develop SQL or a script that runs different types of select SQL statements that will help you use this information to help you monitor database size changes over time. The first select statement shows you the size of all partitions in the database and the total database size yesterday. The second select statement shows you the database sizes only over time. This information could be presented in a graph to show the database size growth over time.
select database,dbpartitionnum,size,date(size_timestamp) date from operations.database_information where date(size_timestamp) = current date - 1 day order by database,size_timestamp DATABASE DBPARTITIONNUM SIZE DATE -------- -------------- -------------------- ---------- PRODDB02 0 2186406912 11/25/2008 PRODDB02 1 784969437184 11/25/2008 PRODDB02 2 781603337216 11/25/2008 PRODDB02 3 780613206016 11/25/2008 PRODDB02 4 750529480704 11/25/2008 PRODDB02 3099901868032 11/25/2008 select database,dbpartitionnum,size,date(size_timestamp) date from operations.database_information where dbpartitionnum = ' ' order by database,size_timestamp DATABASE DBPARTITIONNUM SIZE DATE -------- -------------- -------------------- ---------- PRODDB02 3095516920832 10/26/2008 PRODDB02 3095545461760 10/27/2008 PRODDB02 3095662148608 10/28/2008 PRODDB02 3095763762176 10/29/2008 PRODDB02 3095895849984 10/30/2008 PRODDB02 3096048811008 10/31/2008 PRODDB02 3096162221056 11/01/2008 PRODDB02 3096359111680 11/03/2008 PRODDB02 3096465312768 11/03/2008 PRODDB02 3096645143552 11/04/2008 PRODDB02 3096794696704 11/05/2008 PRODDB02 3096930421760 11/06/2008 PRODDB02 3097016273920 11/07/2008 PRODDB02 3097127816192 11/08/2008 PRODDB02 3097199893504 11/09/2008 PRODDB02 3097231940608 11/10/2008 PRODDB02 3097399696384 11/11/2008 PRODDB02 3097587784704 11/12/2008 PRODDB02 3097786883072 11/13/2008 PRODDB02 3097978125312 11/14/2008 PRODDB02 3098132265984 11/15/2008 PRODDB02 3098199473152 11/16/2008 PRODDB02 3098299218944 11/17/2008 PRODDB02 3098505722880 11/18/2008 PRODDB02 3098718649344 11/19/2008 PRODDB02 3098936228864 11/20/2008 PRODDB02 3099159116800 11/21/2008 PRODDB02 3099320859648 11/22/2008 PRODDB02 3099425651712 11/23/2008 PRODDB02 3099529197568 11/24/2008 PRODDB02 3099901868032 11/25/2008 PRODDB02 3099901868032 11/26/2008
Conclusion
If you do not have sophisticated database monitoring software that captures information about DB2 database sizes, you can capture this yourself from the DB2 database and UNIX operating system by using this script. This valuable source of information can be used by a DBA as evidence to show database size changes over time and used to help justify new storage requests. If you develop a dash board for application teams, users and management that gives them visibility into this information then they can monitor database size history over time and gain a better understanding about the growth of information stored and used by the company.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to try to answer your questions.
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.