Deleting SAP office documents

In your system the amount of SAP office documents is consuming more and more space. You want to clean up these tables: BCST_CAM, BCST_SR, SOC3, SOFFCONT1, SOFM, SOOD, SOOS, SOST. This blog will explain how.

Questions that will be answered are:

  • How to reduce size of tables BCST_CAM, BCST_SR, SOC3, SOFFCONT1, SOFM, SOOD, SOOS, SOST?
  • How to run the clean up programs before running RSBCS_REORG?
  • How to run program RSBCS_REORG?

SAP office documents to content server

If table SOFFCONT1 is growing fast, you can migrate data to the content server. This might be the easiest solution. Read more in this blog.

See also note 1634908 – Reduce the number of entries of table SOFFCONT1.

See OSS note 3225275 – BC-SRV-COM Guided Answer for the guided Q&A.

Preparations before running RSBCS_REORG

SAP office documents are stored in table SOFFCONT1, and can be deleted with program RSBCS_REORG. See note 966854 – Reorganization – new report. Note 988057 – Reorganization – information contained in the past a very useful PDF document that explains what to do in cases that RSBCS_REORG is not directly can delete an SAP office document. This was removed, but you can download it here:

In most cases you have to run a special program that breaks the link between the document and the data. After that is done you can delete the content. Extra explanation is in OSS note 1641830 – Reorganization of Business Communication Services data (RSBCS_REORG).

Set expiration date

Run program RSBCS_SREQ_EXPIRE (see OSS note 1478279 – Deleting send requests that have not expired) to set the expiration date when needed:

This will remove the expiry dates.

Release the send requests

Run program RSBCS_SREQ_RELEASE (see OSS note 1238740 – Deleting unreleased send requests):

Don’t select too much data, otherwise the program will dump (see note 3033843 – Program RSBCS_SREQ_RELEASE failed with dump TSV_TNEW_PAGE_ALLOC_FAILED).

This will release the send requests for deletion.

But it will only do so if a document is attached. Apply OSS note 3256308 – Initial send requests are not released to get new program RSBCS_SREQ_INITIAL_RELEASE:

This will also release the send request for deletion in case there is no document attached.

Unlink document from application

Run program RSBCS_SREQ_UNLINK (see note 1276402 – Send requests cannot be deleted due to links) to unlink the the document from the object:

Document is in SAP connect queue

Documents might still be in the SAP connect queue. Use program RSBCS_DELETE_QUEUE (see OSS note 1244680 – Deleting send requests from the queue) to remove the queue for the documents:

Document is in a folder

If the document is in a folder it might be in different sort of folder:

  • Hidden folder
  • Private folder
  • Document has GOS link
Document is in a hidden folder

Run program RSSODFRE (see OSS note 567975 – Hidden folder: Reorganization) to delete documents from the hidden folders:

Document is in a private folder

Run program RSSO_DELETE_PRIVATE (see OSS note 922671 – Deleting folder entries) to delete from private folder:

Document has GOS link

To be able to delete document with GOS link, run program RSGOSRE01 (see OSS notes 569123 – Reorganizing documents from generic object services, 1641800 – How to delete ‘Has links to GOS/Document not sent’ documents from the Hidden folder and 2016688 – RSGOSRE01: New selection parameters):

Or run program RSGOSRE02 (OSS note 2031234 – RSGOSRE02: Reorganizing documents from generic object services):

Running RSBCS_REORG

Program RSBCS_REORG will not archive, but only delete.

Test this first and check with the data owner that the documents are no longer needed.

Program RSBCS_REORG selection criteria:

Output:

Double clicking on the line will show the details for the issue why you cannot delete.

Bug fix OSS notes for RSBCS_REORG:

Custom program to mass cancel workflow items

Mass cancellation of work items is possible with the SWIA transaction as explained in this blog.

This blog will explain how you can use Z program to mass close like SWIA, but then in batch job mode, and automatically without user interaction.

The custom program

The custom program ZRSWIWILS_WI_CANCEL is basically a copy of standard SAP program RSWIWILS.

You can copy and paste the code from below source code text and add the text symbols and selection screen parameter text.

Start screen has 1 extra option more than standard SAP:

The program working explained

When the new option is selected, after the data fetching, the program simply calls function module SWW_WI_ADMIN_CANCEL to cancel the item. Since it is done in loop and without user interaction, the program can run in batch mode.

The custom program source code text

* Report ZRSWIWILS_WI_CANCEL
*----------------- Standard program RSWIWILS copy ---------------------*
*---------- This code is to extend existing functionality--------------*

REPORT zrswiwils_wi_cancel MESSAGE-ID swf_rep_base.

INCLUDE rswlfcod.
INCLUDE rswuincl.
CLASS cl_swf_rdg_dispatcher DEFINITION LOAD.

************************************************************************
*  Begin of Data                                                       *
************************************************************************
DATA: g_list_cnt TYPE sy-tabix.
DATA: zlt_wiheader TYPE swfatalvitm.
DATA: int TYPE REF TO if_swf_rep_workitem_selection.
DATA: tcode LIKE sy-tcode.
DATA: g_windows_titlebar TYPE string.
CONSTANTS: zlc_tcode  TYPE sytcode VALUE 'SWIA'.
*- type pools
TYPE-POOLS: slis.
TABLES: swfawrkitm.

*- select options
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-a01.

SELECT-OPTIONS: id FOR swfawrkitm-wi_id.
SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE TEXT-a02.
SELECT-OPTIONS:
    type   FOR swfawrkitm-wi_type,
    state  FOR swfawrkitm-wi_stat,
    prio   FOR swfawrkitm-wi_prio,
    dhsta  FOR swfawrkitm-wi_dh_stat,
    task   FOR swfawrkitm-wi_rh_task,
    taskg  FOR swfawrkitm-wi_rh_task NO INTERVALS.
