Data Reduction of Table RSDDSTATAGGRDEF - Aggregates Table

[Former Member]

Overview:

The purpose of the document to provide steps to delete the data from table RSDDSTATAGGRDEF which is the largest table in BW containing information of aggregates created in the system.

Background:

Table RSDDSTATAGGRDEF is used by BW version < 7.4 to store the data related to aggregates. Since aggregates are obsolete in BW 7.4 powered by HANA, this table also becomes obsolete. This table is one of the biggest table in BW. After the upgrade to BW 7.4 powered by HANA, entries in the table should be deleted manually. Here is how we can delete the entries.

Details:

There are 2 ways of truncating the entries in the table.

A.  Data base Level

      Execute below SQL at the data base level.

  DELETE FROM rsddstataggrdef WHERE stepuid NOT IN
             (SELECT DISTINCT stepuid FROM rsddstatinfo)

B.Custom Z Report

     Execute below report.

*-----------------------------------------------------------------------*

REPORT  ZRSDDSTATAGGRDEF_DATA_DEL.
TYPE-POOLS: rs,rsddl.

PARAMETERS: reorg TYPE  rs_bool DEFAULT rs_c_true.

*   delete old records in RSDDSTATAGGRDEF ...

  IF reorg = rs_c_true.
  PERFORM db_del_wrong_records.
  ENDIF.

*
*      Form  db_del_wrong_records
*

FORM db_del_wrong_records.


  TYPES: BEGIN OF _l_s_stepuid,
           stepuid TYPE rsddstatinfo-stepuid,
         END OF _l_s_stepuid.


  FIELD-SYMBOLS: <l_s_stepuid> TYPE _l_s_stepuid.


  DATA:l_t_stepuid TYPE TABLE OF _l_s_stepuid,
       l_t_rsddstat TYPE TABLE OF rsddstatinfo.
       REFRESH l_t_stepuid.

* - Geta list of DISTINCT stepuids from RSDDSTATAGGRDEF
* - LOOP at this list and delete all those records from RSDDSTATAGGRDEF
*   that do not have a corresponding stepuid in RSDDSTATINFO

SELECT DISTINCT stepuid FROM (rsddl_c_db_stataggrdef)
      INTO TABLE l_t_stepuid.


  SELECT * FROM rsddstatinfo INTO TABLE l_t_rsddstat.


  LOOP AT l_t_stepuid ASSIGNING <l_s_stepuid>.


    READ TABLE l_t_rsddstat WITH KEY stepuid = <l_s_stepuid>-stepuid TRANSPORTING NO FIELDS.


    IF sy-subrc <> 0.
      DELETE FROM (rsddl_c_db_stataggrdef)
             WHERE stepuid = <l_s_stepuid>-stepuid.
      CALL FUNCTION 'DB_COMMIT'.
    ENDIF.


  ENDLOOP.


ENDFORM.                               " DB_DEL_WRONG_RECORDS

*-----------------------------------------------------------------------*

The program may take longer depending upon the size of the table. Hence, the recommended approach is the first one.

When to Execute ?

This needs to be executed on the copied system before upgrade. This reduces the DMO time.