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.
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
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
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
Hi Jasmin - Thanks for sharing the HANA SQLScript official link..
Hakim
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.
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
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.
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.
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
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.
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.
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
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.
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.).
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.
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.
Updates to buffered tables are not supported, since the table buffer would become out of sync.
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
Hi,
Very usefull information
Thanks,
Avinash
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
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
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
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
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.
Hi Jens,
it is a very useful blog! Amazing!
Thanks & Best Regards, Olena
Good one. And probably the followers can go through the below blog as well.
Lets explore AMDP - What is happening ?
Hello Jens,
thanks a lot for the great content
Regards,
Shakul.
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
Hi Eli,
you have to enclose the table name in double quotes and it should be in uppercase.
regards,
Prabuddha
Hi Prabuddha
I am now getting an error saying "database procedure was closed unexpectedly"
Kind regards
HI Eli,
just replace / to _ in both data declaration as well as inside amdp, that will work.
-amol
Hi Amol
If I do that then it complains about an invalid object because table with an underscore does not exists
Kind regards
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
Hi Jasmin
Its an ABAP table starting with a '/' Please screenshot below
Kind regards
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
Hi
Goodness me! 🙂
Thank you
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
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..!!
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
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
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
Thanks Thomas and Chris.
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
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
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?
Hi Jens,
This is good and helpful content on AMDP. Can you please share the "AMDP debugging" Â article link.
Regards,
Narender
AMDP Debugging