Skip to Content

Create an ABAP Managed Database Procedure (AMDP) and Analyze Its Performance

test
0 %
Create an ABAP Managed Database Procedure (AMDP) and Analyze Its Performance
Details

Create an ABAP Managed Database Procedure (AMDP) and Analyze Its Performance

Julie Plummer
Julie Plummer
August 16, 2020
Created by
Julie Plummer
February 28, 2019
Intermediate
45 min.
ABAP Development, Intermediate, SAP Cloud Platform, Tutorial, SAP NetWeaver 7.5, SAP Cloud Platform, ABAP environment
Create an AMDP and analyze its runtime performance along with the performance of the executed SQL statements, using the AMDP Profiler in ABAP Development Tools (ADT).

You will learn

  • How to create an ABAP class containing an ABAP Managed Database Procedure (AMDP)
  • How to run the ABAP Profiler on this class

Prerequisites

Throughout this tutorial, objects name include the suffix XXX. Always replace this with your group number or initials.

You should be familiar with ABAP Managed Database Procedures (AMDP). Briefly, AMDP allows you to optimize your ABAP code (for ABAP on SAP HANA) by calling HANA database procedures from a global ABAP class.
For more details, see:

Step 1: Create an ABAP package

  1. Select your project. From the context menu, choose New > ABAP Package.

    Image depicting step2-package

  2. Enter a name and description for your package, then choose Next.

    Image depicting step2b-package-name

  3. Create or assign a transport request and choose Finish.

Done
Log on to answer question
Step 2: Create an ABAP class

  1. Again, from the context menu of your package, choose New > ABAP Class.

  2. Enter a name and description, then choose Next.

    • Name: ZCL_AMDP_DEMO_XXX
    • Description: AMDP Demo w Flight Ref Scenario

  3. Assign the transport request and choose Finish.

Done
Log on to answer question
Step 3: Add two interfaces
View Full Instructions

Add two interfaces by adding this code to the public section. Ignore the warning for now.

  • if_amdp_marker_hdb defines the class as an AMDP class, allowing you to implement AMDP methods - that is, ABAP methods that call a SAP HANA database procedure from within a global ABAP class.

  • if_oo_adt_classrun allows you to output the results to the ABAP Console.

INTERFACES: if_amdp_marker_hdb,
if_oo_adt_classrun.
Done
Log on to answer question
Step 4: Create structures and table types
View Full Instructions

Add these structures and types to the public section, just after the interface definitions.
Note the data elements that you imported earlier.

TYPES:
  BEGIN OF ty_result_line,
    airline           TYPE /dmo/carrier_name,
    flight_connection TYPE /dmo/connection_id,
    old_price         TYPE /dmo/flight_price,
    old_currency      TYPE /dmo/currency_code,
    new_price         TYPE /dmo/flight_price,
    new_currency      TYPE /dmo/currency_code,
  END OF ty_result_line,

  BEGIN OF ty_flights_line,
    airline           TYPE /dmo/carrier_name,
    flight_connection TYPE /dmo/connection_id,
    price             TYPE /dmo/flight_price,
    currency          TYPE /dmo/currency_code,
  END OF ty_flights_line,

  ty_result_table  TYPE STANDARD TABLE OF ty_result_line WITH EMPTY KEY,
  ty_flights_table TYPE STANDARD TABLE OF ty_flights_line WITH EMPTY KEY,
  ty_flights       TYPE STANDARD TABLE OF /dmo/flight.
Done
Log on to answer question
Step 5: Add method definitions
View Full Instructions

Add these two method definitions to your code. Ignore the errors for now.


METHODS:
  get_flights
    EXPORTING
              VALUE(result) TYPE ty_result_table
    RAISING   cx_amdp_execution_error,

  convert_currency
    IMPORTING
              VALUE(flights) TYPE ty_flights_table
    EXPORTING
              VALUE(result)  TYPE ty_result_table
    RAISING   cx_amdp_execution_error.

Both of these are AMDP methods.

