Monitoring tablespace free space with DB2 and extending tablespace containers


Monitoring tablespace free space with DB2 and extending tablespace containers
Prev	Using Veritas Quick I/O	Next

DB2 does not automatically make use of extended DMS files. When tablespace space needs to be extended, a number of DB2 commands must be run. Unlike raw devices, a Database Administrator can easily extend Quick I/O files online. Using this method, a Database Administrator can monitor the free space available in the DB2 tablespaces and use the qiomkfile command to grow the Quick I/O files online as needed (typically when the file is about 80 to 90% full). This method does not require you to lock out unused disk space for Quick I/O files. The free space on the file system is available for use by other applications.

Before extending tablespaces, make sure the following conditions have been met:

Prerequisites

Log on as the DB2 instance owner.

Usage notes

Monitor the free space available in the Quick I/O file, and grow the file as necessary with the qiomkfile command.
A Database Administrator can grow underlying VxFS file systems online (provided the underlying disk or volume can be extended) using the fsadm command. See the fsadm (1M) manual page for more information.
It is strongly recommended that if a DB2 tablespace is running out of space, that all containers are grown by the same amount. It is possible to add extra containers to the tablespace, but this results in DB2 performing a relayout of the data to balance usage across all available containers in the tablespace.

Also refer to the DB2 Administration Guide section on Managing Storage and the DB2 SQL Reference Guide for information on the ALTER TABLESPACE command.

To monitor the free space available in a DB2 tablespace

Use the following DB2 commands:

$ db2 connect to database
$ db2 list tablespaces show detail
$ db2 terminate

To extend a Quick I/O file using qiomkfile

Use the qiomkfile command to extend the Quick I/O file (if the container is running low on free blocks):
```
$ /opt/VRTS/bin/qiomkfile -e extend_amount filename
```

To extend a DB2 tablespace by a fixed amount

Use the following DB2 commands:

$ db2 connect to database

$ db2 alter tablespace tablespace-name extend (ALL amount)

$ db2 terminate

This example shows how to monitor the free space on the tablespaces in database PROD:

$ db2 connect to PROD

$ db2 list tablespaces show detail

$ db2 terminate

This example shows how to extend the three DB2 containers owned by tablespace EMP by 500MB using the qiomkfile command:

$ /opt/VRTS/bin/qiomkfile -e 500M tbsEMP_cont001

$ /opt/VRTS/bin/qiomkfile -e 500M tbsEMP_cont002

$ /opt/VRTS/bin/qiomkfile -e 500M tbsEMP_cont003

This example shows how to notify DB2 that all containers in tablespace EMP have grown by 500MB:

$ db2 connect to PROD

$ db2 alter tablespace EMP extend (ALL 500M)

$ db2 terminate

This example shows how to verify the newly allocated space on the tablespace EMP in database PROD:

$ db2 connect to PROD

$ db2 list tablespaces show detail

$ db2 terminate

Prev	Up	Next
Extending a Quick I/O file	Home	Recreating Quick I/O files after restoring a database