PARAMETERS: top_only TYPE xfeld.
SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE TEXT-a03.
SELECT-OPTIONS:
    cd FOR swfawrkitm-wi_cd,
    ct FOR swfawrkitm-wi_ct NO-EXTENSION.
SELECTION-SCREEN END OF BLOCK b3.

SELECTION-SCREEN BEGIN OF BLOCK b6 WITH FRAME TITLE TEXT-a06.
PARAMETERS: p_cancel TYPE xfeld DEFAULT ' '.
SELECTION-SCREEN END OF BLOCK b6.

SELECTION-SCREEN BEGIN OF BLOCK b4 WITH FRAME TITLE TEXT-a04.
PARAMETERS: p_more TYPE swi_params-option AS CHECKBOX.
PARAMETERS: filter TYPE swf_utl002-clsname NO-DISPLAY.
PARAMETERS: p_swia TYPE xfeld NO-DISPLAY DEFAULT space. " note 1274031
SELECTION-SCREEN END OF BLOCK b4.

SELECTION-SCREEN BEGIN OF BLOCK b5 WITH FRAME TITLE TEXT-a05.
PARAMETERS: p_maxsel TYPE tbmaxsel.
SELECTION-SCREEN END OF BLOCK b5.

*-------------------------------------------------------------
INITIALIZATION.
*-------------------------------------------------------------
  cd-low = sy-datum.
  cd-sign = 'I'.
  cd-option = 'EQ'.
  APPEND cd.

*-------------------------------------------------------------
*- F4 functionality
*-------------------------------------------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR task-low.
  DATA: act_object_ext TYPE rhobjects-object.

  CALL FUNCTION 'RH_SEARCH_TASK'
    IMPORTING
      act_object_ext         = act_object_ext
    EXCEPTIONS
      no_active_plvar        = 1
      no_org_object_selected = 2
      no_valid_task_type     = 3
      OTHERS                 = 4.
  IF sy-subrc EQ 0.
    task-low = act_object_ext.
  ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR task-high.
  DATA: act_object_ext TYPE rhobjects-object.

  CALL FUNCTION 'RH_SEARCH_TASK'
    IMPORTING
      act_object_ext         = act_object_ext
    EXCEPTIONS
      no_active_plvar        = 1
      no_org_object_selected = 2
      no_valid_task_type     = 3
      OTHERS                 = 4.
  IF sy-subrc EQ 0.
    task-high = act_object_ext.
  ENDIF.

*-------------------------------------------------------------
START-OF-SELECTION.
*-------------------------------------------------------------
  PERFORM main USING p_more p_maxsel.

*---------------------------------------------------------------------*
*       FORM MAIN                                                     *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM main USING p_more TYPE xfeld
                p_maxsel TYPE tbmaxsel.
  DATA: field_lst    TYPE slis_t_fieldcat_alv,
        field_cat    TYPE slis_fieldcat_alv,
        is_layout    TYPE slis_layout_alv,
        is_variant   LIKE disvariant,
        it_sort      TYPE slis_t_sortinfo_alv,
        l_string     TYPE string,
        l_grid_title TYPE lvc_title.

*- prepare the list format (determine columns...)
  PERFORM prepare_format CHANGING field_lst
                                  field_cat
                                  is_layout
                                  it_sort.
  is_variant-report = sy-repid.

*- get the list from the database.
  PERFORM get_workitem_header USING    p_more
                                       p_maxsel
                              CHANGING zlt_wiheader.
*- check empty
  DATA: hits TYPE i.
  DESCRIBE TABLE  zlt_wiheader LINES hits.
  IF NOT hits IS INITIAL.

*- set table layout
    is_layout-cell_merge = 'X'.

*- set title
    is_layout-window_titlebar =
                  'Workitems Cancel'(001).

    g_windows_titlebar = is_layout-window_titlebar.
    PERFORM get_title USING hits
                            g_windows_titlebar
                            l_grid_title.
    is_layout-window_titlebar = l_grid_title.

    IF p_cancel IS NOT INITIAL AND zlt_wiheader IS NOT INITIAL.
* Cancel the work item
      PERFORM cancel_workitem TABLES zlt_wiheader.
    ENDIF.
*- call the CO function to display the list.
    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
      EXPORTING
        i_callback_program      = CONV syrepid( 'ZRSWIWILS_WI_CANCEL' )
        i_callback_user_command = 'CALL_UCOMM_WILIST'
        is_layout               = is_layout
        it_fieldcat             = field_lst
        it_sort                 = it_sort
        is_variant              = is_variant
      TABLES
        t_outtab                = zlt_wiheader
      EXCEPTIONS
        OTHERS                  = 1.
  ELSE.
    MESSAGE s003.
  ENDIF.

ENDFORM.                    "main

*---------------------------------------------------------------------*
*       FORM PREPARE_FORMAT                                           *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  FIELD_LST                                                     *
*  -->  FIELD_CAT                                                     *
*  -->  IS_LAYOUT                                                     *
*---------------------------------------------------------------------*
FORM prepare_format CHANGING field_lst TYPE slis_t_fieldcat_alv
                                 field_cat TYPE slis_fieldcat_alv
                                 is_layout TYPE slis_layout_alv
                                 it_sort   TYPE slis_t_sortinfo_alv.
  DATA: is_sort LIKE LINE OF it_sort.
  DATA: structure_name TYPE dd02l-tabname VALUE 'SWFAWRKITM'.
  DATA: lt_fieldcat TYPE lvc_t_fcat.
  DATA: ls_fieldcat TYPE lvc_s_fcat.
  DATA: wf_settings TYPE swp_admin.
  DATA: lh_aging_services TYPE REF TO cl_sww_daag_services.
  FIELD-SYMBOLS: <field_lst> LIKE LINE OF field_lst.

  is_layout-box_fieldname = 'B_MARKED'.
  is_layout-box_tabname   = 'WIHEADER'.