Done
Log on to answer question
Step 6: Implement get_flights
View Full Instructions

  1. In the class definition, select any one of the methods and choose Add 3 unimplemented methods. All three (empty) implementations will appear in the class implementation.

    Image depicting step7-implement-3-methodsImage depicting step7b-implems

  2. Add the following to the method implementation get_flights. Both this and convert_currency are SQLScript.

    For more information on SAP HANA SQLScript, see SAP HANA SQLScript Reference


    METHOD GET_FLIGHTS by database procedure
  for hdb
  language sqlscript
  options read-only
  using
    /dmo/flight
    /dmo/carrier
    ZCL_AMDP_DEMO_XXX=>convert_currency.

    You must specify all ABAP tables, views, and AMDP procedures in the USING clause.
    For more details on these clauses, see ABAP Keyword Documentation: Method - By Database Procedure, Function

  3. Add the SELECT statement. Ignore the warning for now.

    flights = select distinct

  from "/DMO/FLIGHT"  as f
  inner join "/DMO/CARRIER" as c
    on f.carrier_id = c.carrier_id;

  4. Add the fields. You can do this using Auto-complete (Ctrl+1), to make sure you are using the correct field names.


    flights = select distinct
  c.name as airline,
  f.connection_id as flight_connection,
  f.price    as price,
  f.currency_code as currency
  from "/DMO/FLIGHT"  as f
  inner join "/DMO/CARRIER" as c
    on f.carrier_id = c.carrier_id;

  5. Finally, call the other AMDP method, convert_currency. Your method should look like this:


    METHOD get_flights BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING
    /dmo/flight
    /dmo/carrier
    ZCL_AMDP_DEMO_XXX=>convert_currency.

  flights = select distinct
    c.name as airline,
    f.connection_id as flight_connection,
    f.price    as price,
    f.currency_code as currency
    from "/DMO/FLIGHT"  as f
    inner join "/DMO/CARRIER" as c
      on f.carrier_id = c.carrier_id;

  call "ZCL_AMDP_DEMO_XXX=>CONVERT_CURRENCY"( :flights, result );

ENDMETHOD.
Done
Log on to answer question
Step 7: Implement the method convert_currency
View Full Instructions

Similarly, implement the convert_currency method.


METHOD convert_currency BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY.

  declare today date;
  declare new_currency nvarchar(3);

  select current_date into today from dummy;
  new_currency := 'EUR';

  result = select distinct
    airline,
    flight_connection,
    price    as old_price,
    currency as old_currency,
    convert_currency(
      "AMOUNT"          => price,
      "SOURCE_UNIT"     => currency,
      "TARGET_UNIT"     => :new_currency,
      "REFERENCE_DATE"  => :today,
      "CLIENT"          => '100',
      "ERROR_HANDLING"  => 'set to null',
      "SCHEMA"          => current_schema
    ) as new_price,
    :new_currency as new_currency
    from :flights;

ENDMETHOD.
Done
Log on to answer question
Step 8: Implement the method `main` of the interface if_oo_adt_classrun
View Full Instructions

Finally, implement the main method of the interface if_oo_adt_classrun. This will allow you to output your results to the ABAP Console.

  1. Call the method get_flights from the current instance of the class:

    me->get_flights(
  IMPORTING
    result = DATA(lt_result) ).

  2. Output the result to the console:

    out->write( lt_result ).

  3. Wrap this in an exception:

    TRY.

CATCH cx_amdp_execution_error INTO DATA(lx_amdp).
  out->write( lx_amdp->get_longtext( ) ).

Your method should now look like this:

METHOD if_oo_adt_classrun~main.

  TRY.
      me->get_flights(
        IMPORTING
          result = DATA(lt_result) ).

    CATCH cx_amdp_execution_error INTO DATA(lx_amdp).
      out->write( lx_amdp->get_longtext( ) ).
  ENDTRY.

  out->write( lt_result ).

ENDMETHOD.
Done
Log on to answer question
Step 9: Check your code
View Full Instructions

Your code should look like this.

