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.
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.
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!
trying to satisfy your curiosity:
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.
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?
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.
Thanks again Jasmin, makes sense.
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?
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
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?
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
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.
there are two kind of syntax checks running when working with AMDPs:
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.
Of yourse the second syntax check is only performed on HANA and by the "correct" language, e.g. SQLScript in the mentioned example.
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?
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
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?
That is correct.
Best Regards, Thomas
In my case two wrapper procedure is generated..with naming
the only difference in two procedures is the naming.
what is the need for stb2 wrapper class ??
As mentioned, I have created AMDP Class in ABAP Perspective Please tell where I can get the generated procedures for the same?