*- prepare sort
  PERFORM set_sort_tab CHANGING it_sort.

*- get fieldcatalog
  CALL FUNCTION 'LVC_FIELDCATALOG_MERGE' 
    EXPORTING
      i_structure_name       = structure_name
    CHANGING
      ct_fieldcat            = lt_fieldcat
    EXCEPTIONS
      inconsistent_interface = 1
      program_error          = 2
      OTHERS                 = 3.

  LOOP AT lt_fieldcat INTO ls_fieldcat.
    IF ls_fieldcat-fieldname EQ 'WI_RHTEXT'.
      ls_fieldcat-lowercase = 'X'.
    ENDIF.
    MOVE-CORRESPONDING ls_fieldcat TO field_cat.
    field_cat-seltext_s = ls_fieldcat-scrtext_s.
    field_cat-seltext_l = ls_fieldcat-scrtext_l.
    field_cat-seltext_m = ls_fieldcat-scrtext_m.
    APPEND field_cat TO field_lst.
  ENDLOOP.

*- no reference for B_MARKED
  CLEAR field_cat-ref_tabname.
  field_cat-tech = 'X'.
  field_cat-fieldname = 'B_MARKED'.
  APPEND field_cat TO field_lst.
  CLEAR field_cat-tech.

*- set columns positions
  LOOP AT field_lst ASSIGNING <field_lst>.
    CASE <field_lst>-fieldname.
      WHEN 'WI_ID'.
        <field_lst>-col_pos = 1.
      WHEN 'WI_STAT'.
        <field_lst>-col_pos = 2.
      WHEN 'WI_CHCKWI'.
        <field_lst>-col_pos = 3.
      WHEN 'TYPETEXT'.
        <field_lst>-col_pos = 4.
        <field_lst>-outputlen = 15.
      WHEN 'WI_RH_TASK'.
        <field_lst>-col_pos = 5.
        <field_lst>-outputlen = 11.
      WHEN 'WI_CD'.
        <field_lst>-col_pos = 6.
        <field_lst>-outputlen = 10.
      WHEN 'WI_CT'.
        <field_lst>-col_pos = 7.
        <field_lst>-outputlen = 8.
      WHEN 'WI_TEXT'.
        <field_lst>-col_pos = 8.
        <field_lst>-outputlen = 80.
      WHEN 'WI_CONFIRM'.
        <field_lst>-col_pos = 9.
      WHEN 'WI_REJECT'.
        <field_lst>-col_pos = 10.
      WHEN 'WI_PRIOTXT'.
        <field_lst>-col_pos = 11.
      WHEN 'RETRY_CNT'.
        <field_lst>-col_pos = 12.
      WHEN 'TOP_TASK'.
        <field_lst>-col_pos = 13.
      WHEN OTHERS.
        <field_lst>-col_pos = 999999.
    ENDCASE.
  ENDLOOP.
  SORT field_lst BY col_pos.
  LOOP AT field_lst ASSIGNING <field_lst>.
    <field_lst>-col_pos = sy-tabix.
  ENDLOOP.

*- set standard layout
  CLEAR field_cat.
  field_cat-no_out = 'X'.
  MODIFY field_lst FROM field_cat TRANSPORTING no_out
     WHERE fieldname EQ 'WI_LANG' OR
           fieldname EQ 'WI_TYPE' OR
           fieldname EQ 'VERSION' OR
           fieldname EQ 'WI_PRIO' OR
           fieldname EQ 'NOTE_CNT' OR
           fieldname EQ 'WI_RELEASE' OR
           fieldname EQ 'STATUSTEXT' OR
           fieldname EQ 'TCLASS' OR
           fieldname EQ 'WI_DH_STAT' OR
           fieldname EQ 'RETRY_CNT' OR
           fieldname EQ 'WLC_FLAGS' OR
           fieldname EQ 'TOP_TASK' OR
           fieldname EQ 'AGING_STATE' OR
           fieldname EQ 'AGING_TEMPERATURE'.

*- delete aging fields if not applicable
  lh_aging_services = cl_sww_daag_services=>get_instance( ).
  IF lh_aging_services->aging_enabled( ) NE 'X'.
    DELETE field_lst WHERE fieldname EQ 'AGING_STATE'
                        OR fieldname EQ 'AGING_TEMPERATURE'.
  ENDIF.

*- set checkboxes
  CLEAR field_cat.
  field_cat-checkbox = 'X'.
  MODIFY field_lst FROM field_cat TRANSPORTING checkbox
     WHERE fieldname EQ 'WI_CONFIRM' OR
           fieldname EQ 'WI_REJECT' OR
           fieldname EQ 'WI_DEADEX' OR
           fieldname EQ 'NOTE_EXIST' OR
           fieldname EQ 'ASYNCAGENT'.

  IF tcode = 'SWI2_ADM1'. " workitems ohne bearbeiter
    CLEAR field_cat.
    field_cat-col_pos = '0'.
    field_cat-key = 'X'.
    MODIFY field_lst FROM field_cat TRANSPORTING col_pos key
       WHERE fieldname EQ 'ASYNCAGENT'.
  ELSE.
    CLEAR field_cat.
    field_cat-tech = 'X'.
    MODIFY field_lst FROM field_cat TRANSPORTING tech
       WHERE fieldname EQ 'ASYNCAGENT'.
  ENDIF.

*- delete agents if necessary
  CALL FUNCTION 'SWP_ADMIN_DATA_READ' 
    IMPORTING
      wf_settings = wf_settings
    EXCEPTIONS
      OTHERS      = 1.
  IF wf_settings-no_agents = 'X'.
    field_cat-tech = 'X'.
    MODIFY field_lst FROM field_cat TRANSPORTING tech
       WHERE fieldname EQ 'EXEUSER' OR
             fieldname EQ 'FORW_BY'.
  ENDIF.

ENDFORM.                    "prepare_format

