Optimizing SAP DB2 11.1 Performance: Running Offline Reorg, Runstats, and Reducing Tablespace
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)
Update – Mar 28, 2023
We have deleted around 170 Million of records from BALDAT table and results are amazing by performing above steps –
Table Size – 480 GB (Before Reorg and Reduce)
Table Size – 68 GB (After Reorg and Reduce)
Reduce Help help us to reclaim the space at OS level and 400+ GB space has been added to /sapdata mount points back.
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 build 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
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).
There are some PROS and CONS as well for the above activities which also need to consider before performing –
Offline Reorg –
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.
Requires enough free space in the tablespace container path to create a new copy of the table.
Can take a long time to complete, depending on the size of the table.
Improves query performance by providing accurate statistics for the query optimizer to use.
Can be run online, without affecting the availability of the database.
Can take a long time to complete, depending on the size of the table and its indexes.
Table Reduction –
Optimizes storage by reducing the amount of disk space used by the database.
Can be run online, without affecting the availability of the database.
Can take a long time to complete, depending on the size of the tablespace.
Thanks to Gaurav Kumar for providing his inputs.
for the standard Db2 row based tables space is not automatically given back to the tablespace after a mass deletion ( or data archival ). However Db2 can reuse space that has been freed up in the table when new data is inserted.
If you want Db2 to automatically free up space in the table object after a mass deletion ( or data archival ) you need to use another table type. It is possible to convert such table candidates to Insert Time Clustered (ITC) tables (New insert time clustering tables - IBM Documentation ).
SAP recommends to turn on Automatic Reorg on tables for index cleanup or space reclaim ( Note 975352). The reason why SAP does not recommend to configure Automatic Reorg for other purposes is that those reorg types may cause lock wait situations that may lead to SQL0911 errors that potentially affect SAP application.
If you need to reorg a table to reclaim space ( either in the data or in the LOB object ) SAP recommends to use the DB6CONV tool which is documented in note 2091519. Internally this tool uses the Db2 ADMIN_MOVE_TABLE procedure which allows to reorganize a table online.
The DB6CONV tool can also be used for other purposes like recompression or moving a table to a different tablespace.
Since DB6CONV can be run online I do not see why a system downtime is needed.
Thank you for your reply.
I want to know is SAP support ITC tables and we can convert the large growth tables to ITC without impacting the system performance?
The whole story has been started with Access Control Risk Analysis jobs (GRAC tables in SAP GRC) where we need to decresae the table size after cleaning/deleting the old data. Table GRACSODREPDATA is 1 TB in our GRC system and still not decreased after cleaning and keeping the data for last 2 years. Data type is DBCLOB.
According to SAP Note 1580877, you need to export the data contents, delete the table and create the new table to reduce the DB size. I don't believe this is right solution, there should always anothe. However we tried doing offline/online or automatic reog but that also doesn't help for DBCLOB table size reduction. I can't see any DB6CONV utility mentioned anywhere in the note(1580877) as well.
Is DB6CONV will also help to reduce the table size for data type DBCLOB or I am looking at the wrong place?
yes, we support ITC tables and normal tables can be converted to ITC using DB6CONV.
As a default DB6CONV suggests only tables as ITC candidates that have an archiving object defined on them. However it you regularly perform such cleanup tasks on this GRACSODREPDATA tables, you can also convert this table to ITC.
Regular tables and ITC tables only show a minor difference in performance characteristics. At create time ITC tables get an implicit additional MDC index that is basically a list of extents. This allows to release empty extents to the tablespace without any data movement. The cleanup is done via automatic reorg. Regular tables can not return empty extents to the tablespace without a (data moving) reorg.
Thank you Frank.
Definitely will try this.