CLASS ZCL_AMDP_DEMO_XXX DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb,
                if_oo_adt_classrun.

    TYPES:
      BEGIN OF ty_result_line,
        airline           TYPE /dmo/carrier_name,
        flight_connection TYPE /dmo/connection_id,
        old_price         TYPE /dmo/flight_price,
        old_currency      TYPE /dmo/currency_code,
        new_price         TYPE /dmo/flight_price,
        new_currency      TYPE /dmo/currency_code,
      END OF ty_result_line,

      BEGIN OF ty_flights_line,
        airline           TYPE /dmo/carrier_name,
        flight_connection TYPE /dmo/connection_id,
        price             TYPE /dmo/flight_price,
        currency          TYPE /dmo/currency_code,
      END OF ty_flights_line,

      ty_result_table  TYPE STANDARD TABLE OF ty_result_line WITH EMPTY KEY,
      ty_flights_table TYPE STANDARD TABLE OF ty_flights_line WITH EMPTY KEY,
      ty_flights       TYPE STANDARD TABLE OF /dmo/flight.

    METHODS:
      get_flights
        EXPORTING
                  VALUE(result) TYPE ty_result_table
        RAISING   cx_amdp_execution_error,

      convert_currency
        IMPORTING
                  VALUE(flights) TYPE ty_flights_table
        EXPORTING
                  VALUE(result)  TYPE ty_result_table
        RAISING   cx_amdp_execution_error.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS ZCL_AMDP_DEMO_XXX IMPLEMENTATION.
  METHOD get_flights BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING
      /dmo/flight
      /dmo/carrier
      ZCL_AMDP_DEMO_XXX=>convert_currency.


    flights = select distinct
      c.name as airline,
      f.connection_id as flight_connection,
      f.price    as price,
      f.currency_code as currency
      from "/DMO/FLIGHT"  as f
      inner join "/DMO/CARRIER" as c
        on f.carrier_id = c.carrier_id;

    call "ZCL_AMDP_DEMO_XXX=>CONVERT_CURRENCY"( :flights, result );

  ENDMETHOD.


  METHOD convert_currency BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY.

    declare today date;
    declare new_currency nvarchar(3);

    select current_date into today from dummy;
    new_currency := 'EUR';

    result = select distinct
      airline,
      flight_connection,
      price    as old_price,
      currency as old_currency,
      convert_currency(
        "AMOUNT"          => price,
        "SOURCE_UNIT"     => currency,
        "TARGET_UNIT"     => :new_currency,
        "REFERENCE_DATE"  => :today,
        "CLIENT"          => '100',
        "ERROR_HANDLING"  => 'set to null',
        "SCHEMA"          => current_schema
      ) as new_price,
      :new_currency as new_currency
      from :flights;

  ENDMETHOD.


  METHOD if_oo_adt_classrun~main.

    TRY.
        me->get_flights(
          IMPORTING
            result = DATA(lt_result) ).

      CATCH cx_amdp_execution_error INTO DATA(lx_amdp).
        out->write( lx_amdp->get_longtext( ) ).
    ENDTRY.

    out->write( lt_result ).

  ENDMETHOD.
ENDCLASS.
Done
Log on to answer question
Step 10: Save, activate, and test your code

  1. Save and activate your code by choosing Ctrl+S, Ctrl+3.

  2. Test your class by running it the ABAP Console ( F9 ).

Your output should look like this:

Image depicting step11-console

Done
Log on to answer question
Step 11: Set the Run configuration to ABAP Console

You will run your class again in the ABAP Console. First, however, you will specify that the AMDP trace is to be enabled this time.

  1. From the context menu of the editor, choose Profile As > ABAP Application (Console).

    Image depicting step12-profile-as

  2. In the dialog that appears, choose AMDP trace options > Enable AMDP trace. Leave the other settings as they are, then choose Finish.

    Image depicting step5c-enable-amdp

The class will run again, outputting the same data to the Console and also creating an ABAP Trace.

Done
Log on to answer question
Step 12: Open the AMDP tab

Now, you will examine the ABAP Trace you have created.

  1. Open the ABAP Traces view, by choosing it from Quick Access.

    Image depicting step12a-quick-access

    .
    Image depicting step12b-abap-traces

  2. The view opens below the Class Editor, beside the Console. If necessary, refresh the view ( F5 ). Double-click on your class.

    Image depicting step12c-abap-traces-class

The Trace Results view appears. The ABAP Managed Database Procedures tab appears to the right.

Image depicting step12d-amdp-tab

In older versions of SAP AS ABAP (earlier than 7.55): In the ABAP Profiler display for the AMDP Trace, the column “Execution Time” sometimes contains values that are larger than the values in column “Duration”. As the ABAP Profiler usually displays elapsed time, the column “Execution Time” may only be smaller than “Duration”, as there might be some “Compile Time” involved in the overall duration.

This is a known issue and is fixed in SAP AS ABAP Release 7.55.

For more details, see: SAP Note 2874907 - Display Elapsed Time Values in AMDP Profiler

Done
Log on to answer question
Step 13: Test yourself
What are the main benefits of AMDPs? Choose all that apply. Check the introductory blog cited above for hints:
×

Next Steps

Navigate tutorial steps

QR Code

QR code
Back to top