E2E – Consuming HANA procedures using ADBC
Author: Sathish Kumar Dharmaraj
Company: NTT DATA Global Delivery Services Private Limited
Introduction
Our certain section of ABAP reports being executed in SAP NW 7.3 on Oracle DB and by running these reports for large selection takes lot of time in execution and it occupies lot of resources. Hence, on having HANA DB, push down the entire logic to HANA by replicating the source tables using SLT, developing the procedures on base tables and consuming the procedures in SAP using ADBC.
This end to end guide illustrates the replication method of SLT, building the procedures and how to consume in SAP.
HANA
1) Go to Programs -> SAP HANA -> SAP HANA Studio
2) Go to Window -> Open Perspective -> SAP HANA Modeler
3) Click Add System Icon
4) Provide host name, instance and description. Click Next.
5) Provide User name and Password.
6) Click Finish
7) Click Select System from the Quick Launch Modeler Screen
8) Select your corresponding environment
9) Click Data Provisioning
10) Select a schema (Source System) to replicate the table
11) Select Load (if it is Initial Load (one-time)) or Replicate (If you want both Initial + Delta records)
12) Enter the table name if you want to load/replicate
13) Choose the table and click Add button to transfer from source tables to selected tables.
14) Click Finish
15) Table will be initially in Load (Action) – Scheduled (Status) and when the load is in progress it shows the Action as Load and Status as In Process, when the load is completed, it shows the action as Load and Status as Executed. For the replication, it shows the action as Replicate and Status as In Process. You can monitor the status by clicking the refresh button in the top right corner of the below screen.
16) Go to Catalog -> Choose your schema
17) Go to Schema -> Tables
18) Replicated table will be shown here
19) To create the procedure, go to Content
20) To create Package, Go to Content -> New -> Package
21) Enter the package information details like Technical name, description and delivery unit.
22) Delivery unit can be assigned at later stage.
23) Select your package -> Right Click -> New -> Procedure
24) Provide procedure details and choose the default schema were the tables being replicated via SLT.
25) Click Finish
26) Procedure has different panes, Script View, Input Pane and Output Pane.
a) Create the output parameters from the Output pane, Select Output Parameters -> Right Click -> New (List out the fields for output)
b) Save and Activate the procedure
27) Once the procedure been activated, it will be stored under _SYS_BIC schema -> Procedure
28) Execute the procedure using CALL statement
29) Result will be shown as below
30) To insert the data into an table, use CALL procedure WITH OVERVIEW
ABAP
Follow below instructions for calling procedures in ABAP
35) Declare types which is exact similar to Output Parameter structure
36) SQL connection data declaration and its variables
37) Fetching the DB connection name should be maintained in DBCON table or using DBCO transaction.
38) Fetch the table name using the below CALL procedure WITH OVERVIEW
39) Execute Query which will return the table name
40) Execute the table using select and fetch the data
41) Assign the table result into target variable/internal table.
I hope this document helps to give an idea to extract the information from HANA using ADBC connection.