Parallelization - ABAP Managed Database Procedure (AMDP)

[amol_samte]

Hello All,

In this blog we are going to understand how to achieve parallelization in AMDP.

In very simple terms, parallelization is to break the main task into smaller units and simultaneously execute them to achieve the results.

Earlier DML statements and read-write procedure calls had to be executed sequentially, but now  it is possible to parallelize the execution of independent DML statements and read-write procedure calls by using parallel execution blocks.

Below is syntax can be applicable in both AMDP and Native HANA Procedure

Here is syntax

BEGIN PARALLEL EXECUTION

<Stmt>...

END;

Before going towards to the example, have a look on basics of AMDP

https://blogs.sap.com/2014/01/22/abap-managed-database-procedures-introduction/

Now lets see how to add in AMDP.

1. I am selecting PRICE value from SFLIGHT table/ CARRNAME value from SCARR table and taking into variable to update in custom table(ZTABLE1, ZTABLE2)


2. Updating PRICE and CARRNAME in custom table.

METHOD parallel  BY DATABASE PROCEDURE

                 FOR HDB

                 LANGUAGE SQLSCRIPT

                 USING sflight

                       scarr

                       ztable1

                       ztable2.



    DECLARE lv_price INTEGER;

    DECLARE lv_airline_name nvarchar(20);







    select top 1 price into lv_price from sflight  where carrid = 'AA';



    select top 1 carrname into lv_airline_name from scarr where carrid = 'AA';



    BEGIN PARALLEL EXECUTION



        UPDATE ztable1 SET price = lv_price WHERE carrid = 'AA';

        UPDATE ztable2 SET carrname = lv_airline_name WHERE CARRID = 'AA';



    END;





  ENDMETHOD.

Execution Plan:



 

Just to understand what is structure of custom table and what values been updated, sharing below result set.





 

Some important things we need to consider while doing parallelization

Following statement are allowed :

1. DML


2. Imperative logic


3. Autonomous transaction


4. Implicit SELECT and SELECT INTO scalar variable

Restrictions and Limitations

1. Modification of tables with a foreign key or triggers are not allowed


2. Updating the same table in different statements is not allowed


3. Only concurrent reads on one table are allowed. Implicit SELECT and SELCT INTO scalar variable statements are supported.


4. Calling procedures containing dynamic SQL (for example, EXEC, EXECUTE IMMEDIATE) is not supported in parallel blocks


5. Mixing read-only procedure calls and read-write procedure calls in a parallel block is not allowed.

For more details :

https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.02/en-US/8db200a4f585490c81c4930689...

you can share you point of view and expert comments.

Cheers 😉

Amol