AMDP stands for ABAP Managed Database Procedure
An ABAP programmer can create a HANA database procedure (SQLScript code block accepting parameters) without directly interfering with SAP HANA database.
This procedure has an ABAP Data Dictionary entry as a DDIC object and can be transported using TMS transport requests.
AMDP procedures and AMDP class methods can only be created and developed in SAP HANA Studio.
Demo 1: Creating AMDP Procedure using SAP HANA Studio with ABAP
Step 1: Create ABAP class in SAP HANA Studio
Finish.
The default ABAP code for our new class will be displayed and ready to be edited.
This class is not an AMDP class yet. It is still an ordinary ABAP class definition.
Step 2: In order to convert an ABAP class into an AMDP procedure class, the interface if_amdp_marker_hdb interface must be implemented as follows:
This interface defines the class as an AMDP class, allowing you to implement AMDP methods
that is, ABAP methods that call a SAP HANA database procedure from within a global ABAP
class.
Step 2: Create AMDP method.
The AMDP methods must be passed by value instead of by reference, it is to avoid the procedure ends with error and reference parameter is changed unexpectedly.
A red mark appears the left side of the code editor indicating that the implementation is missing for this new AMDP method.
Click on the red mark >> Click on Add implementation for new method.
Now you can activate your AMDP class.
Step 3: Implement AMDP method
Add the following code lines into your AMDP method. Please note that the HANA database tables used are listed after “using” phrase.
Now you have the following error: “The body of a database procedure cannot be empty”.
Add the following SQLScript in your method implementation.
This SQLScript statement reads BUTXT field into company_name parameter from T001
database table for the record that has BUKRS field value is equal to company_code parameter.
Parameter in SELECT statement
If a parameter is used as a comparison value in WHERE clause, developers should add “:” in front of the parameter name.
If developers want to assign a value to the parameter, the target parameter will be placed in double quote sign “” and written in capital letters.
This is our first AMDP class with one method using parameters.
Comment in SQLScript
For commenting code, “--“ is used at the beginning of a related line.
Step 4: Create a sample ABAP program that can consume HANA AMDP class methods
Enter name and description of ABAP program.
Enter the following lines of code into your program.
In CATCH section of TRY-CATCH block, if any problem occurs in HANA database procedure,
the AMDP class returns an exception where the ABAP programmer can get details of the error.
Demo 2: AMDP Procedure + CRUD
Step 1: Implement AMDP class
Remember to include raising exception part in AMDP class definition for catching error which
may happen during modifying database table process.
Step 2: Create Program to test
Demo 3: Analyze AMDP performance
Copy database table into ZTABLE
Add columns into table ZSNWD_SO_I that you are going to use AMDP and ABAP program
update for the performance comparision.
Step 1: Create AMDP class
if_amdp_marker_hdb defines the class as an AMDP class, allowing you to implement AMDP methods - that is, ABAP methods that call a SAP HANA database procedure from within a global ABAP class.
if_oo_adt_classrun allows you to output the results to the ABAP Console.
Step 2: Execute AMDP class
Enter your values for parameters>>Run
Step 3: Analyze AMDP Profiling
Select method to execute
Input value for parameters>>Run
After the execution is finished, go to ABAP Profiling Perspective
Go to ABAP Traces tab>>Refresh Trace Entry>>Double click on your performed Trace Entry
Refer to the following link to work around different tools in AMDP Profiling.
https://archer4sap.com/2018/02/04/abap-profiling-with-eclipse-adt/
Step 4: ABAP program
Result:
According to the above summary, my program had to be terminated due to the excess of runtime and the necessity of workspace for other users.
I was trying to reduce the number of records that need to be selected from database. The ABAP program was tested with three different inputs of number of records (100,000 records, 500,000 records, and 1,000,000 records). The following image shows the result of runtime measurement for these three test cases.
Step 5: Compare ABAP program’s runtime with AMDP class’s runtime
Several lines of code need being added into our AMDP class in order to measure the runtime of the class.
The date time function nano100_between calculates the interval between two point of time. The result is measured in nanoseconds, hence you need to multiply it to 0.001 to get the new result in microseconds.
The following image shows three test cases that I implemented for my AMDP class.
As you can see from the summary, AMDP class enables us to update the whole database table which caused dump if the normal ABAP program is being processed.
Solution: Use parameters to pass in the number of records into your program
Creating AMDP Function using SAP HANA Studio with ABAP
Refer to CDS Table Function’s document and source code.
REFERENCE:
http://www.kodyaz.com/sap-abap/create-amdp-procedure-with-sap-hana-studio-in-abap.aspx
https://archer4sap.com/2018/02/04/abap-profiling-with-eclipse-adt/
https://sapstudent.com/hana/sql-datetime-functions-in-sap-hana
