ABAP on SAP HANA. Part IX. AMDP – ABAP Managed Database Procedure

AMDP - ABAP Managed Database Procedures
Share on Facebook19Share on LinkedIn43Tweet about this on TwitterShare on Google+0
Please Share!!

AMDP – ABAP Managed Database Procedure

ABAP Managed Database Procedures or AMDP. Another jargon. Does it sound bombastic? I was scared when I heard it for the first time. 🙂 . But when you ponder a little deeper, the concept is in the name itself. AMDP is Database Procedure which is Managed by ABAP. It is not a database thing. It is governed and managed by ABAP. So, ABAPers are bound to love and use it to the fullest. 🙂 Like CDS Views, only ABAP transports (ABAP Class/Method) of AMDP needs to be transported and we need not worry about the corresponding underlying HANA artifacts. Both CDS and AMDP fall in Top-Down Approach of HANA, which are recommended by SAP.

We need to be in ABAP system which is on release 7.4 SP05 and higher and HANA is the primary database. By now you have guessed correctly, AMDP works only with HANA as the primary database. But AMDP is conceptually designed to work in any database and any language. This is clear from the way we define the AMDP Method. We need to let the Method know the database and language. For HANA, the database is HDB and language is SQLScript.

SAP Document says: Currently, AMDP only supports database procedures from the SAP HANA database. But in principle, however, AMDP is designed so that stored procedures from other database systems can also be supported.

AMDP can detect Database independent syntax errors; HANA specific syntax error and SQL Script errors.

Parameters not passed by value, wrong parameter types etc are database independent issues. Type mapping checks or wrong default values are HANA specific errors.

Did you Read? SAP HANA for Beginners from a Beginner?

Still scared of this AMDP bomb?

Let us make it more simple. All children who wear the same school uniform belong to one school. One of those students has a special batch on his/her shirt. He is identified as the Head Boy/Girl. They have access to all rooms/areas like any other students and also they have special keys with which they can enter the areas/rooms which are prohibited for other students.

Did I sound crazy? 🙂

Let us co-relate the above example with SAP ABAP. All students = CLASS. If a Class has Marker Interface “IF_AMDP_MARKER_HDB” (student batch) then it is an AMDP class (head boy/girl). If one or more METHOD of AMDP class has the keyword “BY DATABASE PROCEDURE” (special key for head boy/girl), then it is AMDP method.

That’s it. You now know that any class which has a marker interface “IF_AMDP_MARKER_HDB” and one of its method has the keyword “BY DATABASE PROCEDURE” is an AMDP class. Period!!

Let us check how an AMDP Class and Method looks in the real scenario.

Let us join the dots better.

The class “zcl_sapyard_po_amdp” depicted in the figure below is a global class (can view it in SE24) and has the interface marker tag: if_amdp_marker_hdb. Theoretically, there can be more that one “if_amdp_marker_XXX” tag with suffix “XXX” indicating the database system for which the AMDPs (Database Procedures) can be implemented in AMDP methods of the AMDP class.

Looking at the interface marker tag (last three letters), it makes us believe that AMDP is not HANA database specific as it has provision to include other databases. But for now, let us concentrate only for HDB and wait for further releases and documentations from SAP where they show AMDP for non-HANA. Why will they do that? 🙂

In the public section of the class definition add the mandatory interface “if_amdp_marker_hdb”. You can have your own data definitions (TYPES, CONSTANTS, DATA etc.) and Methods as well in this space. But we must have one method which will be an AMDP Method. This so-called AMDP method can have some importing parameter(s) and exporting table output(s). But both should be VALUE reference only.

IF_AMDP_MARKER_HDB

Looking at the Class DEFINITION, we can guess that the method “get_po_data” can be an AMDP method as it meets the pre-requisite of passing all parameters by VALUE. But, just by looking the definition, we cannot say for sure if it really is an AMDP method. However, we can say for sure that the second method “display_po_data” is NOT an AMDP method as it does not meet the basic requirement of passing by VALUE.

To confirm, if the method “get_po_data” is really an AMDP method, we need to look at the IMPLEMENTATION. In the implementation, if you find the keyword “BY DATABASE PROCEDURE”, it is AMDP method.

Look the figure below for more clarity on what we spoke above.

BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

What is the motivation behind AMDP?
Answer: Stored Procedures have been supported by all databases and they can be called and created using ABAP code. Native SQL was the method to consume Stored Procedures before ABAP 7.4. Now we can use ADBC as it has better advantage (OOPs, where-used analysis, exception handling etc) than direct Native SQL call.

Read more about ADBC – ABAP DataBase Connectivity.

ADBC can be Bottom Up and manage the complete lifecycle of the stored procedure outside the ABAP stack. We need to make sure, the stored procedure is deployed in all database systems and we need to take care of different ABAP database schema names and systems like development box, testing box, quality box, pre-production and production system.

