DBIF_RSQL_SQL_ERROR issue due to PSA indexing problem while data loads

This document discusses about “DBIF_RSQL_SQL_ERROR” error issue which was faced in our system during data loads.

We faced this issue after our BI system was copied on to a new server. Data base in question in Oracle.

Problem Definition:

The info packages would remain in yellow status for long time and would not get completed. in the detailed log we could see certain number of records transferred but records updated would always remain 0(zero).

When we check TRFC queues in the source system, (T-code –SM58) we get following messages:

When we checked ST22 logs on BI side, we found “DBIF_RSQL_SQL_ERROR” log:

So if we check the log, it can be seen clearly, stated that Partition of certain PSA index is in Unusable state.

Work Around:

Workaround is to run Function Module RSDU_TAB_UNIQUE_INDX_LOCAL in the respective BI system.

Please provide the name of the PSA table with inconsistency in the I_TABLNM. We will get the PSA table name from the ST22 trace.

Once this has been done go to TRFC queue in the source system and manually execute the LUWs in the erroneous state.

This will resolve our issue for one specific PSA table.

But we would have to run the module for all the erroneous PSA tables individually to resolve the issue.

Solution:

As per SAP we are suppose create a small Report & execute it in the required system. It will take care of all the PSA tables in one go and we wouldn’t have to repeat this activity in one by one for each PSA table.

Code is as given below:

TYPE-POOLS: rs,rsdu.
DATA: PSA_TAB TYPE tabname.
DATA: ALL TYPE C VALUE 'X'.
DATA:
      l_s_tablnm TYPE tabname,
      l_t_tablnm TYPE STANDARD TABLE OF tabname,
      l_cnt      TYPE i,
      l_rc       LIKE sy-subrc,
      l_s_ods    TYPE RSTSODS,
      l_t_ods    TYPE STANDARD TABLE OF RSTSODS,
      l_t_part_info TYPE rsdu_ts_part_info.

CHECK sy-dbsys = 'ORACLE'.

IF PSA_TAB IS INITIAL AND ALL IS NOT INITIAL.
  SELECT * FROM RSTSODS INTO CORRESPONDING FIELDS OF TABLE l_t_ods WHERE USERAPP NE 'FASTSTORE' AND OBJSTAT = 'ACT'.
  LOOP AT l_t_ods INTO l_s_ods.
    l_s_tablnm  = l_s_ods-odsname_tech.
    CALL METHOD cl_rsar_psa=>partitions_info_get
      EXPORTING
        i_tablnm              = l_s_tablnm
      IMPORTING
        e_ts_part_info        = l_t_part_info
      EXCEPTIONS
        table_not_partitioned = 2
        OTHERS                = 9.
    IF sy-subrc EQ 0.
      APPEND l_s_tablnm TO l_t_tablnm.
    ELSEIF sy-subrc = 2.
      WRITE: /'PSA TABLE:',l_s_tablnm, 'NOT PARTITIONED IN DATABASE'.
      CONTINUE.
    ELSE.
      WRITE:/ 'INCONSISTENCY IN PSA TABLE:', l_s_tablnm.
      CONTINUE.
    ENDIF.
  ENDLOOP.
ELSEIF PSA_TAB IS NOT INITIAL AND ALL IS INITIAL.
  l_s_tablnm = PSA_TAB.
  CALL METHOD cl_rsar_psa=>partitions_info_get
    EXPORTING
      i_tablnm              = l_s_tablnm
    IMPORTING
      e_ts_part_info        = l_t_part_info
    EXCEPTIONS
      table_not_partitioned = 2
      OTHERS                = 9.
  IF sy-subrc EQ 0.
    APPEND l_s_tablnm TO l_t_tablnm.
  ELSEIF sy-subrc = 2.
    WRITE: /'PSA TABLE:',l_s_tablnm, 'NOT PARTITIONED IN DATABASE'.
    EXIT.
  ELSE.
    WRITE:/ 'INCONSISTENCY IN PSA TABLE:', l_s_tablnm.
    EXIT.
  ENDIF.
ELSEIF ( PSA_TAB IS INITIAL AND ALL IS INITIAL ) OR ( PSA_TAB IS NOT INITIAL AND ALL IS NOT INITIAL ).
  WRITE:/ 'PLEASE ENTER PSA TABLE OR USE ALL OPTION'.
  EXIT.
ENDIF.
LOOP AT l_t_tablnm INTO l_s_tablnm.

* check whether we really have a PSA table at hand.
  l_cnt = 0.
  SELECT COUNT(*) INTO l_cnt
    FROM rstsods
    WHERE odsname_tech = l_s_tablnm.
  IF l_cnt <> 1 .
    WRITE: / 'Table ', l_s_tablnm, '  is not a recognized PSA table !'.
    CONTINUE.
  ENDIF.
* if so, create the appropriate LOCAL index
*  PERFORM rsdu_tab_unique_indx_local
*    USING
*      l_s_tablnm
*    CHANGING
*      l_rc.
  CALL FUNCTION 'RSDU_TAB_UNIQUE_INDX_LOCAL'
       EXPORTING
            i_tablnm                     = l_s_tablnm
       EXCEPTIONS
            table_not_exists             = 1
            error_executing_create_index = 2
            OTHERS                       = 3.
  IF l_rc < 0.
    WRITE: / 'Index creation failed for table ', l_s_tablnm, '!'.
  ELSEIF l_rc > 0.
    WRITE: / 'No index creation necessary for table ', l_s_tablnm, '!'.
  ELSE.
    WRITE: / 'Index created for table ', l_s_tablnm, '.'.
  ENDIF.

ENDLOOP.

 

If we face this issue in production system where there is no authorization to create a new report on ad-hoc then

We could also create a dummy APD, write the above code in routine process & execute it to resolve the issue in one go for all the problematic PSAs.