In this blog, I will briefly explain the use of synonyms for staging tables and will also provide sample code on how to create synonyms for renaming the staging tables of the SAP S/4HANA migration cockpit. These synonyms can be later used in other ETL Tools such as SAP Data Services.
As mentioned in the blog from
markus.andres: “
Using SAP Data Services to load data to the staging tables“, you can use SAP Data Services to populate the migration cockpit´s staging tables. An overview of the SAP S/4HANA migration cockpit and further details about the migration approach “Transfer Data Using Staging Tables” are explained in Blog:
Part 1: SAP S/4HANA migration cockpit – Migrating data using staging tables and methods for populati....
When moving (i.e. import/exporting) the SAP S/4HANA migration cockpit projects from the development environment to a quality environment, the generated names of the staging tables will be different in the different environments. Consequently, you have to adjust the data flows in your ETL tool and update the names of the staging tables. You can avoid these adjustments if you define synonyms for the staging tables and use the synonyms in the data flows. Synonyms are references or aliases for a table or other database objects, so it’s just another name you can use to refer to the table in SQL statements. For more details about synonyms you can refer to:
For the SAP S/4 HANA migration cockpit, synonyms can be used to obtain identifying names from the system, project, object and structure instead of having consecutive numbers in the table name. Synonyms have the format S_<sid>__<object>__<structure>, for example S_ABC__Z_CUSTOMER2_US8__S_CUST_GEN.
The names of the staging tables are stored in mapping table /1LT/DS_MAPPING in the schema of the staging tables. Users can use table /1LT/DS_MAPPING to recreate the synonyms. This means that even though the names of the staging tables will change, the destinations used for data export stored procedures or scripts in the extraction tools (for example SAP Data Services) will not change.
The example stored procedure below shows how to create the synonyms for the staging tables. For example, the name of the staging table for source structure S_CUST_SALES_DATA in the staging schema of the development system might be /1LT/DSDEV10001234 and /1LT/DSQAS20004321 in the staging schema of the quality system. In the case below, the SID of the S/4HANA system is used in the synonym. However, you can remove this SID (i.e. <sid> & ABC, sample name which represents this SID) to make the stored procedure system independent and get static synonym names across the landscape (simply comment and uncomment the respective line in the script).
……………………………………………………………………………………………….
Stored procedure for all staging tables of a migration project
Important Notes:
- The procedure and synonyms are created in the current schema using SQL console in SAP HANA Studio.
- Input of the stored procedure is the system id <sid> and the project id <mtid>. There is no output.
- A synonym will be created for each staging table in a migration project.
- If a synonym already exists but is assigned to another table name, it will be dropped and created with the current staging table name.
- The naming convention used for the synonym is: S_<sid>__<object>__<structure>, e.g. S_ABC__Z_CUSTOMER2_US8__S_CUST_GEN
- The scripts and stored procedures are examples that should be adjusted to your requirements.
- Create procedure
--drop procedure create_staging_synonyms;
CREATE PROCEDURE create_staging_synonyms(
IN SYS_ID NVARCHAR(8),
IN MT_ID NVARCHAR(3))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE CNT INTEGER;
DECLARE SYNONYM_NAME NVARCHAR(60);
DECLARE STAGING_TAB NVARCHAR(30);
DECLARE CURSOR c_cursor ( SYSID NVARCHAR(8), MTID NVARCHAR(3)) FOR
select *
from "/1LT/DS_MAPPING"
where SYS_ID = :SYSID
and MT_ID = :MTID;
--
FOR wa AS c_cursor(:SYS_ID, :MT_ID)
DO
STAGING_TAB = :wa.STAGING_TAB;
-- synonym name with SID
SYNONYM_NAME = 'S_' || :wa.SYS_ID || '__' || :wa.COBJ_IDENT || '__' || :wa.STRUCT_IDENT;
-- synonym name without SID
-- SYNONYM_NAME = 'S_' | :wa.COBJ_IDENT || '__' || :wa.STRUCT_IDENT;
-- drop synonym if already exists with another staging table
SELECT count(*) into CNT FROM SYNONYMS WHERE SYNONYM_NAME = :SYNONYM_NAME;
if CNT > 0 then
SELECT OBJECT_NAME into STAGING_TAB FROM SYNONYMS WHERE SYNONYM_NAME = :SYNONYM_NAME;
if STAGING_TAB <> :wa.STAGING_TAB then
EXEC 'DROP SYNONYM "' || :SYNONYM_NAME || '"';
STAGING_TAB = :wa.STAGING_TAB;
EXEC 'CREATE SYNONYM "' || SYNONYM_NAME || '" for "' || STAGING_TAB || '"';
end if;
else
EXEC 'CREATE SYNONYM "' || SYNONYM_NAME || '" for "' || STAGING_TAB || '"';
end if;
END FOR;
END;
- Call procedure for a system and project
In SQL console you can call the procedure as follows e.g. for system QKX and project id US8:
CALL create_staging_synonyms(SYS_ID => 'QKX', MT_ID => 'US8');
- You can display all synonyms for your system and project like this
Replace <MTID> with the project id and <SID> with the system id:
SELECT * FROM SYNONYMS WHERE SYNONYM_NAME LIKE 'S_<SID>%<MTID>%' order by SYNONYM_NAME;
If you follow the steps above you will get synonyms with static names that are independent from the generated staging table names and you can use these names in your ETL tools.
