How to start Data Provisioning without using the Data Provisioning UI
Hi,
Due to several reasons you want to use an action which is not available in HANA Studio or you want to start data provisioning for a table which is not displayed in the data provisioning UI due to any problem. Especially in the N:1 use case, only the DDIC (DD* tables) information for the latest connected target are available at the Data Provisioning UI. Also when you replicate from a non-SAP source system – the Metadata information about available tables will not updated (only one time loaded to HANA). If a new table will be created on the source, you can start the replication for this tables by following this How To.
General Process:
Start data provisioning via SQL in HANA Studio
The communication between the HANA Studio and the SAP LT Replication Server is handled via table RS_ORDER. This table is cerated by the SAP LT Replication Server in the corresponding schema, when a new configuration is defined in the SLT configuration and monitoring dashboard. All data provisioning requests for a certain schema will be added to table RS_ORDER. The master job of the respective configuration permanently checks table RS_ORDER for new entries. In case of new entries, the requested action will be processed and the record will be deleted from RS_ORDER. Therefore RS_ORDER contains only those data provisioning requests, which are not yet handeld by the SAP LT Replication Server.
To start a data provisioning via SQL in HANA studio the following record has to be inserted into table RS_ORDER in the respective schema. Depending on the
version of LT Replication Server, the structure of table RS_ORDER looks different in the target schema.
Version DMIS 2011 SP4 / DMIS 2010 SP9 (or higher)
Table strucuture of RS_ORDER
| Name | Key | SQL Data Type | Dim | Description |
|---|---|---|---|---|
| SID | X | NVARCHAR | 3 | Source System ID |
| CI_HOST | X | NVARCHAR | 256 | Hostname of Source System |
| TABLENAME | X | NVARCHAR | 128 | Table name |
| ORDERID | INTEGER | Data provisioning order ID (not yet used) | ||
| ACTION | NVARCHAR | 1 | Requested data provisioning action | |
| TABLE_TYPE | NVARCHAR | 64 | Table Type (space = COLUMN, ROW, HISTORY_COLUMN) | |
| REFRESH_BEHAVIOR | NVARCHAR | 1 |
SLT behaviour for target table in case of reload (space/D = Drop Table, T = Truncate Table, N = Keep Table |
|
| LANGUAGE | NVARCHAR | 1 | Language for column description |
Source System ID (SID) can be derived from table “SYS_REPL”.”RS_REPLICATION_COMPONENTS” field SID.
Hostname of the Source System (CI_HOST) can be derived from table “SYS_REPL”.”RS_REPLICATION_COMPONENTS” field CI_HOST.
SQL Statement:
insert into “SCHEMA”.”RS_ORDER” values(‘SID’,’hostname’,’tablename’,0,’R’,”,”,”)
Example:
insert into “REPLICATION_TARGET”.“RS_ORDER” values(‘BZ4′,’ldcibz4_BZ4_70′,’DD02L’,0,’R’,’COLUMN’,’D’,’E’)
Version DMIS 2011 SP2-3 / DMIS 2010 SP7-8
Table strucuture of RS_ORDER
| Name | Key | SQL Data Type | Dim | Description |
|---|---|---|---|---|
| SID | X | NVARCHAR | 3 | Source System ID |
| CI_HOST | X | NVARCHAR | 256 | Hostname of Source System |
| TABLENAME | X | NVARCHAR | 128 | Table name |
| ORDERID | INTEGER | Data provisioning order ID (not yet used) | ||
| ACTION | NVARCHAR | 1 | Requested data provisioning action |
Source System ID (SID) can be derived from table “SYS_REPL”.”RS_REPLICATION_COMPONENTS” field SID. Hostname of the Source System (CI_HOST) can be derived from table “SYS_REPL”.”RS_REPLICATION_COMPONENTS” field CI_HOST.
SQL Statement:
insert into “SCHEMA”.“RS_ORDER” values(‘SID’,’hostname’,’tablename’,0,’R’)
Example:
insert into “REPLICATION_TARGET”.“RS_ORDER” values(‘BZ4′,’ldcibz4_BZ4_70′,’DD02L’,0,’R’)
Version DMIS 2010 SP4-6
Table strucuture of RS_ORDER
| Name | Key | SQL Data Type | Dim | Description |
| TABLENAME | X | NVARCHAR | 128 | Table name |
| ORDERID | INTEGER | Data provisioning order ID (not yet used) | ||
| ACTION | NVARCHAR | 1 | Requested data provisioning action |
SQL Statement:
insert into “SCHEMA”.“RS_ORDER” values(‘TABLENAME’,0,’R’)
Example:
insert into “REPLICATION_TARGET”.“RS_ORDER” values(‘DD02L’,0,’R’)
The following data provisioning actions are available:
| Action Code | Description |
|---|---|
| T | Create table without loading any data |
| L | Load (w/o Replication) |
| R | Start Replication (includes the initial load) |
| C | Stop Load or Replication (also if status still on scheduled) |
| S | Suspend/Pause Replication (only possible for tables with replication in process) |
| W | Resume/Continue Replication (only possible for tables suspended before) |
| M | Activate Delta Recording (activate trigger without starting replication) |
| Q | Start Replicate Process (without initial load; prerequisite: delta recording has been activated before) |
Action T – Create Table
Description
Creates the table in the HANA target schema based on the strucuture on the source system (including manual defined structural changes). If the table already exist, it will be dropped and recreated.Action is not available in the HANA data provisioning UI.
Next Actions
All actions possible
Action L – Load Table
Description
Creates the table in the HANA target schema based on the strucuture on the source system (including manual defined structural changes). If the table already exist, it will be dropped and recreated. Afterwards the current records of the table are loaded to the table on the HANA database.
Next Actions
All actions possible
Action R – Start Replication
Description
Creates the table in the HANA target schema based on the strucuture on the source system (including manual defined structural changes). If the table already exist, it will be dropped and recreated. As soon as the delta recording is active on the source system (logging tables and triggers are created) the initial load for the table starts while the delta recording logs all changes for that table in the source system. After the initial load is done, table will switch to replication mode and transfer the recorded changes.
Next Actions
Action C – Stop Replication
Action S – Suspend/Pause Replication
Action C – Stop Replication
Description
Stops the replication process and deactivates the delta recording on the source system. This action can only be executed for tables in status “Replication in Process”
If replication is started again afterwards, the initial load has to be repeated as delta recording was deactivated for a certain time
Next Actions
All actions possible
Action S – Suspend/Pause Replication
Description
Pause the replication process but keeps the delta recording active on the source system. This action can only be executed for tables in status “Replication in Process”
Next Actions
Action R – Resume/Continue Replication
Action W – Resume/Continue Replication
Description
Continues the replication process of a previously paused table. As delta recording was still active, replication can continue without the need of a new initial load. This action can only be executed for tables in status “Suspend Executed”
Next Actions
All actions possible
Action M – Activate Delta Recording
Description
Activates the delta recording on the source system (logging tables and triggers are created)
Action is not available in the HANA data provisioning UI.
Next Actions
Action T – Create Table
Action Q – Start Replicate Process
Action Q – Starts Delta Recording
Description
Starts the delta replication for a certain table without doing an initial load. Delta recording is not activated in this step and therefore must be started upfront using action M. Also the table on HANA side must exist before this action is started. The table can be created manually or via action T.
Action is not available in the HANA data provisioning UI.
Next Actions
Action C – Stop Replication
Action S – Suspend/Pause Replication
Please keep in mind that these are expert functions. This blog post should give you a deeper understanding of the technology behind. In the usual setup the Data Provisioning UI is sufficient to run a replication scenario.
Best,
Tobias
Very Informative....
Excellent content Tobias.
Really Very Informative...
Thanks for a handy piece of a doc, nice work ....
Tobias - this is great to know and gives me some ideas on SLT options for moving configurations more consistently. From an understanding point of view, as seen in , the replication settings in each instance in a landscape (DEV/QA/PROD) must be manually initiated at a table level.
What I mean by this is that for a given project/solution, you may need 60 tables or so to support the resulting analytics. From what I understand, there are three options for configuring table replication in each system in the landscape.
1) Initiate from Studio UI for each table required in the solution in each system
2) Execute some predefined SQL statements as you outlined above, which is a reusable set of scripts that can be executed in each system
3) Directly from SLT using IUUC_SYNC_MON in each system
From a consistency standpoint, it seems that option 2, as you outlined above, would provide the best results in terms of completely repeatable system setup - this would avoid any human errors.
Thanks,
Justin
Hi Justin,
there is one additional option - when you use the studio ui for data provisioning and choose "replicate" you get within the selcetion popup also the option to load from an file. You could store all 60 table within this excel file and just upload.
Just one remark - when you use option 3 and select in LTRC UI - you also get the status information within the HANA Studio. So it is more the personal flavour - use HANA or SLT directly.
Best,
Tobias
Tobias - now that we have SLT available for use with HANA, I am trying to get the "Load from File" option to work within Studio and can't seem to get it to work no matter what format (.csv) I put the file in. If I hit the button and actually select my prepared file, nothing happens at all. I would have expected the tables to be populated into the right hand pane, but still nothing happens.
Any tips to use this feature? We are on SPS6 Rev 62 HANA and SLT is 7.02 SP08, DMIS_2011 SP4.
Thanks,
Justin
Just wanted to add another tidbit of information on this - it seems that the flat file format for the "Load from File" option within the Studio UI needs to be in the following format.
Structure
Target Schema, Table Name
<REPL_SCHEMA>, <TABLE_NAME>
Example with data
Target Schema, Table Name
DE2_DH2, CE12000
Took me some time to figure it out but if you have a large number of tables to replicate, simply save in this format (in .csv) and you can upload them all within the Replicate function in one shot.
Thanks,
Justin
Yes, the status is with every option visible on the studio itself.
Best,
Tobias
Hi Tobias,
Can you please tell what all things can be done using the new Provisioning folder introduced in SPS6 regarding Federation?
Regards,
Vivek
Hi Vivek,
For federation features refer to the released Notes:
HAPPY HANA 🙂
Deepak Chodha.
Thanks Deepak
Great article Tobias! It saved me some time during this phase when implementing HANA Live for ERP.
Thank you!
Joseph Banegas
SAP BI HANA Solutions Architect
Very Informative....
Hi Tobias,
Had a Query on the resume replication option. It states as below, Highlighted the statement in question.
Action W - Resume/Continue Replication
Description
Continues the replication process of a previously paused table.
As delta recording was still active, replication can continue with the need of a new initial load. This action can only be executed for tables in status "Suspend Executed"
Next Actions
All actions possible
Do we have to perform an initial load again, even though deltas are still active? As i understand since deltas are still active, it will just run from where it was left off upto the current state.Please correct if i missed anything here?
Thanks
Hey,
you are right - it will continue withOUT a new initial load.
I edit it in the blog.
Thank you!
Best,
Tobias
Very good info..
Hi Tobias, nice post.
I'm trying to implement the RS_ORDER status table on a situation where can't lose the data on the target table from SLT.
What I did is:
Insert the status code 'M' with table name in RS_ORDER, after that, on the data provisioning UI, the status changed to "Record Only"
Insert the status code 'Q' with table name in RS_ORDER, after that, it start the replication but we receive a error related to 'Status Check Error'.
Do you know what can be this issue?
Thanks,
Rodrigo.
Hi,
have you checked the application log in ltrc?
Best,
Tobias
Hi Tobias,
Need your help in using the option: "Action M – Activate Delta Recording"
For a particular table we have different entries in Source (ECC) and Target (HANA) system.
When we use this, the data in target system is becoming zero.
Do we have any options or parameters changes, where can use Action M without making the entries in target system to 0. And than use "Action Q – Starts Delta Recording" for replicating the delta changes.
Please suggest.
Thanks & Regards,
Sridhar.