ADBC can also be Top Down. Surprised!!! Yes, it can follow Top Down Approach. When we concatenate the native SQL statements in our own program and call the database and execute those SQL statements, it is Top Down. This removes the need for handling the database artifacts in each system of the landscape and all can be handled by the normal transport. But, do you think creating the complex stored procedure by concatenation strings in ABAP that easy? You might build native SQL code for simple selects and other normal stuff and build your program. But complex/actual project requirement is more than just DEMO program. 🙂 And most developer (ABAPers like me) are not familiar with native SQL (and database language) and ADBC still, lacks native SQL check during compile.

So, the motivation is crystal clear. With ADMP, the creation, modification, activation and transport are all handled at ABAP layer, i.e. stored procedure runtime objects on HDB is created and managed by AMDP in ABAP AS. Also, SQLScript source code is managed at ABAP AS by AMDP. SQLScript syntax check also happens in HDB (but not in another database), unlike ADBC.

What are the restrictions in AMDP Methods?
Answer:
1. RETURNING parameters cannot be used. When you can have IMPORTING/EXPORTING/CHANGING parameters, who cares for RETURNING parameters. Right? 🙂
2. Parameters have to be passed by VALUE.
3. Parameters can only be either Table or Scalar. That means, only variables, structures and simple internal tables can be passed. No deep structures, no complex tables (tables in a table) i.e. no nested tables.
4. If ABAP Dictionary structures are used for typing, the method cannot be implemented as an AMDP.
5. Whatever ABAP dictionary tables, views, other procedures etc you want to use in AMDP Method has to be declared while implementing using keyword USING (in the above figure, EKKO and EKPO are passed).

How are AMDP Methods called?
Answer: AMDP Method call is not special. They are called like any other normal class method. But AMDP methods are executed like static methods, even though they are defined as instance methods.

When does AMDP execute in underlying database?
Answer: When AMDP is executed, the ABAP Kernel calls the database procedure in the underlying database (SAP HANA).

AMDP makes the database procedure available at runtime in the database. Database procedures are created when they are called by AMDP for the first time. This is call Lazy Approach. Wiki says: “Lazy loading is a design pattern commonly used in computer programming to defer initialization of an object until the point at which it is needed“. JIT. right? Just In Time. 🙂

If we make any change in the source code of database procedure or any dependent objects, then the new version of the database procedure is created and old versions are deleted asynchronously (taking its own sweet time :)).

Before we proceed forward, let us refresh our CDS Concept in SAP HANA.

Where are AMDPs created?
Answer: From SAP NetWeaver 7.4 SPS 05 i.e. ABAP release 740 Service Pack Level 05, AMDP can be created in ABAP in Eclipse (Eclipse based environment i.e. ADT : ABAP Development Tool). We need to be in ABAP Perspective. We can view the class and methods in SE24 in ABAP workbench (GUI) but we cannot edit them in GUI. Although AMDPs are created in Eclipse, they are saved at the ABAP layer. So developers are concerned only with ABAP artifacts. No need to worry about database artifacts and system handling in different environments in the same landscape.

AMDPs are defined at ABAP layer but they are dependent on the underlying database so that they can optimize the database in use at the fullest. As they are database dependent, the implementation language differs based on the database. SQLScript is the implementation language for HDB so playing with AMDP in HDB is same as implementing SQLScript in our ABAP programs. In another database, the implementing language may not be SQLScript.

Check error message which we get when we try to edit AMDP Class.

AMDP class cannot be edited in GUI

Do you want an example of Standard SAP AMDP?
Answer: Check the standard class “CL_CS_BOM_AMDP” provided by SAP.

Go to t-code SE24.

Check the Interface tab. You will find “IF_AMDP_MARKER_HDB”. Makes the class AMDP.

AMDP example
Check the source code of methods “MAT_REVISION_LEVEL_SELECT”, “MAT_BOM_CALC_QUANTITY”, “MAT_DETERMINE_HEADER” etc. Keyword “BY DATABASE PROCEDURE FOR HDB” and “LANGUAGE SQLSCRIPT” is waiting for you.

AMDP class method keywords

Look at the IMPORTING and EXPORTING parameters. Passed by VALUE.

You might like to refer to this AMDP Class/Methods for some SQLScript, SELECTs, JOINs etc examples and usage.

Custom AMDP Class and Method and its usage in custom ABAP program

In your Eclipse environment / HANA Studio /ADT, go to ABAP Perspective. From the Menu, click on ABAP Class.

AMDP usage in custom ABAP Program

Provide the package name, Class name you want to create and description. Provide the Class Definition and Implementation. Do not forget to provide the Marker interface in the Public section of the Class Definition and the Keywords in the AMDP Method. The below example shows that both AMDP Method and non-AMDP Method can co-exist in AMDP Class.

Custom AMDP Class Usage

