Walker News

How To Log DB2 Tablespaces Free Pages Statistics

The DB2 CLP allows user to execute database manager command to query tablespaces related statistics, e.g. total pages, useable pages, used pages, free pages, etc.

As a Sysadmin or particularly the DB2 administrator, you might aware the important of monitoring DB2 tablespaces used pages and/or free pages before the server running out of disk space.
How to monitor DB2 Tablespaces Free Pages and/or Used Pages Statistics?

A less favorable way will be manually executing this DB2 CLP command and record the statistics when you’re working:

db2 list tablespaces show detail

A better way will be writing a Linux Shell Scripts (for those DB2 servers running on RHEL) and setup a cronjob to automatically logging the used/free pages of all tablespaces into a simple table (on a daily basis).

The Linux shell scripts, wDB2TblSpLog

After keeping reasonable records, then you could run a SELECT SQL to list the daily tablespaces statistics into a tabulated format, import it to Microsoft Excel (or similar spreadsheet) and generate a chart with trend line to analysis or predict:
  • The growth rate of database size
    • For capacity planning (hard disk size) – i.e. how many free pages left or when will the tablespaces running out of free pages, when will you have to add more hard disk for a SMS database (system managed space) or to add new tablespace containers for a DMS database (Database Managed Space)
  • The peak period of database growth
    • For load monitoring – i.e. when will the database receive most hits

This Linux Shell Scripts is used to automatically log DB2 tablespaces free and used pages statistics into logging a database called WTBLSPST:

The scripts needs to run under the DB2 administrative account
  • Scan all existing databases and the respective tablespaces to record the total pages, total useable pages, used pages, free pages, tablespace ID, tablespace name and the database name that own the tablespace.
  • Automatically create a simple logging database and table to keep the statistics (if they’re not exists during execution).
  • The scripts will only record those statistics once a day – no duplication as to simplify the SELECT SQL statement for trend analysis.

The script doesn’t require parameters or switches, nor does it require complicated setup or dependencies to run. Just download the scripts, change the path of db2profile to your environment, and run it under the DB2 administrative account in Linux environment will do.

So, the cronjob will be simple as this (suppose the Shell Scripts is kept in a DB2 administrative account called walker):

0 5 * * * /home/walker/wDB2TblSpLog 1>>/tmp/wdb2tblsplog.log 2>&1

This typical style of cronjob will redirect the standard output (STDOUT) and standard error (STDERR) to a log file for logging/debugging (i.e. the /tmp/wdb2tblsplog.log).

A simple SQL statement to query all the DB2 tablespaces used pages and free pages statistics will be like this:
db2 connect to wtblspst
db2 +c "select * from tbl_sp_stat"
db2 connect reset

You may easily filter the SQL output to particular date, database, and/or tablespace (by ID or by Name).

Is it simple enough? Please comment/feedback for improvement, especially the SED editor related coding. You may click here to download wDB2TblSpLog.

Custom Search

  1. Excel Trend Line To Predict DB2 Tablespaces Free Page – Walker News 01-03-08@04:14

    […] Linux platform, you may schedule a cronjob to automatically record the tablespaces statistics (e.g. How to log DB2 tablespaces free pages statistics). After some times, extract the date / free page figures in tabulation format and import it to […]

  2. How To Setup Non-Interactive SSH Login – Walker News 07-06-08@00:14

    […] But, how could I handle this interactive authentication process in Windows batch files or Linux shell scripts that scheduled to trigger file transfer between networked hosts on a daily basis? Apparently, in […]

2014  •  Privacy Policy