ABAP Managed Database Procedure- Data Preview

What is AMDP ?

ABAP managed database procedure is a simple ABAP class method containing database-specific procedure coding. The code within the method is pushed to the database layer and executed within the database. Thus significant performance improvements of data-intensive processes can be achieved by code push-down from the application server to the database server. This reduces the number of data transfers and the amount of transferred data between both servers.

Need for AMDP

With SAP NetWeaver 7.40 SP02 AS ABAP, there is a possibility to access so-called Stored Database Procedures defined in SAP HANA via Database Procedure Proxies from the ABAP application server. From a technical point of view, a stored procedure is written in SQLScript in SAP HANA and the corresponding database procedure proxy (or proxies) can be generated in the ABAP server based on the interface definition of the database procedure (Bottom-Up approach). The call of the procedure proxy in ABAP triggers the procedure execution in SAP HANA. On the other side, the disadvantage of having a mix of ABAP coding and database-specific coding is that they are managed by different lifecycle management tools (ABAP and HANA), which need to be synchronized manually.

In contrast to this Bottom-Up approach, a Top-Down approach solves these synchronization issues, because the database procedure coding is embedded within ABAP code, which is managed by the ABAP lifecycle management tools and is executed at runtime on the database. Several technical realizations with ABAP Database Connectivity (ADBC) and EXEC SQL are available since a longer time, but they are not feasible to use in case of more complex scripts. With SAP NetWeaver 7.40 SP05 AS ABAP there is a new way available to implement database procedures within the ABAP stack as so-called ABAP Managed Database Procedures (AMDP). The procedure coding is implemented directly in method container defined in a global ABAP class. Therefore, the corresponding method needs to be marked as AMDP method in the implementation part of the class and the method body contains database-specific script language coding like SQLScript instead of ABAP code, which is also checked regarding syntactical correctness during implementation. Based on the script coding, a database procedure is generated within SAP HANA once the new or changed AMDP method is executed in the ABAP system (not at compile time).This allows the reuse of the ABAP transport infrastructure for these database procedures, which do not need to be transported via the SAP HANA transport infrastructure.

From a technical point of view, AMDP methods have to be implemented in ABAP in Eclipse development tool, exclusively. Furthermore, for each AMDP method exactly one database procedure is generated in the database. This allows for the protection of AMDP methods against unauthorized usage based on the ABAP package concept as for any other classical ABAP method. This is not possible for SAP HANA stored procedures, because several database procedure proxies can be created on the ABAP side to access a particular stored procedure.

Creating AMDP method

A class method can only be implemented as AMDP, if some prerequisites are fulfilled in the class definition as well as in the class implementation part as depicted in the following figures.

Thus an ABAP developer can write database procedure directly in an ABAP class and call of the procedure is call of the class method i.e., execution of AMDP method.

Syntax enhancement explanation:

• OPTIONS: The optional addition declares database specific options for a database procedure. Currently we support only the read-only option, which is mapped to the “reads sql data” security privilege.

• USING: All database objects that are used inside the method body, must be declared in advance. This declaration is needed for database tables, views, and other database procedures.

• Language: Currently SQLScript is supported but in future support for other languages of HANA (e.g, LLANG) or of other DB vendors is possible.

Step 2: Executing the AMDP method in the ABAP class. Right-click the AMDP method in the Project Explorer or in the Outline view. Select the Data Preview option from the context menu. Currently public AMDP methods support the Data Preview feature.The data preview feature is supported from SAP Netweaver 7.40 SP9 AS ABAP.

Step 3: An editor appears with an editable section between two non-editable sections. Non-Editable section contains the method signature definition. The editable area where code (glue code) for creation of constructor can be defined by the user. The exporting parameter values can also be changed in this section. The syntactic check for the code is done through the check button at the top of the editor.

Step 4: Change the exporting parameter value to 100.(It specifies the number of records to be fetched in this case).Press the Execute button on the toolbar or right-click on the editor area and select Execute from the context menu.

The preview tool is a simple tool to quickly see the results of the glue code after execution. The generated glue code would not have the complete code for object creation in case of AMDP classes with complex constructor signatures (with object references and deep structures). Nevertheless, we do not wish to restrict the usage to only static methods but rather expect the developer to fill in the appropriate object instantiation calls in the free-style glue code editor.