Let us check how we can call the custom AMDP Class in our custom ABAP Program.

Let us test our custom program and AMDP usage.

Provide the T-Code as the Input.

AMDP in SAP ABAP Program

The output shows two Roles. The program uses AMDP Method.

ABAP Managed Database Procedures

Find the above AMDP Class Method Code Snippet here.

Find the above Custom Program which consumes the AMDP here.

The above program and AMDP class use one Parameter as an input in the selection screen. Handling of Parameters are easy. In the next post, we would show how we can handle the SELECT-OPTIONS in AMDP.

What happens if we change the existing AMDP Method name?
Answer: The method name is automatically updated in the Class which we can see in the GUI.

AMDP change before and after

Hope this article is clear enough to demonstrate the concept and usage of AMDP in ABAP Program. In the next articles, we would show more examples of AMDP programs for different scenarios and also how to Debug them.

SAP ABAP for HANA Tutorial. Part X. How to use AMDP with SELECT OPTIONS

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Do you have anything to add to this article? Have you faced any issue using AMDP? Do you want to share any real project requirement or solutions? Please do not hold back. Please leave your thoughts in the comment section.

Thank you very much for your time!!

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial
9. SAPUI5 Tutorial

Call for Guest Authors and Contributors to write SAP Articles on our page and get noticed and also receive cool Gifts.

Do you have any tips or tricks to share? Do you want to write some articles at SAPYard? Please REGISTER and start posting and sharing your knowledge to the SAP world and get connected to your readers. Please check our simple guidelines for contributing your articles and receiving the gifts.

Share on Facebook19Share on LinkedIn43Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

SAP Yard
SAP Yard
SAPYard is one stop page for all Technical Folks in SAP. You would find un-conventional explanations, tutorials, and tricks. Please like our Facebook Page and also join our LinkedIn Group.

17 Comments on "ABAP on SAP HANA. Part IX. AMDP – ABAP Managed Database Procedure"

  1. Kindly Provide Tutorial Exception Handling on AMDP Method

  2. Best explanation I have read so far. Thank you!

    • Dear Wounky – Thank you very much for your kind words. We are glad you read our presentation and explanation.

      Please keep visiting and providing your constructive feedback.

      Regards,
      Team SAPYard.

  3. First of all congratz on the Site, is the first blog were someone can really integrate and get a clear idea of ABAP for HANA.

    Regarding SQL Script – AMDP. I found this in a SAP slide regarding SQL Script

    SELECT statements are executed in parallel unless:
    – Local scalar parameters and variables are used in the procedure
    – Read/Write procedures or DML/DDL operations are executed
    – Imperative logic is used within the procedure
    – SQL statements are used that are not assigned to a variable

    How are we suppose to accomplish “Code Pushdown” if we can´t use Imperative Logic. Parallel Processing is the most important feature regarding speed.

    Can we know if parallel processing was used ?

    • Dear Rodrigo,

      Thank you so much for your encouraging words.

      Please check the reply from Amol, an expert in ABAP on HANA:

      Hana procedure itself run parallely accordingly to load balancer. It divides into threads and its call of DB. Same goes with amdp( AMDP is a code push down technique which creates a procedure on HDB on a first run). Again we can take the advantage of application layer to process in parallel meanwhile same procedure runs on DB parallely as its load balancer’s call.

      Please feel free to add follow up questions, and we would try our best to answer it.

      Regards,
      Team SAPYard.

    • Dear Rodrigo,

      In addition to Amol, our other ABAP HANA expert, Mostafa added the below points. If you have further questions, please feel free to write here.

      You can achieve parallel processing by using select union approach, or you can use union inside Calculations view that is being used for select statement in the AMDP.

      Regards,
      Team SAPYard.

    • Dear Rodrigo,

      Another ABAP on HANA enthusiast Mohsin has the below answer for your query:

      1.we can use imperative logic in stored procedure/SQL Scripts

      2. Parallel process is one of the major features of HANA but there is no command /key word for this mutually exclusive SQL statements will be automatically be executed in parallel

      Regards,
      Team SAPYard.

  4. very nice..

  5. Very systematic and easy to understand. Thank you..!!!

  6. Srinath Acharya | September 29, 2016 at 4:00 am | Reply

    This is one of the best ‘wonderfully and lucidly presented’ article I have ever read. Described everything keeping its reader in mind in simple and friendly terms. Good job.

    • Thank you so much Srinath. We are glad you liked our way of presentation. Our aim is to be as simple as possible without being too out of context. We make sure that our articles are understood by the beginners and even non-technicals.

      Thank you again for your feedback. Please keep visiting and providing your valuable inputs.

      Regards,
      Team SAPYard.

  7. wow..!! you explained AMDP in super simple..absolute..flawless..!! Thank you for your hard work 🙂

Leave a comment

Your email address will not be published.


*