Skip to Content
Author's profile photo Jens Weiler

ABAP Managed Database Procedures – Introduction

What are AMDPs…

ABAP Managed Database Procedures are a new feature in AS ABAP allowing developers to write database procedures directly in ABAP. You can think of a Database Procedure as a function stored and executed in the database. The implementation language varies from one database system to another. In SAP HANA it is SQL Script. Using AMDP allows developers to create and execute those database procedures in the ABAP environment using ABAP methods and ABAP data types. This video will show you more details…

AMDPs are introduced to easily enable you to get the best out of ABAP for HANA following the “code push-down” paradigm first introduced with SAP NetWeaver AS ABAP 7.4 last year. More information on this paradigm can be found here: ABAP for HANA and “Code Push-Down”.

… and why should I use them ?


As you can imagine (and hopefully have already experienced) calculations executed while retrieving the data from the database can significant reduce the runtime of an ABAP program. Especially when the calculations can be parallelized.

Using SQL and all of its possibilities is one example of this. But this has limitations. A SQL statement always has one result set and the calculation must be done in one single step. If you have complex calculations which you want to execute on the database you need other possibilities. In those cases and many more, database procedures come into the game. And the vehicle of choice to implement database procedures as an ABAP geek are AMDPs.

Sounds great – How do I create an AMDP ?


Creating an AMDP is as easy as it can get in ABAP. You can simply implement SQLScript in an ABAP method:

METHOD <meth> BY DATABASE PROCEDURE
    FOR <db>
    LANGUAGE <db_lang>
    [OPTIONS <db_options>]
    [USING <db_entities>].

< Insert SQLScript code here >

ENDMETHOD.

Also important to know is that every class containing an AMDP must contain the interface IF_AMDP_MARKER_HDB. But that’s all – You don’t need anything else. For more details on the syntax and the different options just take a look at our ABAP Documentation.

Enough talking – Show us an example !


Here we go – a very very simple example but it should give you an idea. A real life example is of course much more complex and normally has more in- and/or output parameters.

/wp-content/uploads/2014/01/amdp_simple_example_378121.png

In this example we have created a database procedure for SAP HANA (FOR HDB) using SQLScript (LANGUAGE SQLSCRIPT) which simply selects the company name for our top business partners. The number of business partners to be selected is passed as an input parameter (iv_number) which is used in the SQL Script statement.


Carine has also prepared a video with a little more complex example for you:

OK great, but what do I need to use AMDPs ?

AMDPs are available with SAP NetWeaver AS ABAP 7.4 SP5. Currently AMDPs are only available with SAP HANA as primary persistence and can only be written in SQLScript.

To create an AMDP you need to use at least ABAP Development Tools in Eclipse 2.19 ( see Get ABAP Development Tools 2.19 and find out what’s new). By the way it is of course still possible to open an AMDP with se80 /se24, but you won’t be able to edit it,

All set and ready but are there any limitations ?

Yes, there are some limitations, but only based on the parameters:

The parameters have to meet the following prerequisites:

  • Exporting, importing and changing parameters are allowed
  • Methods with returning parameters cannot be implemented as AMDPs
  • Method parameters have to be tables or scalar types
  • Method parameters have to be passed as values

Come on – I want to know more…

As already mentioned we will continue with more technical deep dives (e.g. debugging an AMDP) and a more complex example to show you more benefits of AMDPs.

I hope you enjoyed this introduction and stay tuned

Jens

