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 access data from an underlying database schemas is : –
<code>SELECT * FROM “<physical schema>”.”<table name>” WHERE <condition> -> which you will write in an AMDP method implementation. </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 . 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:
- Why are we talking about different database schemas?
- What is the problem if I just use the direct Open SQL or CDS to fetch the data?
- I have written AMDP selection without physical schema name, then what is this that you are talking about?
- 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.
Let’s say that you want 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 the default.
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 above syntax, you would write below AMDP code: –
<code>SELECT * FROM “DEV_SCHEMA”.”ZCCARD_DETAILS” WHERE customer_name = ‘SAPSPOT’.</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 development system, 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 be in 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: –
- Get the physical schema based on Alias -> First select query
- 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 you have to dynamically pass the physical schema 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 auto converts the alias into physical schema name and then directly places it into the SELECT query where it is being used. The syntax is: –
SELECT * FROM “$ABAP.Schema( ZS4 )”.” ZCCARD_DETAILS” WHERE customer_name = ‘SAPSPOT’.
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.
Is that all? Well NO!!
There are some actions to 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 the logical schema ZS4. After following all the steps in the wizard, you will finally 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 choose to also transport this logical name to further systems.
But remember, though you move the logical schema name via transport, the attachment to the physical schema name in transaction DB_SCHEMA_MAP has to be done in the target system directly. This becomes a cutover activity.
After maintaining the schema name, you can re-verify it in the logical database schema in ADT. You will now see as below: –
This finishes all the activity.
Note: The AMDP can be activated only when the above steps are done. Until then, you will keep getting syntax error stating that the required configuration 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.
