Under the HANA hood of an ABAP Managed Database Procedure

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