Technical Articles
Pitfalls at S/4HANA db tables with replacement object
1 Introduction
Due to the change of some data models, SAP uses so-called replacement objects for some db tables in S/4HANA. There are some oddities to consider, which I would like to discuss in this blog post.
Since the basis for the replacement object is a CDS-View, it seems mysteriously that a db table can also exist on the HANA database at the same time, where individual data records can be stored.
2 Main Part
2.1 Example and technical Basics
A good example is the table COEP (CO object line items by period).
In the text editor of eclipse the replacement object is specified using an annotation.
This means that the CDS-View V_COEP_VIEW is responsible for the data collection.
With the help of the Dependency Analyzer you can quickly get an overview which database objects are finally invoved in this CDS-View.
We can see that the view apparently recursively accesses the COEP table and other tables like the ACDOCA.
The UNION statement indicates that part of the data records are from COEP and the rest from ACDOCA and the other tables.
2.2 Where is what
To find out where the data are, let’s try to use our well-known board tools like transaction SE16h.
By the way: In SE16h “Proxy Object” means the sqlViewName for the replacement object CDS-View.
In sum we have 5.395 datarecords, but who many are stored in table COEP directly?
To find this out I wrote a small report with native SQL, considering client handling manually.
REPORT y_coep_demo.
DATA: gv_count TYPE i.
TRY.
EXEC SQL.
SELECT COUNT( * ) FROM coep INTO :gv_count WHERE MANDT = :sy-mandt
ENDEXEC.
WRITE: gv_count.
CATCH cx_sy_native_sql_error.
ENDTRY.
The result shows that 483 records are stored directly in the HANA COEP table.
However, can we assume that only the remaining 4912 data records can be read via the CDS-View V_COEP_R3?
Unfortunately no. Here we have as result 5262 records.
And what is the difference of 350 datasets?
2.3 Harder weapons
We need to find out the data sets that are in both the COEP table and the CDS-View V_COEP_R3_VIEW.
Therefore I wrote a second little report.
To make it a bit easier and to use few lines, I select with * what is “forbidden” in a productive program.
REPORT y_coep_demo2.
DATA: go_table TYPE REF TO data,
gt_coep TYPE SORTED TABLE OF coep WITH UNIQUE KEY kokrs belnr buzei.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE.
DATA(go_struct_descr) = CAST cl_abap_structdescr(
cl_abap_structdescr=>describe_by_name( 'COEP' )
).
DATA(go_table_descr) = cl_abap_tabledescr=>create(
CAST #( go_struct_descr )
).
CREATE DATA go_table TYPE HANDLE go_table_descr.
DATA(go_result) = NEW cl_sql_statement( )->execute_query(
`SELECT * FROM coep WHERE MANDT = ` && sy-mandt ).
go_result->set_param_table( itab_ref = go_table ).
go_result->next_package( upto = 6000 ).
ASSIGN go_table->* TO <gt_table>.
IF <gt_table> IS ASSIGNED.
gt_coep = <gt_table>.
ENDIF.
SELECT kokrs, belnr, buzei
FROM v_coep_r3_view
INTO TABLE @DATA(gt_coep_r3).
DATA(gt_diff) = FILTER #( gt_coep_r3 IN gt_coep
WHERE
kokrs EQ kokrs AND
belnr EQ belnr AND
buzei EQ buzei ).
WRITE: lines( gt_diff ).
And lo and behold, here are exactly the 350 records, which are present in both.
2.4 Crazy Details
There are document numbers where the individual lines are divided into different db tables and are combined again using the replacement object.
Posting rows 1 and 3 (BUZEI) are in COEP directly.
and posting rows 2 and 4 are finally in the ACDOCA via V_COEP_R3 CDS-View stack.
3 Conclusion
In order to finally find out from which db table the data really came from using a replacement object, it need a lot of investigation.
You also have to use native SQL to select the “real” data in the db table. This could also be achieved with SQL-Script within AMDP.
An update on the database table with replacement object can only be done for the records which are really stored in HANA table. The update will fail if data records are read from other CDS-Views.
Nice. Thank you
Hi Andreas Schaller
Thank you for the blog. This is really interesting to see how various techniques interpret these replacement objects / proxy views.
It might have been easier to understand if SAP had converted COEP into a view (as they've done with BSIS etc.) and moved the data that is currently stored in COEP to a separate table (COEP_2?). But I'd imagine there are lots of inserts into the COEP table in standard code that would need to be replaced? As I'm guessing INSERT/UPDATE/DELETE ignore the replacement object.
With regards to the easiest way of selecting from the actual records in the COEP table, simply select from the view V_COEP_ORI in ABAP. This is a CDS view directly on COEP. In CDS, referring to COEP actually refers to the underlying table, not the replacement view so you could also create your own custom view. I assume this is to prevent recursive definitions in the replacement object for COEP.
It's certainly easier than using SQL-Script within AMDP!
Oli