Skip to Content
Author's profile photo Peter Murphy

Under the HANA hood of an ABAP Managed Database Procedure

Hi All, I’ve been looking into ABAP managed database procedures for HANA recently and decided to take a look at what’s actually created under the hood in the HANA database when an AMDP is created.

I created a small test class in our CRM on HANA system with a method to read a couple of columns from the crmd_orderadm_h table using sqlscript. The method takes one input parameter IV_OBJECT_ID and has one export parameter ET_ORDER.


class ZCL_TEST_AMDP definition
  public
  final
  create public .
public section.
    interfaces IF_AMDP_MARKER_HDB.
    types:
        begin of ty_order,
            object_id TYPE crmd_orderadm_h-object_id,
            description TYPE crmd_orderadm_h-description,
        end of ty_order,
        tt_order TYPE STANDARD TABLE OF ty_order.
    methods get_orders_sql
        IMPORTING VALUE(iv_object_id) TYPE crmd_orderadm_h-object_id
        EXPORTING VALUE(et_order) TYPE tt_order.
protected section.
private section.
ENDCLASS.
CLASS ZCL_TEST_AMDP IMPLEMENTATION.
    method get_orders_sql
        by database procedure
        for hdb
        language sqlscript
        using crmd_orderadm_h.
      et_order = select object_id, description from crmd_orderadm_h where object_id = iv_object_id;
    endmethod.
ENDCLASS.




So pretty simple class. My expectation was that in HANA I would see a SQLScript procedure object and a Table Type object for the et_order parameter. What I did find was a bit different.

First of all it created 2 SQLScript procedures:

– a stub wrapper type procedure

– a main procedure called by the above stub procedure

It also created:

– a temporary table for the output data

– a view for the referenced table

The stub procedure seems to follow the naming convention of CLASSNAME=>METHODNAME#stub#DATETIME.

As you can see the definition includes only the input parameter that was defined in the class method. The export parameter is not specified in the definition.

In the body of the procedure it calls the main procedure and returns the output to the ET_ORDER parameter.


create procedure
  "ZCL_TEST_AMDP=>GET_ORDERS_SQL#stub#20140318165936"
(
  in    "IV_OBJECT_ID" NVARCHAR (000010)
)
language sqlscript sql security invoker  as begin
  call "ZCL_TEST_AMDP=>GET_ORDERS_SQL" (
    "IV_OBJECT_ID" => :IV_OBJECT_ID ,
    "ET_ORDER" => :ET_ORDER
  );
  select * from :ET_ORDER;
end;




The main procedure follows the naming convention CLASSNAME=>METHODNAME

It specifies the input param IV_OBJECT_ID and the output param ET_ORDER with the type as the newly created temporary table ZCL_TEST_AMDP=>GET_ORDERS_SQL=>ET_ORDER#tft

In the body of the procedure it then reads from a newly created view ZCL_TEST_AMDP=>CRMD_ORDERADM_H#covw instead of directly from the table CRMD_ORDERADM_H.

ZCL_T


create procedure
  "ZCL_TEST_AMDP=>GET_ORDERS_SQL"
(
  in    "IV_OBJECT_ID" NVARCHAR (000010),
  out   "ET_ORDER" "ZCL_TEST_AMDP=>GET_ORDERS_SQL=>ET_ORDER#tft"
)
language sqlscript  sql security invoker                            as begin
      et_order = select object_id, description from "ZCL_TEST_AMDP=>CRMD_ORDERADM_H#covw" where object_id = iv_object_id;
end;




ZCL_TEST_AMDP=>GET_ORDERS_SQL=>ET_ORDER#tft is defined as a global temporary table with the columns mapping to the columns of the ET_ORDER param.

Pic1.PNG

Pic2.PNG

ZCL_TEST_AMDP=>CRMD_ORDERADM_H#covw is a view based on the CRMD_ORDERADM_H table. Here is the create statement:


CREATE VIEW "SAPSR3"."ZCL_TEST_AMDP=>CRMD_ORDERADM_H#covw" ( "CLIENT",
  "GUID",
  "OBJECT_ID",
  "PROCESS_TYPE",
  "POSTING_DATE",
  "DESCRIPTION",
  "DESCR_LANGUAGE",
  "LOGICAL_SYSTEM",
  "CRM_RELEASE",
  "SCENARIO",
  "TEMPLATE_TYPE",
  "CREATED_AT",
  "CREATED_BY",
  "CHANGED_AT",
  "CHANGED_BY",
  "HEAD_CHANGED_AT",
  "ORDERADM_H_DUMMY",
  "INPUT_CHANNEL",
  "BTX_CLASS",
  "AUTH_SCOPE",
  "OBJECT_TYPE",
  "ARCHIVING_FLAG",
  "DESCRIPTION_UC",
  "OBJECT_ID_OK",
  "VERIFY_DATE",
  "CRM_CHANGED_AT",
  "POSTPROCESS_AT" ) AS select
  "CLIENT" ,
  "GUID" ,
  "OBJECT_ID" ,
  "PROCESS_TYPE" ,
  "POSTING_DATE" ,
  "DESCRIPTION" ,
  "DESCR_LANGUAGE" ,
  "LOGICAL_SYSTEM" ,
  "CRM_RELEASE" ,
  "SCENARIO" ,
  "TEMPLATE_TYPE" ,
  "CREATED_AT" ,
  "CREATED_BY" ,
  "CHANGED_AT" ,
  "CHANGED_BY" ,
  "HEAD_CHANGED_AT" ,
  "ORDERADM_H_DUMMY" ,
  "INPUT_CHANNEL" ,
  "BTX_CLASS" ,
  "AUTH_SCOPE" ,
  "OBJECT_TYPE" ,
  "ARCHIVING_FLAG" ,
  "DESCRIPTION_UC" ,
  "OBJECT_ID_OK" ,
  "VERIFY_DATE" ,
  "CRM_CHANGED_AT" ,
  "POSTPROCESS_AT"
from "CRMD_ORDERADM_H";


So in all 4 new objects were created:

– Procedure ZCL_TEST_AMDP=>GET_ORDERS_SQL

– Procedure ZCL_TEST_AMDP=>GET_ORDERS_SQL#stub#20140318165936

– Temporary table ZCL_TEST_AMDP=>GET_ORDERS_SQL=>ET_ORDER#tft

– View ZCL_TEST_AMDP=>CRMD_ORDERADM_H#covw

