As databases grow in size, it’s important to periodically reorganize and reduce the tablespaces to maintain performance and optimize storage. As an SAP BASIS consultant, Housekeeping/Cleanup is the essential part of his role which help SAP system to run smoothly and also increase the overall system performance The are some standard housekeeping jobs which you can schedule via SM36 however some table cleanup still requires which doesn’t come under standard procedure. That tables are which are related to Application Logs, SAP office documents, table changes record data etc. The cleanup of these table requires extra attention and with proper handling/procedure.

Let’s take an example of BALDAT(Application Logs) transparent table which is also associated with BALHDR(Header Table)

The first step is to get a approved retention period to perform any cleanup/housekeeping activity for your business system. This period value should be approved by your business and comes under SOX policy. let’s take an example that company need data for last 2 years(730 days) for current date.

SAP provide an standard job (SBAL_DELETE) to cleanup the application logs which eventually decrease the count from BALHDR and BALDAT table both. Remember one thing while cleanup that it’s not mandatory that 1 header record in BALHDR table is associated to 1 record in BALDAT table. There must be 1 record(BALHDR) to many records(BALDAT) exists. This is the reason that the count and size of both tables are different and it’s a huge difference.

After running the cleanup job via SBAL_DELETE for keeping the retention period of 730 days, you checked that table size is still same even though there is an feature of online REORG and RUNSTATS is already enabled in DB2. Now what next –

This requires an offline reorg of table which need a SAP system downtime. This feature is used to improve database performance by reorganizing tables, indexes, and other database objects. The Offline Reorg process involves copying the data from the original tablespace to a new one, which is then optimized for performance. This process can be time-consuming, especially for large databases but here is an catch , if you want to run offline reorg of any table suppose the current size of the table is 500GB and then there should be similar space available in Filesystem as this process is going to copy tablespace to temporary location.

To run an Offline Reorg in DB2 11.1, follow these steps:

Take Approval & Shutdown your SAP system.

Identify the tablespace you want to reorg (we need to check the BALDAT comes under which tablespace)

db2 list tablespaces show detail

Put the tablespace into “backup pending” state:

db2 “backup db sample online to /dev/null”
db2 “set tablespace containers for tablespace_name enable offline”

Run the Offline Reorg:

db2 reorg table BALDAT

Bring the tablespace back online:

db2 “set tablespace containers for tablespace_name disable offline”

The above process will take long time to complete (depend on size). once REORG activity get completed then trigger the RUNSTATS.

Runstats is a DB2 feature that updates statistics on tables and indexes. This feature is used to optimize the performance of SQL statements by providing the optimizer with accurate statistics about the data in the tables and indexes.

To run Runstats in DB2 11.1, follow these steps:

db2 runstats on table BALDAT with distribution and detailed indexes all

Once the above process get completed then we can proceed for reducing the tablespace, this feature will reduce the amount of space used by a tablespace and optimize database performance by freeing up space and reducing disk usage. The Tablespace Reduction process involves moving the data to a new tablespace that is optimized for performance and then dropping the old tablespace.

To reduce a tablespace in DB2 11.1, follow these steps:

As you have already verified the tablespace in first step now you need to set the tablespace in “backup pending” state –

db2 “backup db sample online to /dev/null”
db2 “set tablespace containers for tablespace_name enable offline”

Run the Tablespace Reduction command:

db2 alter tablespace tablespace_name reduce max

Bring the tablespace back online:

db2 “set tablespace containers for tablespace_name disable offline”

The above process will increase and boost your overall system performance and also release the space at OS level which is visible on sapdata mount points (df -h).

There are some PROS and CONS as well for the above activities which also need to consider before performing –

Offline Reorg –

Pros:
Improves performance by removing fragmentation in the table.
Improves storage efficiency by reclaiming unused space.
Can be run offline, without affecting the availability of the database.

Cons:
Requires enough free space in the tablespace to create a new copy of the table.
Can take a long time to complete, depending on the size of the table.

Runstats –

Pros:
Improves query performance by providing accurate statistics for the query optimizer to use.
Can be run online, without affecting the availability of the database.

Cons:
Can take a long time to complete, depending on the size of the table and its indexes.

Table Reduction –

Pros:
Optimizes storage by reducing the amount of disk space used by the database.
Can be run online, without affecting the availability of the database.

Cons:
Can take a long time to complete, depending on the size of the tablespace.