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 four phases(Short, build, replace and Recreate all indexes).In relapse phase copying the data from the original tablespace to a system tablespace, 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 tablespace container 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 | grep -i BALDAT
other way to identify the tablespace by using the “syscat.tablespace”
Restart the DB and keep the instance in quiesce mode (The QUIESCE utility establishes a quiesce point for a table space, partition, table space set, or list of table spaces and table space sets. A quiesce point is a point at which data is consistent across these objects.)
db2 quiesce instance INSTANCE_NAME immediate
run the offline reorg on respective table
db2 reorg table BALDAT
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
Above both commands run during downtime.
Now run the unquiesce command to release the DB for use.
db2 unquiesce instance INSTANCE_NAME
Once the above process get completed then we can proceed for reducing the tablespace, this is an uptime command and during this command, the system can be accessible. this feature will reduce the amount of space used by a tablespace and optimize database performance by freeing up space and reducing disk usage.
To reduce a tablespace in DB2 11.1, follow these steps:
Run the Tablespace Reduction command:
db2 alter tablespace tablespace_name reduce max
The above process will take time to complete depend on your database and tablespace size. This 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).
