ABAP Managed Database Procedure

The technology is evolving and SAP has developed techniques like CDS View or AMDP to solve the performance issues of fetching data from the database and also to take advantage of the HANA power.

Let’s keep up with innovations!

I would like to share with you some of the basic information we need to have about AMDP.

ABAP Managed Database Procedures (AMDP)  was introduced in Release 7.40, SP05, allowing developers to create their software and write directly in ABAP. Database procedure, but fully managed on ABAP.

After 7.40 was released, some concepts that we are all familiar with started to become widespread. ‘Code Push Down’ is one of them. We know how to get the records using CDS Views and calculate on the ABAP layer. The aim here is to perform all transactions at the database layer, rather than moving all raw records to the ABAP layer. But it is normal to encounter some restrictions when using CDS views. For instance, in AMDP we can call one function inside another, it helps in returning multiple results set on complex logic. Whereas CDS is reserved for a single set of logic and returns only one result set. We can use the CDS view to read and process data in the database layer. AMDP, on the other hand, can be used to both read and even process data from the database layer and modify it. The main difference here is AMDP provides you to write using SQL Script in the database layer and OpenSQL provides writing in the ABAP layer.

All in all, it seems easier for us to write the new syntax code that came with 7.40 using OpenSQL in SE38. But here it will be useful to know the differences and to know which one to use according to the needs.

What do we need to know?

◉ AMDP can be created in an eclipse-based development environment.

◉ SQL Script is a database language that is easy to understand and code. After coding logic inside the AMDP method, you can consume it in ABAP report.

◉ SQL Script syntax check and debugging are also integrated into Abap debugging.

◉ Possible to open an AMDP in sap gui, but you are not able to edit.

Let’s not just learn theory, now I will tell you the steps we need to follow when creating an AMDP.

1. We have an ABAP program which will call the method of global class.

2. The method consists of DB-specific script.

3. When we use -By Database Procedure- it came to stored procedure database.

4. Stored Procedures are automatically deployed on the Hana DB.

Getting started

AMDPs are created as a method of global classes. You can create the AMDP class as static or instance in any visibility section. It can be either public, private, protected.

We have two structures; class definition and class implementation just like we did in ABAP program. Under the definition, we have all data types and definitions.  And the implementation we have a database procedure we’re going to write. A very important point is here the interface.

In order to connect your Hana DB and AMDP we must give the marker interface:

IF_AMDP_MARKER_HDB

We can create our class method for AMDP using exporting, importing, changing parameters. Returning parameter is not allowed.

In methods: Our AMDP method parameters must be pass by value. Then parameters must be scalar types or table types. Scalar types are integer, char, etc.

The AMDP method must use the “BY DATABASE PROCEDURE FOR HDB LANGUAGE SQL SCRIPT” after its name in the class implementation section. It means that we’re going to write a method here executed by database procedure for Hana DB.

METHOD meth BY DATABASE PROCEDURE|FUNCTION

FOR <DB>

LANGUAGE <DB Language>

            OPTIONS <DB Options>

USING   <DB Entities>

Here, DB defines the database system for which the AMDP method is used.

DB language defines the database-specific language in which the AMDP is implemented.

DB Options specifies database-specific options for the AMDP procedure or function.

DB Entities declares the database objects managed by ABAP accessed in the AMDP procedure or function.

What we should pay attention to:

In the following example, I have created a basic database procedure structure using SQLScript which selects the document id and record number.

As you see here we don’t need to create an internal table, we can fetch data like :

result_tab = select * from itab_name

◉ Define interface, table type, and AMDP method by specifying the parameters in the definition section.

◉ Implementation part for the method we created.

◉ Internal table where you don’t have to do any additional declaration anymore.

So how do we consume it?

Now, we believe we have the data in our table and want to analyze it.

In order to do that, create an ABAP program and call the method in program using the class name. Take the importing parameter and pass it to the method’s parameter.

Let’s quickly observe the result by using salv in the ABAP program we created.

Activating AMDP Debugger

This time let’s write a simple code block worth debugging using two selects.

CLASS zcl_amdp_demo02 DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_data,

             matnr TYPE mara-matnr,

             maktx TYPE makt-maktx,

             ersda TYPE mara-ersda,

             brgew TYPE mara-brgew,

           END OF ty_data,

           tt_data TYPE TABLE OF ty_data.

    CLASS-METHODS amdp_conversion IMPORTING VALUE(iv_data)  TYPE mara-matnr

                                  EXPORTING VALUE(et_value) TYPE tt_data.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.

CLASS zcl_amdp_demo02 IMPLEMENTATION.

  METHOD amdp_conversion BY DATABASE PROCEDURE FOR HDB

                                     LANGUAGE SQLSCRIPT

                                     OPTIONS READ-ONLY

                                     USING mara

                                     makt.

    et_value = select top 2 distinct

    mara.matnr,

    makt.maktx,

    mara.ersda,

    mara.brgew from mara

    left outer join makt on makt.matnr = mara.matnr and

                            makt.spras = 'E' ;

    et_value2 = select

    mara.matnr,

    makt.maktx,

    CASE when mara.ersda >= 22102021

    then 'FUTURE'

    else 'PAST' end as ersda,

    mara.brgew from mara

    left outer join makt on makt.matnr = mara.matnr and

                            makt.spras = 'E'

                            where mara.matnr = :iv_data ;

  ENDMETHOD.

ENDCLASS.

Eclipse already marks the lines where you can break. Right-click on ‘Restart AMDP Debugger’.

At this point, you can control in 2 ways.

◉ I can’t give up the sap gui, I want to debug from there.

◉ Or you can continue with eclipse.

Let’s open the sap gui and put a debug in the program we created.

When we execute it, the class displayed what we created on eclipse. Here you can check your parameters.

Let’s put a breakpoint in the program in another way.

To make a long story short, everyone has different preferences.

Conversions of SELECT-OPTIONS into a Where Clause

In the examples above, all selection screen items were parameters. Using parameters in the AMDP method is simple. Now we’re going to pass SELECT OPTION to the AMDP method and use them. Remember that we cannot use select options directly to AMDP Methods. This is a limitation of AMDP.

We must select the data from the database and then apply the filter using the APPLY_FILTER function.

Let’s see how it is.

Defining select-options within the program.

And implementing it with an apply_filter. This is similar to how we use a where condition in a select query.

You’ve got it.

Source: sap.com