*---------------------------------------------------------------------*
*       FORM CALL_UCOMM_WILIST                                        *
*---------------------------------------------------------------------*
*       Dynamic call to process the keyboard input.                   *
*---------------------------------------------------------------------*
*  -->  UCOMM                                                         *
*  -->  SELFIELD                                                      *
*---------------------------------------------------------------------*

FORM call_ucomm_wilist   USING ucomm TYPE syucomm
                               selfield TYPE slis_selfield.
  DATA: old_list_cnt    LIKE g_list_cnt,
        s_return        LIKE swl_return,
        line_idx        LIKE sy-tabix,
        l_tabix         LIKE sy-tabix,
        linesel_cnt     LIKE sy-tabix,
        b_line_selected LIKE sy-binpt,
        ls_wiheader     TYPE LINE OF swfatalvitm.
  DATA: lt_wrkitm       TYPE swfatwrkitm.
  DATA: delta_list_cnt  TYPE sytabix.
  DATA: ls_por          TYPE sibflpor.
  DATA: lt_por          TYPE sibflport.
  DATA: lv_ucomm        TYPE syucomm.
  DATA: l_excp          TYPE REF TO cx_swf_ifs_exception.
  DATA: ls_suspend      TYPE swp_suspen.
  DATA: ls_swwwidh      TYPE swwwidh.
  DATA: l_wi_index      TYPE sytabix.
  DATA: lh_grid         TYPE REF TO cl_gui_alv_grid.
  DATA: l_grid_title    TYPE lvc_title.
  DATA: l_count         TYPE sytabix.


  PERFORM pick_line USING    selfield-tabindex
                             zlt_wiheader
                    CHANGING line_idx
                             linesel_cnt
                             b_line_selected.

  LOOP AT zlt_wiheader INTO ls_wiheader WHERE b_marked EQ 'X'.
    ls_por-catid  = swfco_objtype_bc.
    ls_por-instid = ls_wiheader-wi_id.
    APPEND ls_por TO lt_por.
  ENDLOOP.

  IF lt_por[] IS INITIAL.
    READ TABLE zlt_wiheader INDEX line_idx INTO ls_wiheader.
    ls_por-catid  = swfco_objtype_bc.
    ls_por-instid = ls_wiheader-wi_id.
    APPEND ls_por TO lt_por.
  ENDIF.

************************************************************************
*    Refresh Instancemanager                                         *
************************************************************************
  TRY.
      CALL METHOD cl_swf_run_wim_factory=>initialize( ).
    CATCH cx_swf_ifs_exception INTO l_excp.
      CALL METHOD cl_swf_utl_message=>send_message_via_exception( l_excp ).
  ENDTRY.

  CASE ucomm.
************************************************************************
*    Filter                                                            *
************************************************************************
    WHEN '&ILT'.

************************************************************************
*    Refresh                                                           *
************************************************************************
    WHEN '1REF'.
      CALL METHOD int->refresh
        IMPORTING
          ex_delta_count = delta_list_cnt.
      CALL METHOD int->get_entries
        IMPORTING
          ex_wientries = lt_wrkitm.
      CLEAR zlt_wiheader[].
      PERFORM convert_to_alv_list USING    lt_wrkitm
                                           p_more
                                  CHANGING zlt_wiheader.
      MESSAGE s811(w8) WITH delta_list_cnt.
      selfield-refresh    = 'X'.
      selfield-row_stable = 'X'.

************************************************************************
*   Pick                                                               *
************************************************************************
    WHEN '&IC1'.
      IF linesel_cnt > 1.
        MESSAGE s201(wi).
        EXIT.
      ENDIF.
      IF  line_idx     = 0.
        MESSAGE s004(0k).
        EXIT.
      ENDIF.

      READ TABLE zlt_wiheader INDEX line_idx INTO ls_wiheader.
      ls_por-catid  = 'BC'.
      ls_por-instid = ls_wiheader-wi_id.

      IF ls_wiheader-wi_type = wi_flow.
        lv_ucomm = function_wi_workflow_display.
      ELSE.
        lv_ucomm = cl_swf_rdg_dispatcher=>c_function_wi_display.
      ENDIF.
      CALL METHOD cl_swf_rdg_dispatcher=>execute_dialog_request
        EXPORTING
          im_por      = ls_por
          im_function = lv_ucomm
        EXCEPTIONS
          OTHERS      = 1.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.

    WHEN OTHERS.

*      IF  line_idx     = 0.  "--- OSS note 1422569 ---
      IF linesel_cnt   = 0.
        MESSAGE s004(0k).
        EXIT.
      ENDIF.

      CALL METHOD cl_swf_rdg_dispatcher=>execute_dialog_request_multi 
        EXPORTING
          im_por      = lt_por
          im_function = ucomm
        EXCEPTIONS
          OTHERS      = 1.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.

  ENDCASE.

  DATA: ls_layout TYPE slis_layout_alv.
  DATA: lt_filtered_entries TYPE  slis_t_filtered_entries.
  DATA: l_lines TYPE i.
  CALL FUNCTION 'REUSE_ALV_GRID_LAYOUT_INFO_GET'
    IMPORTING
      es_layout           = ls_layout
      et_filtered_entries = lt_filtered_entries
    EXCEPTIONS
      OTHERS              = 1.
  IF sy-subrc EQ 0.
    DESCRIBE TABLE lt_filtered_entries LINES l_lines.
    DESCRIBE TABLE zlt_wiheader LINES l_count.
    l_count = l_count - l_lines.
    PERFORM get_title USING l_count
                            g_windows_titlebar
                            l_grid_title.
    ls_layout-window_titlebar = l_grid_title.
    CALL FUNCTION 'REUSE_ALV_GRID_LAYOUT_INFO_SET'
      EXPORTING
        is_layout = ls_layout.
  ENDIF.

ENDFORM.                    "call_ucomm_wilist

