IBM FlashSystem

IBM FlashSystem

Find answers and share expertise on IBM FlashSystem


#Storage
 View Only

DB2 Maintenance Best Practices to Optimise IBM Spectrum Control

By Jaideep Singh Chauhan posted Tue May 06, 2025 01:16 AM

  
IBM Spectrum Control is a comprehensive on-prem storage monitoring and reporting solution that provides end-to-end visibility, historical capacity & performance monitoring across different components in your SAN Environment such as Storage, SAN Switch, Servers.

Spectrum Control allows our customers the flexibility to set the History Retention in order to retain performance and capacity data over extended periods, enabling long-term trend analysis and capacity planning. By storing historical metrics, administrators can identify usage patterns, forecast growth, and proactively manage infrastructure before issues arise.

(Spectrum Control High Level Architecture)

Since telemetry data is stored in an IBM Db2 database, proper administration of the Db2 environment is essential to ensure the reliability and performance of Spectrum Control. In this blog, I’ll walk you through the key concepts and detailed steps involved.

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.

Note: All the db2 commands in below steps must be ran using db2inst1 user in Unix/AIX. For windows, ensure those commands are ran in db2 command window-administrator.

❗️ 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. Connect to TPCDB using command "db2 connect to tpcdb" then execute below:

 

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, and then run the re-org script to ensure optimal performance and avoid potential disruptions. Db2 must be up and running.
    # 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.

4 comments
65 views

Permalink

https://community.ibm.com/community/user/blogs/jaideep-singh-chauhan/2025/05/01/maintaining-db2-database-tpcdb-for-ibm-spectrum-co

Comments

Jaideep Singh Chauhan
yesterday

Below changes are done in this blog body:

  • db2 should not be stopped in step 4. only SC services needs to be stopped that too in large environments.
  • added a note to run all the db2 commands using db2 user i.e. db2inst1 and for windows use the db2 command window-administrator
  • updated step 3

Bacil123
7 days ago

Hi.

Yes, the filesystem space gets recovered and the procedure is all right,  and only few changes are needed. But anyone working a bit on this tool can figure it out easily also.

Also, this command doesn't work straightaway.  Need to connect to TPCDB first.

[root@spectrum control tools]# su - db2inst1
[db2inst1@spectrum control ~]$ db2 get dbm cfg | grep -i dftd
 Default database path                       (DFTDBPATH) = /home/db2inst1
[db2inst1@spectrum control ~]$ db2 get db cfg for TPCDB show detail | grep -i ORG
No Output
[db2inst1@spectrum control ~]$ db2 get db cfg for TPCDB show detail | grep -i org
No Output
So first connect to TPCDB, and then above command works fine.
[db2inst1@spectrum control ~]$ db2 connect to TPCDB
 
   Database Connection Information
 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TPCDB
 
[db2inst1@spectrum control ~]$ db2 get db cfg for TPCDB show detail | grep -i org
     Automatic reorganization               (AUTO_REORG) = OFF                        OFF
 Default table organization              (DFT_TABLE_ORG) = ROW                        ROW

Thanks.

Jaideep Singh Chauhan
7 days ago

@Bacil123

Thnks for sharing the feedback. Let me cross verify and also add instructions on which commands needs to be run using db2 user..
For time being, you can run the maintenance script without shutting down the SC or DB2 services. If its big env, only shutdown the SC services.. Let DB2 running. 

I am bit occupied these days ;) .. However, I will try to update this blog hopefully by Sunday..

Bacil123
7 days ago

Hello.  Great article and a very excellent tips given for DB2 maintenance. However, it appears there are some inaccuracies in it. For example, the article says stop DB2 and all the Spectrum Control services first.  But if we do that, it throws the error:

[root@spectrum_control tools]# ./runTPCDBMaintenance reorg
2025-10-29 12:05:12.981-05:00 Starting TPC database maintenance.
2025-10-29 12:05:13.277-05:00 Failed to connect to database

And, if keep everything running, the command works fine.

Also, it sometime gives this error as well:

[db2inst1@spectrum control ~]$ db2 -v "ALTER TABLESPACE CTBSPPM LOWER HIGH WATER MARK"
ALTER TABLESPACE CTBSPPM LOWER HIGH WATER MARK
DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

So, one needs to manually run first:

 db2 connect to TPCDB

And then run the above command again.  Also, these few commands doesn't run with root. They due to some reasons only run via db2inst1 user.

Thanks