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.
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.
CLASS zcl_sapspot_po_amdp DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
* TYPEs here
TYPES: BEGIN OF lty_po_data,
* field1,
* field2,
END OF lty_po_data.
* AMDP Method
METHODS get_po_data
IMPORTING VALUE(ip_client) TYPE mandt
VALUE(ip_lifnr) TYPE s_lifnr
EXPORTING VALUE(ex_po_data) TYPE lty_po_data.
* Non AMDP Method
METHODS display_po_data
IMPORTING ex_po_data TYPE type lty_po_data.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.CLASS zcl_sapspot_po_amdp IMPLEMENTATION.
* AMDP Method
METHOD get_po_data BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING ekko ekpo.
* Logic to Select/Join/Loop etc to populate ex_po_data
* ex_po_data = logic here
ENDMETHOD.
* Non-AMDP Method
METHOD display_po_data.
* Logic to display ex_po_data
* ALV Call
ENDMETHOD.
ENDCLASS.Let us join the dots better.
The class “zcl_sapspot_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.
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.
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.
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:
- RETURNING parameters cannot be used. When you can have IMPORTING/EXPORTING/CHANGING parameters, who cares for RETURNING parameters. Right?
- Parameters have to be passed by VALUE.
- 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.
- If ABAP Dictionary structures are used for typing, the method cannot be implemented as an AMDP.
- 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 :)).
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.
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.
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.
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.
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.
Let us check how we can call the custom AMDP Class in our custom ABAP Program.
REPORT zmm_tcode_role_report NO STANDARD PAGE HEADING
LINE-COUNT 132.
*--------------------------------------------------------------------*
* DATA DECLARATION
*--------------------------------------------------------------------*
* Inline data declaration for the AMDP Class Instance
DATA(lr_data) = NEW zcl_user_role_amdp( ).
*--------------------------------------------------------------------*
* SELECTION SCREEN
*--------------------------------------------------------------------*
SELECTION-SCREEN: BEGIN OF BLOCK block1 WITH FRAME TITLE text-t01.
PARAMETERS p_tcode TYPE tcode.
SELECTION-SCREEN: END OF BLOCK block1.
*--------------------------------------------------------------------*
* INITIALIZATION.
*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
* START-OF-SELECTION.
*--------------------------------------------------------------------*
START-OF-SELECTION.
* Calling the AMDP method to get the data
CALL METHOD lr_data->get_t_code_role_matrix
EXPORTING
ip_tcode = p_tcode
ip_object = 'S_TCODE'
ip_langu = sy-langu
ip_line = '00000'
IMPORTING
ex_it_tcode_role = DATA(it_tcode_role).
*--------------------------------------------------------------------*
* If you are in ABAP 740 and SP 5 and above but still not in HANA,
* You can connect from Eclipse/HANA Studio and create AMDP but
* cannot execute in database layer. You can try below code for
* normal Class-Method call.
*--------------------------------------------------------------------*
** Normal method call at AS ABAP Layer
* CALL METHOD lr_data->get_t_code_role_matrix_nonamdp
* EXPORTING
* ip_tcode = p_tcode
* ip_object = 'S_TCODE'
* ip_langu = sy-langu
* ip_line = '00000'
* IMPORTING
* ex_it_tcode_role = DATA(it_tcode_role).
*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
* END-OF-SELECTION.
*--------------------------------------------------------------------*
END-OF-SELECTION.
* Publishing the data in an output
cl_demo_output=>display_data(
EXPORTING
value = it_tcode_role
name = 'AMDP Usage to display the TCode and Role' ).Let us test our custom program and AMDP usage.
Provide the T-Code as the Input.
The output shows two Roles. The program uses AMDP Method.
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.