*&---------------------------------------------------------------------*
*&      Form  set_sort_tab
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_IT_SORT  sorttab (ALV format)
*----------------------------------------------------------------------*
FORM set_sort_tab CHANGING p_it_sort TYPE slis_t_sortinfo_alv.
  DATA: is_sort TYPE slis_sortinfo_alv.

  REFRESH p_it_sort.
  is_sort-tabname = 'WIHEADER'.
  is_sort-spos = 1.
  is_sort-fieldname = 'WI_CD'.
  is_sort-down = 'X'.
  APPEND is_sort TO p_it_sort.

  is_sort-spos = 2.
  is_sort-fieldname = 'WI_CT'.
  is_sort-down = 'X'.
  APPEND is_sort TO p_it_sort.

  is_sort-spos = 3.
  is_sort-fieldname = 'WI_ID'.
  is_sort-down = 'X'.
  APPEND is_sort TO p_it_sort.

ENDFORM.                               " set_sort_tab
*&---------------------------------------------------------------------*
*&      Form  get_workitem_header
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_WI_HEADER  text
*----------------------------------------------------------------------*
FORM get_workitem_header USING    p_more      TYPE xfeld
                                  p_maxsel    TYPE tbmaxsel
                         CHANGING p_wi_header TYPE swfatalvitm.

  DATA: rangetab_for_id TYPE swfartwiid.
  DATA: rangetab_for_type TYPE swfartwitp.
  DATA: rangetab_for_creation_date TYPE swfartcrdat.
  DATA: rangetab_for_creation_time TYPE swfartcrtim.
  DATA: rangetab_for_task TYPE swfartrhtsk.
  DATA: rangetab_for_state TYPE swfartwista.
  DATA: rangetab_for_priority TYPE swfartprio.
  DATA: rangetab_for_dhsta TYPE swfartdhsta.
  DATA: lt_wrkitm TYPE swfatwrkitm.
  DATA: ls_wrkitm TYPE LINE OF swfatwrkitm.
  DATA: ls_alvitm TYPE LINE OF swfatalvitm.

  IF int IS INITIAL.
    int = cl_swf_rep_manager=>get_instance( ).
  ENDIF.

*- set selection properties
  IF p_more EQ 'X'.
    CALL METHOD int->set_property
      EXPORTING
        im_name  = if_swf_rep_workitem_selection=>c_get_administrator
        im_value = 'X'.
  ENDIF.

*- convert parameters
  rangetab_for_id[] = id[].
  rangetab_for_type[] = type[].
  rangetab_for_creation_date[] = cd[].
  rangetab_for_creation_time[] = ct[].
  rangetab_for_task[] = task[].
  rangetab_for_state[] = state[].
  rangetab_for_priority[] = prio[].
  rangetab_for_dhsta[] = dhsta[].

  CALL METHOD int->clear( ).
  CALL METHOD int->set_filter_strategy( filter ).

  CALL METHOD int->set_range_tab( rangetab_for_id ).
  CALL METHOD int->set_range_tab( rangetab_for_type ).
  CALL METHOD int->set_range_tab( rangetab_for_creation_date ).
  CALL METHOD int->set_range_tab( rangetab_for_creation_time ).
  CALL METHOD int->set_range_tab( rangetab_for_task ).
  CALL METHOD int->set_range_tab( rangetab_for_state ).
  CALL METHOD int->set_range_tab( rangetab_for_priority ).
  CALL METHOD int->set_range_tab( rangetab_for_dhsta ).
  CALL METHOD int->set_only_top_wi( top_only ).

  CALL METHOD int->set_maxsel( p_maxsel ).

  CALL METHOD int->read( ).

  CALL METHOD int->get_entries
    IMPORTING
      ex_wientries = lt_wrkitm.

  PERFORM convert_to_alv_list USING    lt_wrkitm
                                       p_more
                              CHANGING p_wi_header.


ENDFORM.                               " get_workitem_header

*---------------------------------------------------------------------*
*       FORM convert_to_alv_list                                      *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  WIHEADER                                                      *
*  -->  WIALVITM                                                      *
*---------------------------------------------------------------------*
FORM convert_to_alv_list USING    wiheader TYPE swfatwrkitm
                                  more     TYPE xfeld
                         CHANGING wialvitm TYPE swfatalvitm.
  DATA: ls_wrkitm TYPE LINE OF swfatwrkitm.
  DATA: ls_alvitm TYPE LINE OF swfatalvitm.
  DATA: lv_wi_handle TYPE REF TO if_swf_run_wim_internal.
  DATA: ls_admin TYPE swhactor.
  DATA: lt_agents TYPE tswhactor.

  LOOP AT wiheader INTO ls_wrkitm.                  
    MOVE-CORRESPONDING ls_wrkitm TO ls_alvitm.
    IF ls_wrkitm-wlc_flags O swfcr_p_asynchronous_rule.
      ls_alvitm-asyncagent = 'X'.
    ENDIF.
    IF more EQ 'X' AND ls_alvitm-wi_type EQ swfco_wi_flow.
      TRY.
          CALL METHOD cl_swf_run_wim_factory=>find_by_wiid 
            EXPORTING
              im_wiid     = ls_wrkitm-wi_id
            RECEIVING
              re_instance = lv_wi_handle.
          lt_agents = lv_wi_handle->get_administrator_agents( ).
          READ TABLE lt_agents INDEX 1 INTO ls_admin.
          ls_alvitm-adm_agent = ls_admin.
        CATCH cx_swf_run_wim.
      ENDTRY.
    ENDIF.
    IF top_only IS INITIAL.
      APPEND ls_alvitm TO wialvitm.
    ELSE.
      IF ls_alvitm-wi_chckwi IS INITIAL.
        APPEND ls_alvitm TO wialvitm.
      ENDIF.
    ENDIF.
  ENDLOOP.

ENDFORM.                    "convert_to_alv_list