Assigned Tags

      37 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Abdul Hakim
      Abdul Hakim

      Hi Jens-Thanks for sharing another wonderful blog with the ABAP Community..Can u please share any starting point to master HANA SQLScript for ABAP Developers?

       

      Thanks

      Hakim

      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi Abdul,

       

      for a comprehensive starting point to for SQLScript, see the HANA SQLScript reference http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf.

       

      Short remark: ABAP Managed Database Procedures currently only provide the option to create a database procedure on SAP HANA (HDB) using SQLScript (language), but conceptually also allow for other databases and other languages - as indicated by "BY DATABASE PROCEDURE FOR db LANGUAGE db_lang".

       

      Cheers,

        Jasmin

      Author's profile photo Abdul Hakim
      Abdul Hakim

      Hi Jasmin - Thanks for sharing the HANA SQLScript official link..

       

      Hakim

      Author's profile photo Former Member
      Former Member

      Hi Jasmin,

            Can all the operations specified in this HANA SQLScript reference document be used in an AMDP method as is it with the same syntax. I tried the insert statement and i was getting an error.

      Author's profile photo Manjunath Baburao
      Manjunath Baburao

      Hello jhansi,

       

      Have you defined the ABAP class method as READ ONLY by chance? Do you have insert rights on the table?

       

      Please check.

       

      Regards,

      Manju

      Author's profile photo Former Member
      Former Member

      Hi Manju,

       

      Thanks for the response.

      1)I did not include the options clause itself. So, that could be an issue..

         Is there any default option that the system takes itself when in case it is not mentioned by    the user externally?

      2)I tried inserting record into internal table and export parameter. Used the same insert syntax as in the document.

       

      Regards,

      Jhansi.

      Author's profile photo Former Member
      Former Member

      Hello...

       

      Can I use CE_COLUMN_TABLE funcion inside the AMDP SQLScript method?

      If so, what's the correct syntax? I've tried several ways but nothing has passed the check.

       

      Thank you!

       

      João Otto.

       

       

      Author's profile photo Former Member
      Former Member

      Hello Otto,

       

      As of now, you can not use CE_COLUMN_TABLE in AMDP as it is one of the known limitations from AMDP.

       

      FYI, i am listing down some more limitations from AMDP.

       

      Limitations in the Procedure Defiinition

      • Naming.
        AMDP creates DB procedures with a special naming convention which is derived from the ABAP definition. It is not possible to create DB procedures with a procedure name or parameter names. Also usage of camel case is not possible.
      • Usable data types.
        The HANA type system is not completely covered by the ABAP stack. Since the procedure definition is derived from ABAP types, it is not possible to create DB procedures that contain HANA data types which are not supprted by the ABAP stack, i.e. DATE, TIME, SECONDDATE, TIMESTAMP, TINYINT, BIGINT, SMALLDECIMAL, DECIMAL, REAL, VARCHAR, ALPHANUM, SHORTTEXT, TEXT.
      • Result views.
        Database procedures can be defined with a result view. This concept is not supported with AMDP and will later be supported with the support of table functions.

        

      Limitations in the Procedure Code

      • No DDL Statements allowed
        DDL statements for creation of DB tables or views in the default schema are not supported. It is recommended to use the ordinary DDIC functionality instead.
      • No transaction control statements allowed.
        The SQL statements COMMIT and ROLLBACK are (currently) not supported directly inside SQLScript database procedures.
        It is still possible to issue a commit via the EXEC statement. It has to be made clear, that the transaction management should be done from ABAP to ensure transactional consistency (e.g. triggering of follow up processing like update tasks, etc.).
      • Only access to ABAP managed DB artifacts in default schema.
        Within AMDPs only DB objects of the own/default schema can be accessed, that are ABAP managed, i.e. AMDPs or DDIC tables/views/CDS views.
      • Some calc engine functions (e.g. CE_COLUMN_TABLE) are not usable.
        The calc engine function CE_COLUMN_TABLE only works on column store tables and not on views. This is a major problem, since AMDPs or LM tools like SUM/DMO replace the table access with a view-based access.
        All calc engine functions which cannot operate on views are not supported. This is the case for CE_COLUMN_TABLE, which can simply be substituted with a SQL statement.
      • No DB changes of buffered tables.
        Updates to buffered tables are not supported, since the table buffer would become out of sync.
      • Only ASCII printable characters allowed.
        The usage of non ASCII printable characters in the source code has indicated several problems with the syntax-check, error positioning and procedure generation. It is strongly recommended to avoid the problematic characters. The first occurrence of such a character is indicated with a warning.

       

      Thanks & Regards

      Prakash K

      Author's profile photo Avinash D M
      Avinash D M

      Hi,

       

      Very usefull information

       

      Thanks,

      Avinash

      Author's profile photo Former Member
      Former Member

      Hello...

       

      I created an AMDP class method in ABAP system and executed it in report. It created the DB procedure in the Hana system. When made some changes in the method and activated the class in ABAP system, the DB proc. in Hana catalog disappeared.


      Again when I executed the report the proc. got created in the Hana system catalog, which is fine.


      My question is that if I make any changes to AMDP method in ABAP system, the same already existing proc. in the Hana catalog shall disappear or not.


      Best Regards

      Kunal

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Hi Kunal,

      why do care whether the existing proc in the catalog disappears or not?

      As long as there is the correct procedure there when it gets executed, I do not mind what was before.

      Best Regards, Thomas

      Author's profile photo Former Member
      Former Member

      Hi Thomas

       

      I thought exactly the same way initially that why should I care if proc is not shown in catalog.

      However, when I thought of a scenario where I executed the AMDP method and created the proc, and same is displayed in catalog, and to be used elsewhere.

      Now if I change the AMDP method again and activate, but didnt executed it (no new proc instance is created). Now if user wants to use the earlier proc instance, he cannot do that as its not in the catalog.

       

      As a user I want the older instance to be available, at the same time if I want the new proc instance, I can always get it by executing the AMDP method.

       

      Actually, I am looking it from a tester perspective, not from dev perspective.

       

      Best Regards

      Kunal

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Hi Kunal,

       

      I am not sure, if I understand what you want to do:

       

      When you use the AMDP from elsewhere in ABAP (even in test code or another AMDP) it will be generated just in time. You do not need to take care.

      If you want to use the AMDP from elsewhere directly in HANA you will anyway get into deep trouble with your life cycle management because ABAP and HANA artefacts might be transported out of sync.

       

      I also do not understand why you want still access to the old version, when a new version is already existing.

      Can you explain what you have in mind?

       

      Best Regards, Thomas

      Author's profile photo kyo choi
      kyo choi

      Here's an example of how you would call the method,

      REPORT Z2.
      PARAMETERS pnumber TYPE i DEFAULT 10.

      DATA: lv_number TYPE i.
      DATA: ZCL_DEMO_AMDP_I TYPE REF TO ZCL_DEMO_AMDP.
      * set the value of the procedure input parameter
      lv_number = pnumber.
      * call AMDP methods
      CREATE OBJECT ZCL_DEMO_AMDP_I.

      CALL METHOD ZCL_DEMO_AMDP_I->MY_METHOD
      EXPORTING
      IV_NUMBER = LV_NUMBER
      IMPORTING
      ET_TOP = DATA(LT_TOP)
      .

      .
      * display the returned itab with TOP customers
      WRITE: / 'Best customers:' COLOR COL_POSITIVE.
      LOOP AT lt_top ASSIGNING FIELD-SYMBOL(<f>).
      WRITE:/ <f>-company_name .
      ENDLOOP.
      * display the returned itab with FLOP customers
      ULINE.

      Author's profile photo Olena Demeter
      Olena Demeter

      Hi Jens,

       

      it is a very useful blog! Amazing!

       

      Thanks & Best Regards, Olena

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai

      Good one. And probably the followers can go through the below blog as well.

      Lets explore AMDP - What is happening ?

      Author's profile photo Shakul Jugran
      Shakul Jugran

      Hello Jens,

       

      thanks a lot for the great content

       

      Regards,

      Shakul.

      Author's profile photo Timothy Muchena
      Timothy Muchena

      Hi

       

      I have a table which starts with a forward slash '/' and its giving an error inside an AMDP class. How do I get around this error?

       

            INCORRECT SYNTAX NEAR "/"

       

      Kind regards

      Author's profile photo Prabuddha Raj
      Prabuddha Raj

      Hi Eli,
      you have to enclose the table name in double quotes and it should be in uppercase.
      regards,
      Prabuddha

      Author's profile photo Timothy Muchena
      Timothy Muchena

      Hi Prabuddha

       

      I am now getting an error saying "database procedure was closed unexpectedly"

       

      Kind regards

      Author's profile photo Amol Samte
      Amol Samte

      HI Eli,

       

      just replace / to _ in both data declaration as well as inside amdp, that will work.

       

      -amol

      Author's profile photo Timothy Muchena
      Timothy Muchena

      Hi Amol

       

      If I do that then it complains about an invalid object because table with an underscore does not exists

       

      Kind regards

      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi,
      are you talking about a table starting with '/' or are you using a namespace? Is it an ABAP table or a native HANA table? Can you please show a short coding snippet (including the line before and after the syntax error)?
      Cheers,
        Jasmin

      Author's profile photo Timothy Muchena
      Timothy Muchena

      Hi Jasmin

       

      Its an ABAP table starting with a '/' Please screenshot below

       

      amdp error.PNG

       

      Kind regards

      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi,
      please enclose the table name with ", i.e.
      SELECT ... FROM "/POSWD/FLOGF" and the statement should be closed with ";" instead of the period as it's SQLScript rather than ABAP ;-).
      Cheers,
        Jasmin

      Author's profile photo Timothy Muchena
      Timothy Muchena

      Hi

       

      Goodness me! 🙂

       

      Thank you

      Author's profile photo Matthias Heiler
      Matthias Heiler

      what also is worth to mention: with AMDP you gain the possibility to use the ABAP based authorizations, i.e. you don't have to create a separate HANA DB user and gran rights to him. And you also gain the lifecycle management capabilities of an ABAP stack. So you guarantee that changes in ABAP and HANA gets transported together.

      Both are hugh advantages compared to a native HANA XS implementation. (But HANA XS has of course other benefits and not always you implement something on HANA from an SAP perspective dor example if you are a start-up and build non-SAP apps.)

      Jasmin could you elaborate a little bit more on that?

       

      Best regards

      Matthias

      Author's profile photo Former Member
      Former Member

      Hi Jens Weiler,

       

      Thanks for such a lucid document.

       

      I have just a small question, here we are forced to mention the database used, so today I am usinh HANA but because of any reason after an year I have to use some other DB, then how will my AMDP will work ?

       

      Thanking You All..!!

      Author's profile photo Former Member
      Former Member

      Hi,

       

      Does anybody know, in AMDP if I am using a HANA calculation view how should the name of the view should be written in AMDP as It is not allowing the HANA view name?

       

      Thanks,

      Deepika

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Hi Deepika,

      it is like in SQL Console e.g.:

      SELECT ... FROM "_SYS_BIC"."sap.bc.epm.oia.dfg/AN_OPEN_INVOICE_AMOUNT"

       

      You can see an example in class CL_OIA_CTG_AMDP_PRVDR.

      Regards, Thomas

      Author's profile photo Christiaan Edward Swanepoel
      Christiaan Edward Swanepoel

      Hi Deepika,

       

      Just a short word of caution:

       

      If you are implementing an AMDP, it is advisable to only use other ABAP Managed objects (ABAP CDS [SQL] Views, DDIC tables, DDIC views, other AMDPs) in your AMDP, wherever possible. This simplifies the lifecycle management of the objects.

       

      If your AMDPs have dependencies to objects which are not managed by the ABAP Change and Transport System (CTS) , then you have to ensure that these objects are transported together with your AMDP and exist in the system before your AMDP is executed. This can become complicated.

       

      Regards

      Chris

      Author's profile photo Former Member
      Former Member

      Thanks Thomas and Chris.

      Author's profile photo Jose Sanchez
      Jose Sanchez

      Hi Jens!

       

      I have a couple of questions.

       

      One of them would be: Is there a equivalent to the ABAP "WHERE xxx IN" clause in AMDP? I've been looking for a way to create a query using something similar to ABAP Range type.

       

      The other one is: Do you have any reference link to a tutorial about using cursors in AMDP?

       

      Thank you in advance

      Author's profile photo Rajarshi Muhuri
      Rajarshi Muhuri

      I am using AMDP for replacing FOX FORMULA planning functions in SAP BPC .

      I was wondering if SAP will change the AMDP parameters to PASS by REFERENCE instead of PASS BY VALUE .

      I would think that PASS by REFERENCE would be better for performance

      Author's profile photo Former Member
      Former Member

      CDS view can’t read custom enhancements, I think we can call BAPI within the AMDP to read the results which processed the custom enhancements.   But then AMDP cannot be used as Transient provider in BOBJ so how can we do the reporting correctly?

      Author's profile photo Former Member
      Former Member

      Hi Jens,

      This is good and helpful content on AMDP. Can you please share the "AMDP debugging"  article link.

      Regards,

      Narender

       

      Author's profile photo Armin Beil
      Armin Beil

      AMDP Debugging