What is Database Maintenance in IBM DB2 ?
Database maintenance in DB2 involves regular tasks to keep the database fast and efficient. Over time, as data is added, deleted, or updated, the layout of data can become messy and slow down performance. To fix this, DB2 uses a process called REORG, which reorganizes the data to make it easier and faster to access. Other important tasks include RUNSTATS, which helps DB2 understand the data better for faster queries, and REORGCHK, which tells you when a REORG is needed. Together, these steps help keep the database running smoothly.
Tablespaces in DB2: SMS vs DMS
A tablespace in DB2 is like a storage area where your tables and indexes live. There are two main types:
- SMS (System-Managed Space) : the operating system manages this space and grows files as needed—easy to set up but less flexible
- DMS (Database-Managed Space): DB2 manages the space by creating fixed-sized containers ahead of time, which gives more control and usually better performance, especially for large databases.
Why isn’t database maintenance automatic ?
AUTO_REORG in DB2 is a feature that automatically reorganizes tables to keep them efficient. While it sounds convenient, many administrators turn it off because it can slow down the system or make parts of the database temporarily unavailable if it runs at the wrong time. Since reorgs use a lot of resources, it’s safer to run them manually during quiet hours. This gives more control and avoids unexpected slowdowns. Most teams prefer to schedule these tasks instead of letting DB2 run them automatically.
The Auto-Reorg feature is disabled by default when installing IBM DB2. Additionally, we have several DMS-type tablespace to store large data in Spectrum Control Database(TPCDB), which do not return freed space to the Filesystem once it is freed at the DB2 level.
Performing Spectrum Control Database (TCPDB) Maintenance
Since the DB2 reorg is not automatic, we often hear below problem statements for Spectrum Control:
• Modifying the Sample Data History retention from 2 weeks to 1 week did not change the Filesystem Utilization where DB2 is storing the data.
• Removing multiple storage devices from Spectrum Control did not affect Filesystem Utilization.
• Filesystem Capacity continues to grow despite having set History Retention.
• Storage Performance Exports runs slow in environments with large database size.
Spectrum Control includes custom DB2 maintenance scripts that are located in <SC_Install_Path>/data/server/tools/. More information here
Note: The DB2 maintenance scripts have been optimized in the SC 5.4.12.1 release. Therefore, we recommend upgrading Spectrum Control to version 5.4.12.1 or the latest available release.
Resolution:
1. Manually run the re-org on TPCDB tables to free the space at DB2 Level.
2. Alter the TPCDB in a Downtime Window to free up space in filesystem.
|
Step-By-Step Example:
This example is based on Spectrum Control running on Linux, but the steps are generally applicable to Windows after updating the installation path.
❗️ It is recommended to take Spectrum Control VM snapshot or db2 backup before executing the procedure.
1. In this setup, TPCDB database path in under /home and its current utilization is 27 G.
2. Lets cross check the Database path is in /home/db2inst1 at Db2 level
3. Lets verify the status of DB2 Auto-Reorg is by default OFF
4. Initiate the Re-Org process to reclaim space at the DB2 level. If you’re working with a large Spectrum Control environment, it’s recommended to schedule downtime—stop all Spectrum Control services, shut down DB2, and then run the re-org script to ensure optimal performance and avoid potential disruptions.
# cd <SC_Install_Path>/data/server/tools/
# runTPCDBMaintenance reorg
Note: Execute above commands using root user. For windows OS, execute runTPCDBMaintenance.bat reorg
5. Check the tablespace information using below command:
# db2pd -tablespaces -db TPCDB
As an example in below screenshot, notice FreePgs changed to 318800 for ID 6 (TPCTBSPPM) after the db2 reorg.
6. Check filesystem utilization using df-h .. Notice there are no utilization changes in /home. In fact it increased due to reorg which is known behavior. This is because the tablespace are of type DMS, the space is freed within db2 level but not at OS level.
7. Next step will be to reclaim the space back to filesystem. To do this, first stop all the SC services. Please refer here for steps to stop/start the SC services.
8. Here is the important step to Alter the identified tablespace in step 6. Example: TPCTBSPPM:
# db2 -v "ALTER TABLESPACE <TPCDB_TABLESPACE> LOWER HIGH WATER MARK"
# db2 -v "ALTER TABLESPACE <TPCDB_TABLESPACE> REDUCE MAX"
Repeat these for each tablespace you need to reclaim space back to filesystem
9. Space utilization reduced to 17G from 31G for /home filesystem.
10. Start the Spectrum Control Services.
|
If you have any questions or concerns, please feel free to contact IBM Support.