Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Sunday, 17 March 2019 14:45

ABAP for SAP HANA – Part 23. How to Access Database Schema Dynamically Using AMDP?

Written by 
Rate this item
(0 votes)

We all know that ABAP Managed Database Procedure (AMDP) is introduced by SAP to develop SQL script based programs called as Database Procedures. Since we no

longer need a database user to program the database procedures, it has become increasingly easy to access data from different database schema using SQL script by implementing AMDP methods.

The syntax to accessdata from an underlying database schemas is : –

1
2
<code>SELECT*FROM&lt;physicalschema>.&lt;tablename>WHERE&lt;condition>->whichyouwillwriteinanAMDPmethodimplementation.</code>

Please note the specific difference between SQL script syntax and the Open SQL. We have to mention the Physical Schema Name to correctly identify the underlying path to access the required data of that <table>. If you do not give the physical schema, then the default schema is automatically chosen. The default schema can be found using function module – DB_DBSCHEMA_CURRENT.

Now, you would have the below questions:

  1. Why are we talking about different database schemas?
  2. What is the problem if I just use the direct Open SQL or CDS to fetch the data?
  3. I have written AMDP selection without physical schema name, then what is this that you are talking about?
  4. As an ABAP programmer, why and when would I be required to pull data using such DB procedures?

Well, from my learning – not all the tables in the underlying database schemas have their corresponding dictionary view. Hence, you cannot see them all in SE11 / SE16, and such tables can exist which hold relevant business master / transaction data. The source of these data could be any SAP / non-SAP system and using SAP LT replication technique, these tables are populated.

Also ReadATC – ABAP Test Cockpit Setup & Exemption Process

Ok, now coming to the real purpose of this blog.

Let’s say that youwant to access table ZCCARD_DETAILS (table holding credit card details of the customer)which is in HANA database but does not have dictionary view and is in a physical schema different from thedefault.

You have been told by the basis person that the physical schema name in which this table is lying is: DEV_SCHEMA whereas the schema name would change in quality as QUAL_SCHEMA and in production as PROD_SCHEMA(different schema name in different system is the regular practice, nothing new ? ).

Now, as per the abovesyntax, you would write below AMDP code: –

1
2
<code>SELECT*FROM“DEV_SCHEMA”.”ZCCARD_DETAILS”WHEREcustomer_name=‘SAPYard’.</code>

This will work perfectly fine in development but when you will move to quality, it is going to fail since there is no such physical schema called “DEV_SCHEMA”. In quality the physical schema is QUAL_SCHEMA.

To tackle this problem, there is something called Schema Mapping Concept – which is again an underlying database table in physical schema “_SYS_BI”, and it holds the alias for each physical schema. Alias remains the same in all systems, but the names of physical schema attached against it change.

So, in developmentsystem, the schema mapping entries would look like this: –

ALIAS ( called as AUTHORING SCHEMA or logical name) Physical Schema
ZS4_ALIAS DEV_SCHEMA

And the same would bein quality:

ALIAS ( called as AUTHORING SCHEMA ) Physical Schema
ZS4_ALIAS QUAL_SCHEMA

Hurray, now you just have to refer to Alias name in AMDP select query in below way: –

  1. Get the physical schema based on Alias -> First select query
  2. Get the card details by appropriately referring the physical schema name that you got in step 1 in the second select query.

This means, that youhave to dynamically pass the physicalschema name to the second select query.

But, is there any SQL script syntax which supports passing such dynamic reference of the schema name ??? Well, not in my knowledge. ?

Then, are we stuck?What can we do now?

The answer to this is:AMDP’s standard delivered macro called $ABAP.Schema.

This macro autoconverts the alias into physical schema name and then directly places it intothe SELECT query where it is being used. The syntax is: –

1
2
<code>SELECT*FROM$ABAP.Schema(ZS4).ZCCARD_DETAILS”WHEREcustomer_name=‘SAPYard’.</code>

Excellent!! With this syntax, you do not have to write 2 selects queries and also the problem of passing dynamic physical name is resolved, since you have to just pass the logical name and macro takes care of the rest.

Also please check my other ArticleCode Quality Control, Simplified

Is that all? Well NO!!

There are some actionsto be performed even before we can use this syntax.

First of all, let me tell you that I used logical name as ZS4 but did not use the alias name ZS4_ALIAS that I showed you. Now, what is this ZS4? Where is this coming from?

Let’s begin:

ZS4 is the logical database schema and can be created using Eclipse ADT. Open the project, click NEW object. Move to Others -> Define logical schema definition. Follow the wizard and then activate it.

Choose: Other ->Logical Database Schema.

Here you create thelogical schema ZS4. After following all the steps in the wizard, you willfinally get the below screen: –

This screen tells you that you have now mapped the underlying physical schema name in the transaction DB_SCHEMA_MAP. Please activate the logical schema before you go to the transaction, else it will not be visible there.

In the transaction DB_SCHEMA_MAP, you will see that the entry with logical name ZS4 has appeared.

Choose the record,click on EDIT, place the physical schema name and click on SAVE. You can chooseto also transport this logical name to further systems.

But remember, though you move the logical schemaname via transport, the attachment to the physical schema name in transactionDB_SCHEMA_MAP has to be done in the target system directly. This becomes a cutover activity.

After maintaining theschema name, you can re-verify it in the logical database schema in ADT. Youwill now see as below: –

This finishes all theactivity.

Note: The AMDP can be activated only when the above steps are done. Untilthen, you will keep getting syntax error stating that the requiredconfiguration is not done and AMDP will not be activated.

You do tons of SAP ABAP on HANA training. You read lots of S/4HANA Technical blogs. But no one can teach you better than the real project issues and use cases. I have been working on HANA ABAP for few years now, but accessing the database schema dynamically gave me an initial set back. Hopefully, you would not have to spend any time in finding the solution.

Thank you for stopping by and going through this article. If you faced similar issues in HANA ABAP, please do write to us at This email address is being protected from spambots. You need JavaScript enabled to view it. or This email address is being protected from spambots. You need JavaScript enabled to view it.. We would like to publish more articles on S/4HANA ABAP issues and tips.

We have a very active Telegram (App) SAP Technical Group with more than 4250+ SAP Technical Practitioners from 6 Continents of the SAP World. Please join it using below link.
Telegram SAP Technical Discuss Group. You need to install the Telegram App first on your mobile device. Once you have it on your mobile, you can join the group and also access it from the Web on your computer and laptop.

Free SAP ABAP for HANA Training Tutorials Exercises.

Read 88 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.