SQLScript Debugging from External Session

With the introduction of Suite on HANA, ABAP developers will need to branch out a bit more and extend their skills into the database.  We’ve discussed the concept of code push down for quite some time now, and if it hasn’t sunk in yet, let me remind you again with this blog.  ABAP based applications can run faster on HANA without modification as long as the bottleneck is not the application server itself.  If your ABAP program is simply bringing massive amounts of data from HANA into the ABAP application server, and you are doing a LOOP over that data and doing some calculation, then you are not really using the full potential of HANA.  We need to take that data intensive logic and rewrite it in SQLScript and push that logic into the database for faster processing, leveraging the massive parallel processing capabilities of HANA.  So if we are integrating SQLScript procedures into our ABAP applications running on top of HANA, of course we need to be able to debug the procedures in-line with our ABAP applications.  As of HANA 1.0 SPS06(Rev 60), we now support the concept of external session debugging. In this blog, I will concentrate on debugging from ABAP, but the external session debugging is not specific to ABAP, and can be used to debug from other sources as well. 

In this example, I have a procedure called GET_PRODUCTS which accepts an input parameter called IM_PRODUCT which is used as a filter value, and returns one export parameter called EX_PRODUCTS.  The code of this procedure is rather simple, it reads the “Products” table based on the “Product Id” filter and returns a list of products.

CREATE PROCEDURE get_products ( in im_product nvarchar(20),

                                out ex_products tt_products_list )

       LANGUAGE SQLSCRIPT

       SQL SECURITY INVOKER

       READS SQL DATA AS   

BEGIN

/*****************************

       Write your procedure logic

*****************************/

ex_products = select “ProductId”, “Category”, “Price”

                 from “SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”

                       where “ProductId” like im_product; 

END;

There are two ways to call a procedure from ABAP.  You can use ADBC(ABAP Database Connectivity) to call your procedure or use the new “Database Procedure Proxy” method which was introduced in NetWeaver 7.40.  Debugging  from ABAP into SQLScript works the same way whether you call it via ADBC, or use a proxy. For this example, I have created a Database Procedure Proxy called ZGET_PRODUCTS for this procedure.

I have also created an ABAP program which calls this “Database Procedure Proxy”.   Here I am passing P_PROD which is a PARAMETER and has the product filter value. LT_PRODUCTS is an internal table defined with the same structure as the EX_PRODUCTS exporting parameter.

Now that I have all of the components of my application, I can start debugging from my ABAP program straight into the SQLScript procedure in HANA.  In the SQLScript Debug configuration screen, I can set the external session parameters.  In this dialog is where you set the HANA server which is sitting under your ABAP system. The “HANA User” value would be the SAP<sid> user id which ABAP uses to talk to the database.  The “Application User” would be the user id which the developer uses to log on to the ABAP system.

Once the configuration is complete, click the “Debug” button.  Next, I can set the breakpoints in the procedure and switch to the “Debug” perspective.  Notice there is a debug session running which is simply waiting for ABAP.

Next, I return to my ABAP program and set a breakpoint on the CALL DATABASE PROCEDURE statement and run the program.  Once I execute the CALL DATABASE PROCEDURE statement from the debugger, control is then passed to the SQLScript debugger in HANA Studio.  You can see that the input parameter value has been passed to the procedure and you can see this value in the “Variables” tab of the SQLScript debugger. From here I can debug the procedure as normal, and when complete, control is passed back to the ABAP application.

Check out the video demonstration on the SAP HANA Academy.