Skip to Content

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

To report this post you need to login first.

35 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
    1. 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

      (0) 
      1. jhansirani j

        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.

        (0) 
          1. jhansirani j

            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.

            (0) 
  2. João Otto Gethmann Junior

    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.

     

     

    (0) 
    1. K Prakash

      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

      (1) 
  3. Kunal Shrouty

    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

    (0) 
    1. 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

      (0) 
      1. Kunal Shrouty

        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

        (0) 
        1. 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

          (0) 
  4. 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

    (0) 
    1. 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

      (0) 
        1. 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

          (0) 
  5. 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

    (0) 
  6. Ankit Sharma

    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..!!

    (0) 
  7. Deepika Parmar

    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

    (0) 
    1. 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

      (0) 
    2. 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

      (0) 
  8. 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

    (0) 
  9. 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

    (0) 
  10. Jonathan Choi

    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?

    (0) 

Leave a Reply