*---------------------------------------------------------------------*
*       FORM PICK_LINE                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  SELFIELD_IDX                                                  *
*  -->  WIHEADER                                                      *
*  -->  INDEX                                                         *
*  -->  LINESEL_CNT                                                   *
*  -->  B_OK                                                          *
*---------------------------------------------------------------------*
FORM pick_line USING    selfield_idx LIKE sy-tabix 
                            wiheader     TYPE swfatalvitm
                   CHANGING index        LIKE sy-tabix
                            linesel_cnt  LIKE sy-tabix
                            b_ok         LIKE sy-binpt.
  DATA: lines_marked LIKE sy-tabix,
        marked_idx   LIKE sy-tabix,
        cursor_line  LIKE sy-binpt.

  IF selfield_idx > 0.
    READ TABLE wiheader INDEX selfield_idx TRANSPORTING NO FIELDS.
    IF sy-subrc = 0.
      cursor_line = 'X'.
    ENDIF.
  ENDIF.

  LOOP AT wiheader TRANSPORTING NO FIELDS WHERE b_marked = 'X'.
    ADD 1 TO lines_marked.
    marked_idx  = sy-tabix.
  ENDLOOP.

************************************************************************
*  List tool algorithm                                                 *
************************************************************************
  IF cursor_line = 'X'.
    index = selfield_idx.
    IF lines_marked < 2.
      linesel_cnt = 1.
    ELSE.
      linesel_cnt = lines_marked.
    ENDIF.
    b_ok = 'X'.

  ELSEIF lines_marked = 1.
    index = marked_idx.
    linesel_cnt = 1.
    b_ok = 'X'.

  ELSEIF lines_marked > 1.
    linesel_cnt = lines_marked.
    b_ok = 'X'.

  ENDIF.

ENDFORM.                    "pick_line
*&---------------------------------------------------------------------*
*&      Form  GET_TITLE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_HITS  text
*      -->P_L_STRING  text
*----------------------------------------------------------------------*
FORM get_title  USING p_hits TYPE sytabix
                      p_title_template TYPE string
                      p_title TYPE lvc_title.
  DATA: l_string TYPE string.
  DATA: l_count(10) TYPE n.

  IF p_hits > 1.
    l_string = '(&1 entries)'(014).
  ELSEIF p_hits EQ 1.
    l_string = '(1 entry)'(015).
  ENDIF.
  l_count = p_hits.
  SHIFT l_count LEFT DELETING LEADING '0'.
  CONCATENATE p_title_template l_string INTO p_title SEPARATED BY space.
  REPLACE '&1' IN p_title WITH l_count.

ENDFORM.                    " GET_TITLE
*&---------------------------------------------------------------------*
*&      Form  CANCEL_WORKITEM
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_ZLT_WIHEADER  text
*----------------------------------------------------------------------*
FORM cancel_workitem  TABLES p_zlt_wiheader TYPE swfatalvitm.
  TYPES: zlty_status TYPE RANGE OF sww_wistat.
  DATA: zlt_r_status TYPE RANGE OF sww_wistat.

  CONSTANTS: zlc_stat_complete TYPE sww_statxt VALUE 'COMPLETED',
             zlc_stat_cancel   TYPE sww_statxt VALUE 'CANCELLED',
             zlc_sign          TYPE  ddsign VALUE 'I',
             zlc_options       TYPE ddoption VALUE 'EQ'.

  zlt_r_status = VALUE zlty_status(
                       LET s = zlc_sign
                           o = zlc_options
                           IN sign   = s
                              option = o
                              ( low = zlc_stat_complete )
                              ( low = zlc_stat_cancel ) ).
*//Keep only the work items which has to be CANCELLED
  DELETE p_zlt_wiheader WHERE wi_stat IN zlt_r_status.

  IF p_zlt_wiheader[] IS NOT INITIAL.
    LOOP AT p_zlt_wiheader ASSIGNING FIELD-SYMBOL(<zlfs_wiheader>). 
      TRY.
          CALL FUNCTION 'SWW_WI_ADMIN_CANCEL' 
            EXPORTING
              wi_id                       = <zlfs_wiheader>-wi_id
              do_commit                   = abap_true
            IMPORTING
              new_status                  = <zlfs_wiheader>-wi_stat
            EXCEPTIONS
              update_failed               = 1
              no_authorization            = 2
              infeasible_state_transition = 3
              OTHERS                      = 4.
*- exception handling
        CATCH cx_swf_run_wim INTO DATA(lv_excp).
          DATA: zlr_txmgr TYPE REF TO cl_swf_run_transaction_manager.
          CALL METHOD zlr_txmgr->rollback( ). 
      ENDTRY.
    ENDLOOP.
  ENDIF.
ENDFORM.

add parameter

call SWW_WI_ADMIN_CANCEL in loop

Analysis of SYS_LOB* tables

When you are trying to manage your database size, you might see large SYS_LOB* tables on Oracle based systems.

Questions that will be answered in this blog are:

  • How can I find large SYS_LOB tables in my Oracle based SAP system?
  • How can I find the real table for the SYS_LOB?

Finding SYS_LOB table size

In DB02 select all segments larger than 1 GB with name SYS_LOB*:

Output might be like this:

On the tab Lobs you can see the table:

From SYS_LOB name to table

Next to the direct view in DB02 as shown above, OSS note 2142497 – What to do for large lobsegment on Oracle database? describes several ways of determining the translation from the SYS_LOB label to the real table.

You can use ST04 SQL query and fire this query: replace the SYS_LOB number from the one in your own system:

SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_LOBS
WHERE 'SYS_LOB0007061918C00006$$' IN (SEGMENT_NAME, INDEX_NAME);

Or in ST04, you launch the DBA view for DBA_LOBS:

Result:

In this case table DMC_INDXCL is the source of the SYS_LOB. Knowing this it will easily lead you to OSS note 2704146 – How to delete data in DMC_INDXCL? – SLT for clean up.

