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.
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!
- SAP Managed Tags:
16 Comments
