SAP on IBM i: Truncating Tables in the SAP Business Warehouse Application

To maintain a reasonable size of your tables in the SAP Business Warehouse (BW) application, you want to delete old data from time to time. Technically, this is implemented through an SQL statement DELETE. When you delete only a subset of the rows in a table, the DELETE operation is deleting one row at a time. For a large number of rows, it can take a significant amount of time to process all rows of the request. In the special case that you delete all rows from a table, for example when you compress InfoCubes or if you delete all data from an InfoCube, the database has a faster way of processing the request: Instead of processing the request row by row, it clears the data space at once (comparable to the CL command CLRPFM), and that operation is much faster. This special operation mode is called Fast Delete. The downside is that the database requires an exclusive lock on the table to perform the fast delete. If other processes still use the table, the delete operation cannot obtain the exclusive lock and the database performs a row-by-row deletion as fallback. If the number of rows to be deleted is very large, it can take a significant amount of time to complete this operation.

Based on the assumption that no one should be using a table that you are about to truncate, SAP BW tries to get an exclusive lock on a large table before executing the DELETE statement. If it cannot get the exclusive lock, the reaction depends on the operation mode: When running in a dialog process, you get a popup window where you can choose if you want to continue with row-by-row deletion or abort the operation and check who is preventing the exclusive lock. When running as background job, the truncation is aborted. RSADMIN parameter DBMS_MAX_TRUNCATE is used to control when a table is considered “large”, so that it is attempted to acquire an exclusive lock before deleting all rows. The default for this parameter is 1 million rows, and it was introduced and described in SAP Note 1427103 many years ago.

Recently the request came up to allow background jobs to continue with row-by-row deletions if the exclusive lock cannot be obtained. To make that configurable in a more granular way, a new RSADMIN parameter DB4_TRUNC_WITHOUT_FAST_DELETE was introduced with SAP Note 3115165. The parameter can have the following values:

• YES: Always truncate the table. When Fast Delete is not possible because the table cannot acquire an exclusive lock, the records will be deleted row by row.
• NO: Abort the process if no Fast Delete is possible.
• BATCH_NO_DIALOG_ASK: When running in batch and no Fast Delete is possible, abort the process. When running in a dialog work process, ask via popup whether to continue or abort. This behavior is also the default in case the RSADMIN parameter is not set.
• BATCH_YES_DIALOG_ASK: When running in batch, always truncate the table. When running in a dialog work process, ask via popup whether to continue or abort.

By the way: If you did not register for the next SAP on IBM i Webcast on December 2^nd, 2021 yet, you can still do that at http://ibm.biz/ibmi-sap-Dec2021. The topic will be an introduction to strategies for performance analysis on your IBM i system with SAP software.