Frequent SYS_LOB tables

Frequent SYS_LOB tables and solutions:

Data archiving: data retrieval

When you perform data archiving, from time to time you need to give support on data retrieval issues.

This blog will explain some of the general data retrieval concepts.

Questions that will be answered in this blog are:

  • How does single record retrieval work?
  • How can I use the archive explorer?
  • How can I get a list of data from the archive?

Single record retrieval

Single record retrieval is different per archiving object.

Some objects (like FI_DOCUMNT) are nicely integrated. In FB03 the system will check first database, then look into the archive inforecords to find if the document is archived. And then it will show the document in same layout.

Most objects have archive read program which you can find in SARA:

Now run the read program:

And fill out the record(s) you need:

Now you need to select the data files:

If you didn't label your files correctly, you need to select them all, which makes data retrieval slow.

Results are shown:

Results might look ok, or very basic. This is different per archiving object.

Use of archive explorer for table level

An alternative way is the use of the archive explorer. This will give details on table level.

Start transaction SARE:

Fill out the required object and archive infostructure. In this case we used change document. In the second screen fill the object:

Now you can see list of changes:

Double click on the record to see the tables:

Double clicking on the table will give the actual table line content.

Filling infostructures

More on infostructures can be read in this dedicated blog.

List transactions

Some transactions (especially in FICO domain) have integrated reporting with the data archive. We will use transaction FBL3N as example.

Start FBL3N:

Then click on Data Sources, include Archive, and select the needed files:

If you didn't label your files correctly, you need to select them all, which makes data retrieval slow.

Data archiving improvement notes 2018

In 2018 SAP ran an improvement project which resulted into a set of OSS notes that will make data archiving more robust and easy.

All of these notes come with manual work. Select the ones really useful.

Archiving write process improvements

Write variant maintenance has been made easier by allowing copying of variants (useful if you have many plants and company codes and want to store each one in different archive file): 2520093 – Archive administration: Enhanced variant maintenance (writing, preprocessing, and postprocessing).

To be able to detail the written file name of the archive file implement this oss note: 2637105 – Print list for archiving write jobs: Placeholders for session numbers, archive file key in title.

Archiving storage process improvements

Archiving system technical check button is available in OAC0, but not in SARA. After applying this note you can also check it in the technical settings in SARA: 2599263 – Connection test for storage systems for archiving object.

Deletion process improvements

To be able to quickly continue with interrupted archiving sessions apply this note 2520094 – Continue: Information on existence of interrupted or incomplete archiving sessions.

This note will implement checks to warn you about uncompleted previous store and delete runs: 2586921 – Run selection for deletion: Information about the existence of unstored archive files.

Some archiving object use the AIS (archiving information system) to enable the end user a quick retrieval of archiving information. This note will give warning before start of deletion if the AIS is note active for the object: 2624077 – Starting delete jobs: Check for active info structures.

Archiving overview and logging improvement

To get a better overall overview of all logs apply OSS note 2433546 – Archive administration logs: Information about errors in hierarchy display. Showing only success message is possible after applying OSS note 2855641 – Logs: New option “Success Messages Only” for detail log.

Direct navigation to Archive File Browser: apply OSS note 2544517 – Archive administration: Direct navigation to ArchiveFileBrowser. This note only gives you a link. You can already start the archive file browser using transaction AS_AFB:

Archive file browser

Note 2823924 – Archive File Browser: Messages that do not belong to the Archive File Browser are output solves a bug in the Archive File Browser.

SAP database growth control: HANA data aging

HANA data aging is a method to reduce the memory footprint of the HANA in-memory part without disturbing the end users. It is not reducing your database size.

This blog will answer following questions:

  • What is HANA data aging?
  • How to switch HANA data aging on?
  • How to set up HANA data aging for technical objects?
  • What about data aging for functional objects?

What is HANA data aging?

HANA data aging is an application method to reduce the memory footprint based on application data logic. It is not a database feature but an application feature. The goal of HANA data aging is not to reduce the database size (which it is not doing), but to reduce the actual memory footprint of the HANA in-memory database.

Let’s take idocs as example: the idocs that are processed ok you need to keep in database for an agreed amount of time before business or audit allows you to delete them. Lets say you can only delete after 1 year. Every action on idocs now means that full year of idoc content is occupying main memory. For daily operational tasks you normally only need 2 months of data in memory and rest you can accept that it will take bit longer to read from disc into memory.

This is exactly what data aging is doing: you partition the data into application logic based chunks. In this case you can partition the idoc data per month and only have last 2 months in active memory. The other 10 months are on disc only. Reading data of last 2 months is still fast as usual. When having to report on the 10 months on disc, the system first needs to load from disc into memory; will be slower.

To reduce database itself, you would still need to do data archiving.

Advantage of the data aging is that the more expensive memory footprint costs can be reduced in such a way that the end users are not hampered. Data aging is transparent for them. With data archiving the users will always need to select different transaction and data files.

How to switch on data aging?

To switch on data aging on system level you need to do 2 things:

  1. Set the parameter abap/data_aging to on in RZ11
  2. In SFW5 switch on the switch called DAAG_DATA_AGING

This only enables the system for data aging.

Data aging switch on for technical object: example for application logging

With transaction DAGADM you can see the administration status of the data aging object. You first see red lights that the objects are not activated for data aging.

Per object you have extra transactions (which unfortunately differ per object…) to set the retention times. For application logging this is transaction SLGR. Here we choose in this example to data age all log after 180 days:

The advantage of this tailoring is that you could only age some of the objects if you want.

The transaction and OSS note for each of the objects can be found on this SAP blog.

Next step is to setup partitions for the object. To do this start transaction DAGPTM and open the object you want to partition:

SBAL partitioning

Initial screen is in display mode. Hit change button. On the bottom right side hit the Period button (Selection Time Period). In the popup enter the desired start date, time buckets (months, years) and amount of repetitions:

Partition intervals

Now the partitions are defined. To execute the partitioning hit the execute button to start the partitioning in the background. Wait until the job finishes. Before running this on productive system check the runtime first on non-productive system with about same data size if possible.

After partitioning the screen should look like this:

Now we can activate the object in transaction DAGADM. Select the object and press the activate button. Popup appears to assign the object to existing data aging or new group:

The data aging run will be done per group.

To start the actual data aging run start transaction DAGRUN.

Here you can schedule a new run with the Schedule new run button.

To see the achieved results of the data aging go to transaction DAGADM and select the object. Then push the button View current/Historical data.

Functional data aging objects

Functional data archiving objects exist as well for Financial documents, sales orders, deliveries, etc. The full list and minimal application version can be found on this SAP blog.

Words of caution for functional archiving:

  • The technical archiving objects are more mature in coding and usage. They are used in productive system and are with lesser bugs than the technical objects
  • Before switching on a functional data aging object you need to prepare your custom ABAP code. If they are not adjusted properly to take the partitions with the date selections (or other application selection mechanism) into account all benefits are immediately lost. A Z program that reads constantly into full history will force a continuous read of historical partitions….

Background information

More background information can be found in OSS notes 2416490 – FAQ: SAP HANA Data Aging in SAP S/4HANA and 2869647 – Guidance for use of Data Aging in SAP S/4HANA.

Bug fix and other OSS notes

SAP database growth control: getting insight

This blog will explain about getting insight into SAP database growth and controlling the growth.

Questions that will be answered are:

  • Do I have a database growth issue?
  • What are my largest tables?
  • How do I categorize my tables?

Why control database growth?

Controlling database growth has several reasons:

  • When converting to S/4 HANA you could end up with smaller physical HANA blade and need to buy less memory licenses from SAP
  • Less data storage leads to less costs (think also about production data copied back to acceptance, development and sandbox systems)
  • Back up / restore procedures are longer with large databases
  • Performance is better with smaller databases

Database growth

The most easy way to check if the database is growing too fast or not is using the Database Growth section in the SAP EWA (early watch alert). The EWA has both graphical and table representation for the growth:

EWA database growth picture

EWA database growth table

Or you can check online in the EWA workspace.

You now have to determine if the growth is acceptable or not. This depends a bit on the usage of the system, amount of users, business data, and if you already stretched your infrastructure or not.

General rules of thumb: 

1. Growth < 1 GB/month: do not spend time.
2. Growth > 1 GB/month and < 5 GB/month: implement technical clean up.
3. Growth > 5 GB/month: implement technical clean up and check for functional archiving opportunities.

Which are my largest tables?

To find the largest tables and indexes in your system start transaction DB02. In here select the option Space/Segments/Detailed Analysis and select all tables larger than 1 GB (or 1000 MB):

DB02 selection of tables larger than 1 GB

Then wait for the results and sort the results by size:

DB02 sorted by size

You can also download the full list.

Analysis of the large  tables

Processing of the tables is usually done by starting with the largest tables first.

You can divide the tables in following categories:

  1. Technical data: deletion and clean up can be done (logging you don’t want any more like some idoc types, application logging older than 2 years, etc): see blog on technical clean up
  2. Technical data: archiving or storing can be done (idocs you must store, but don’t need fast access to, attachments)
    1. In Oracle based systems, you might find large SYS_LOB tables. To analyze these, read this special blog.
  3. Functional data: archiving might be done here: for functional data archiving see this blog on technical execution, and this blog for business discussions.

SAP data management guide

SAP has a best practice document called “Data Management Guide for
SAP Business Suite” or “DVM guide”. This document is updated every quarter to half year. The publication location is bit hidden by SAP under their DVM (data volume management) service. In the bottom here go to SAP support and open the How-to-guides section. Or search on google with the term “Data Management Guide for SAP Business Suite” (you might end up with a bit older version). The guide is giving you options per large table to delete and/or archive data.

Common technical objects

Most common technical tables you will come across:

  • EDIDC, EDIDS, EDI40: idocs
  • DBTABLOG: table changes
  • BALHDR, BALDAT: application logging
  • SWW* (all that start with SWW): workflow tables
  • SYS_LOB…..$$: attachments (office attachments and/or DB storage of attachments and/or GOS, global object services attachments)

Detailed table analysis for functional tables: TAANA tool

For detailed analysis on functional tables the TAANA (table analysis) tool can be used. Simply start transaction TAANA.

Now create a table analysis variant by giving the table name and selection of the analysis variant:

TAANA start screen

The default variant will only do a record count. Some tables (like BKPF in this example) come with a predefined ARCHIVE variant. This is most useful option. If this option does not fit your need, you can also push the create Ad Hoc Report button and define your own variant.

Caution: with the ad hoc variant select your fields with care, since the analysis will count all combinations of fields you select. Never select table key fields

Results of TAANA are visible after the TAANA batch job is finished.

TAANA result

By running the proper TAANA analysis for a large functional table you get insight into the distribution per year, company code, plant, document type etc. This will help you also estimate the benefits of archiving a specific object.

For TAANA improvement on dynamic subfields, please check this blog.

If you run on HANA, you can also use SE16H for the table analysis.

Bug fix OSS note for TAANA:

Alternatives for TAANA

If you are running on HANA, you can also check using SE16H and SE16S.

From analysis to action

For the technical clean up read the special blog on this topic. For functional objects, you need to find the relation from the table to the functional data archiving object. This relation and how to find it is clearly explained in OSS note 2607963 – How to find the relationship between table and archive object.

SAP data volume management via SAP solution manager

SAP is offering option to report on data volume management via SAP solution manager directly or as a subsection in the EWA. Experience so far with this: too long in setup, too buggy. The methods described above are much, much faster and you get insight into a matter of hours. The DVM setup will take you hours to do and days/weeks to wait for results….