Walker News

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.

Topic - Howto, Linux   Search - CLP, Database, DB2, Howto, IBM, Linux, Redhat, RHEL, Shell Scripts, Sysadmin, Tablespaces, Trend Analysis

Similar Articles:
» DB2 SQL To Query Tablespace Free Pages Statistics
» Excel Trend Line To Predict DB2 Tablespaces Free Page
» How To Create An ISO Image Of Directory / Filesystem In Linux
» How To Setup IBM DB2 Control Center
» Why VIM Editor Better Than VI Editor
» How To Install RHEL4 In HP NetServer LH3000
» How To Fix DB2 SQL6048N Error Of DB2START

↑ Grab this Headline Animator

Navigation:
» HOME - WalkerNews.net
« PREV  - An Email + Thumbnail = Your Web Identity, Sir!
» NEXT  - DB2 SQL To Query Tablespace Free Pages Statistics