I can kinda understand the reasoning behind the temp table in order to easily map to an ABAP internal table but unsure of the purpose or logic for a) the stub procedure and b) the view for the table that’s referenced. Maybe some SAP internal dev folks might have some insight here….just to satisfy my curiosity!

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi Peter,

      trying to satisfy your curiosity:

      • "Purpose of stub procedure": Main purpose is to allow a "simple" call of the DP procedure within the ABAP stack (report, class, etc.). You can see that if you have a DB procedure with tabular input and/or output tables, just try it out and have a look 😉 . Moreover, there are technical arguments like "version management", you may have seen the timestamp in the stub name - apply changes to the AMDP, "reexecute" it and have a look at the changes.
      • "Purpose of view": This eases ABAP development pitfalls / issues which now might enter the game when developing an AMDP (or previously in native SQL but... who's bold enough to go this way 😆 ).
        For example, think of a "SELECT *" query in an AMDP. The order of the field list in the ABAP Data Dictionary might differ from the corresponding ordering in the database. Performing the query, this may lead (if you are lucky) to an error or (if you are less lucky) to wrong results without even noticing it. So the views have rather "comfort" reasons when reusing DDIC types.
        Another reason is, that you shouldn't perform DML statements on buffered tables, which is obiously not possible, since you cannot perform a DML on the created view.

      Cheers,

        Jasmin

      Author's profile photo Peter Murphy
      Peter Murphy
      Blog Post Author

      Thanks for the reply Jasmin.

      I will try it out with tabular input params also. I had suspected that there was a versioning aspect to the stub procedure alright.

      Regarding the creation of the view that makes sense and I guess the view creation is why we need to specify the forward declaration of the objects in the "USING" section of the definition?

      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi Peter,
      the USING clause allows to check the availability of DDIC/AMDP objects at compile time.

      You know that AMDPs can be transported in heterogeneous system landscapes, e.g. a class with a HANA AMDP can be transported to and activated on a non-HANA system without syntax errors but of course you might receive a runtime error when accessing the AMDP (like native SQL calls...). Yet, the non-existence of a DDIC table or another AMDP is independent of the underlying DB system, so we favour to check their existence at compile time rather than getting runtime errors.

      Cheers,

        Jasmin

      Author's profile photo Peter Murphy
      Peter Murphy
      Blog Post Author

      Thanks again Jasmin, makes sense.

      Author's profile photo Sean Holland
      Sean Holland

      hi Jasmin, a question on the use of language identifier, what role does it play if we specify sqlscript (currently only supported?) wrt parsing and compilation/activation?. Also if it's transported to a non-HDB environment e.g. Oracle, is there some kind of conversion to pl/sql or will it just create specific Oracle equivalent objects, presuming the statements will also compile there?

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Dear Sean,

      the language depends on the database and currently only sqlscript for HANA is supported but others can follow.

      As the scripting languages of the different databases are too different there cannot be an efficient way to automatically transform the code into the other language. (if that would be possible, we would use ABAP here and transform into every database script language 🙂 ).

      That implies: you have to code for every database separately and take care that you call these methods only when the current database supports it.

      If a AMDP reaches via transport a system where it is not supported it is silently ignored. As the database support is then missing, it even cannot be syntax checked. So you don't get a transport or compile error, but you would get a runtime error if you would call it.

      Best Regards, Thomas

      Author's profile photo Peter Murphy
      Peter Murphy
      Blog Post Author

      Hi Thomas, so lets say we have an AMDP language SQLscript and USING table A in HANA system. We then in theory transport this to a system on an Oracle database. In the Oracle based system lets say table A does not exist as per the scenario Jasmin has mentioned. What happens at compile time? Is the AMDP silently ignored due to the fact the we have declared language as "SQLScript" as you have mentioned or does it give a compile time error due to the fact that table A as specified in the USING clause does not exist?

      Thanks

      Peter

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Dear Peter,

      the USING part in the signature is checked, even if the sqlscript content of the procedure cannot be checked. So you would get an error in your example.

      But wouldn't you anyway transport the DDIC table A if you have code that relies on it?

      Best Regards, Thomas

      Author's profile photo Peter Murphy
      Peter Murphy
      Blog Post Author

      Hi Thomas, yes that would be my thought that of course that you would transport the DDIC table but was teasing out a situation where the USING clause would be relevant and comes into play.

      Author's profile photo Jasmin Gruschke
      Jasmin Gruschke

      Hi Peter,
      there are two kind of syntax checks running when working with AMDPs:

      1. "ABAP" Checks: They will find missing DDIC tables, missing AMDPs, etc.
        E.g. imagine you define an AMDP AMDP_A wherein AMDP_B is called, i.e. you have the using clause "USING CLASS_XYZ=>AMDP_B". You transport this to the non-HANA system and you will get a syntax error if the class method AMDP_B of class CLASS_XYZ is missing (and maybe others but just leave them out here) but you won't get any syntax checks for your SQLScript... because you non-HANA system does not support this.
      2. "HANA" Checks: In addition to the ABAP checks, the SQLScript syntax is checked by the HANA database. You see such checks, e.g. if you write down a "wrong" select statement.

      Of yourse the second syntax check is only performed on HANA and by the "correct" language, e.g. SQLScript in the mentioned example.

      Cheers,

        Jasmin

      Author's profile photo Sean Holland
      Sean Holland

      Thanks Thomas & Jasmin..

      A question we have is why the USING enforces the objects to exist in advance, say in theory we have a mix of HANA & non-HANA databases for multiple Biz Suite applications. We want to introduce a new check_table_details routine and write two new methods for HANA & non-HANA. For the HDB we have the native object TABLES, so that is called out in the USING, however the other DB's equivalent object is called DBA_TABLES. By enforcing the USING check up front, compilation will fail for one of the two, depending on which environment it's being transported to?

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      Are you talking about DDIC objects or some other db artefacts?

      The USING clause is only needed for DDIC objects (tables, views) and other AMDPs which are called.

      For access to db artefacts that are not DDIC objects (e.g. the table/view named TABLES ) you do not need a USING clause.

      And the DDIC objects are expected to exist in any database.

      Best Regards, Thomas

      Author's profile photo Sean Holland
      Sean Holland

      OK got it, so DDIC objects are expected to be on both application servers so need to be called out in the USING clause, but the native db objects don't have to be defined in the USING clause but can be referenced in for example the sql script code?

      Author's profile photo Thomas Gauweiler
      Thomas Gauweiler

      That is correct.

      Best Regards, Thomas

      Author's profile photo Former Member
      Former Member

      In my case two wrapper procedure is generated..with naming

      CLASSNAME=>METHODNAME#stb2#DATETIME.and

      CLASSNAME=>METHODNAME#stub#DATETIME

      the only difference in two procedures is the naming.

      what is the need for stb2 wrapper class ??

      Author's profile photo Dinesh Rupchandani
      Dinesh Rupchandani

      Hello Peter,

      Nice Blog.

      As mentioned, I have created AMDP Class in ABAP Perspective Please tell where I can get the generated procedures for the same?

       

      Regards,

      Dinesh