This blog post is to explain how to replicate a table from SAP S/4HANA >> SAP Landscape Transformation (SLT) >> DB2 Z/OS
Prerequisites:
◉ RFC Connection between SAP S/4HANA and SAP Landscape Transformation (SLT)
◉ DB Connection Between SAP Landscape Transformation (SLT) >> DB2 Z/OS
◉ Manually creating the Table in Target before starting the Replication
◉ Other Mandatory settings in SAP Landscape Transformation (SLT) like having all the required notes implemented in Source as per the version of SAP Landscape Transformation (SLT), users and required roles and privileges in Source and target.
◉ Required DB2 Drivers or ODBC connection from SAP Landscape Transformation (SLT) to DB2 Z/OS
Considerations:
You already have a good knowledge on how to install and configure SAP Landscape Transformation (SLT), what is SAP NOTE ANALYZER in SAP Landscape Transformation (SLT), Security requirements for seamless Replication in Source, SAP Landscape Transformation (SLT) and Target systems.
What is the Requirement:
To Replicate a table from SAP S/4HANA system in to DB2 Z/OS with changed Field Names, Datatypes and removing unwanted columns.
You can see that in the target structure the column MANDT is removed, some of the Columns like ERSDA, ERNAM are renamed as CREA_DATE, CREATED_BY respectively(Compare the two structures above to refer to all the fields that has different name in source and target). Also, there are Columns which has a different Data type in Source compared to Target, for Eg: MATNR in Source is CHAR 40 where as in Target it is INTEGER 4 (Please refer to the structures above for details on different data types in source and target)
Just For your reference I am providing some additional info on the Data types how it can be considered w.r.t mapping between SAP and DB2
The lengths you are seeing are DB2 INTERNAL LENGTH for the Datatype, not the length looking at the data.
Datatype SMALLINT length 2 can hold values up to 32,768 but it only takes 2 Bytes (15 bits) to hold the value.
Your MATNR field may be 40 characters but the values in there are always numeric and do not exceed the limits listed below for INTEGER.
Data type |
Denotes a column of… |
SMALLINT |
- Small integers. A small integer is binary integer with a precision of 15 bits. The range is -32768 to +32767. |
INTEGER |
- Large integers. A large integer is binary integer with a precision of 31 bits. The range is -2147483648 to +2147483647. |
DATE |
- A date is a three-part value representing a year, month, and day in the range of 0001-01-01 to 9999-12-31. |
Configuration Steps:
◉ RFC Connection to Source System from SAP Landscape Transformation (SLT):
Go to TCODE – SM59 and Create a RFC connection to Source system
Make sure the RFC connection test is successful
◉ DB connection to Target DB in SAP Landscape Transformation (SLT):
Go to TCODE – DBACOCKPIT
Create a DB connection to DB2 Z/OS and click on Database connections
Create the connection with the details obtained from DB2 DBA
Click on TEST button to check the connection to DB2 is working fine
◉ Replication Configuration:
Go to TCODE – LTRC
Create the MT ID with all the connection details of source and Target.
Before setting up any other configuration go to TCODE – LTRS
Navigate to the MT ID that was created earlier and Add the Source table
Right click on the TABLE SETTINGS and ADD TABLE
Enter the Source table name and click OK
Now you can see the table listed under Table Settings
Double Click on the Table name to make changes to the structure as per the requirements in the Target. In this Screen you have to enter the name of the TARGET TABLE – “TAO012SAP_ITEMXREF_SAP_QA”
I have selected the Refresh behavior as NO ACTION, this is because I will ask the DB2 DBA to create the table manually, before replication the table.
Now Navigate to the tab TABLE STRUCTURE and validate the table structure in comparison with the Source table in SAP S/4 HANA
Based on the source structure create the target structure as per the target structure mentioned below
Field MATNR has to be excluded and certain fields are to be changed to different names and different data types
Click on View Modified Table Structure to see the structure of the table in target
From the below screen make sure the table structure from SAP Landscape Transformation (SLT) is matching with the table structure in DB2 Z/OS, Since the DATS field type from SAP cannot be converted to DATE in DB2 the only option available is to create those fields in target as CHAR or VARCHAR data type
Now click on the create table button to generate the SQL statement which will be given to DB2 DBA to create the table in Target. ( The earlier target structure screenshots that were mentioned in this doc are after the DBA creating the table based on this script )
Copy the text on to a notepad to make required adjustments to the script as highlighted below
Actual Script:
— CREATE_TABLE
— Generated at 20210202 115316 by CISK4 for DB2 on QL1/100
CREATE TABLE “SAPPOQ”.”TAO012SAP_ITEMXREF_SAP_QA”
( “MATNR” BIGINT
NOT NULL DEFAULT 0
,
“MEINH” VARGRAPHIC ( 000003 )
NOT NULL DEFAULT ‘ ‘
,
“EAN11” DECIMAL ( 000018 )
NOT NULL DEFAULT 0
,
“RAPIDITEM” INTEGER
NOT NULL DEFAULT 0
,
“COMMSUBCLS_CODE” VARGRAPHIC ( 000001 )
NOT NULL DEFAULT ‘ ‘
,
“GENRC_ARTICLE_ID” VARGRAPHIC ( 000040 )
NOT NULL DEFAULT ‘ ‘
,
“CREA_DATE” VARGRAPHIC ( 000008 )
NOT NULL DEFAULT ‘00000000’
,
“CREATED_BY” VARGRAPHIC ( 000012 )
NOT NULL DEFAULT ‘ ‘
,
“LAST_UPD_DATE” VARGRAPHIC ( 000008 )
NOT NULL DEFAULT ‘00000000’
,
“LAST_UPD_BY” VARGRAPHIC ( 000012 )
NOT NULL DEFAULT ‘ ‘
,
“DIV_NUM” SMALLINT
NOT NULL DEFAULT 0,
“DEPT_NUM” SMALLINT
NOT NULL DEFAULT 0,
“COMM_NUM” SMALLINT
NOT NULL DEFAULT 0,
“SRP_AMT” DECIMAL ( 000007, 000002 )
NOT NULL DEFAULT 0,
“VNDR_NUM” VARGRAPHIC ( 000005 )
NOT NULL DEFAULT ‘ ‘
,
“UMREZ” SMALLINT
NOT NULL DEFAULT 0,
PRIMARY KEY (
“MATNR”,
“MEINH”,
“EAN11”,
“RAPIDITEM”) ) CCSID UNICODE
Modified Script: Based on the DBA guidelines that they don’t allow data type “VARGRAPHIC” the above script is modified as below, the differences between the scripts is highlighted in BLUE.
sql(‘tgtXXXXX’, CREATE TABLE “SAPPOQ”.”TAO012SAP_ITEMXREF_SAP_QA”
( “MATNR” BIGINT
NOT NULL DEFAULT 0
,
“MEINH” CHAR ( 000003 )
NOT NULL DEFAULT ‘ ‘
,
“EAN11” DECIMAL ( 000018 )
NOT NULL DEFAULT 0
,
“RAPIDITEM” INTEGER
NOT NULL DEFAULT 0
,
“COMMSUBCLS_CODE” CHAR ( 000001 )
NOT NULL DEFAULT ‘ ‘
,
“GENRC_ARTICLE_ID” CHAR ( 000040 )
NOT NULL DEFAULT ‘ ‘
,
“CREA_DATE” CHAR ( 000008 )
NOT NULL DEFAULT ‘00000000’
,
“CREATED_BY” CHAR ( 000012 )
NOT NULL DEFAULT ‘ ‘
,
“LAST_UPD_DATE” CHAR ( 000008 )
NOT NULL DEFAULT ‘00000000’
,
“LAST_UPD_BY” CHAR ( 000012 )
NOT NULL DEFAULT ‘ ‘
,
“DIV_NUM” SMALLINT
NOT NULL DEFAULT 0,
“DEPT_NUM” SMALLINT
NOT NULL DEFAULT 0,
“COMM_NUM” SMALLINT
NOT NULL DEFAULT 0,
“SRP_AMT” DECIMAL ( 000007, 000002 )
NOT NULL DEFAULT 0,
“VNDR_NUM” SMALLINT
NOT NULL DEFAULT 0,
“UMREZ” SMALLINT
NOT NULL DEFAULT 0,
PRIMARY KEY (
“MATNR”,
“MEINH”,
“EAN11”,
“RAPIDITEM” ) ) CCSID UNICODE) IN DATABASE SAPPOQ NOT LOGGED’);
The modified script is sent to the DB2 DBA to get the table created in target and the table will be created as displayed in the below screenshot
Now that the target table is ready to receive the data, lets now go back to LTRC to complete replication setup
Go to TCODE – LTRC
Select the MTID – in this case its 00H >> 1. Go to the tab Table Overview >> 2. Click on Data Provisioning >> 3. Select the radio button Replication >> 4. Enter the name of the Source Table and Click OK
Here comes the most important step, the moment you click ok after adding the table for replication, 1. Refresh the screen >> 2. select the table >> 3. Actions >> Suspend Replication immediately without any delay to configure the mapping of the fields.
Once the table replication is suspended go the tab 1.DATA TRANSFER MONITOR >> 2. Double click on the table name
Now click on the Display button on the next screen
In the following screen double click on “Recipient Range: Edit Structures and Fields” to get this configuration from display mode to change mode.
Now click on Define Field Relations
In this screen just drag and drop the field from left to the corresponding field on the right
Save and go back to the LTRS Screen and to the tab 1. TABLE OVERVIEW >> 2. Select the table >> 3. Click on Actions and select Resume Replication
Go to the Tab Load Statistic to verify the number of records replicated to target and make sure it matches to the count in source.
For any errors or trouble shooting please check the tab Application Logs or TCODE SLG1
No comments:
Post a Comment