Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Most of the cases we need to monitor the space of the FileSystem while data is growing in the database.
And if there are any delete operations performed on a table then the table must be reorged and space should be reclaimed.
It this case we need to reduce the free pages that are there in a tablespace.
So to automate that we can use the below script.
Here is the script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | #!/bin/bash # Establishing database connection db2 "connect to itgdb" # Gathering the tablespaces other than temporary and placing them in a file db2 list tablespaces show detail | grep -i -A2 name | tac | sed '/Temporary/I,+2 d' | tac | grep -i name | awk '{print $3}' > /tmp/TablespaceList.out # Using for loop to get the tablespace name one by one for i in `cat /tmp/TablespaceList.out` do # Gathering free pages count for each tablespace db2 list tablespaces show detail | grep -i -A9 $i | grep -i 'free pages' | awk '{print $4}' > /tmp/FreePages.out FreePages=`cat /tmp/FreePages.out` # Comparing whether the free pages are more than 1000 or not if [ $FreePages -gt 1000 ] then # If the free pages are more than 1000 then reducing them db2 "alter tablespace $i reduce max" fi done |
This script is fine . I would suggest couple of additions
1. Check to ensure it runs at specific time so that There should be minimum sufficient pages to ensure insert /load operation should not be affected due to less number of free pages (this happens in very rare cases but terminate Load/insert)
2. This script takes care of only Automatic tablespaces . It Can be amended to take care of those tablespaces which has created in 9.7 as DMS but not automatic.