Parallel Replication taking time using SLT configuration

[Saritha_K]

Hi Experts,

My SLT version is 2011_1_731 sp 5. My configuration works fine for small tables but it becomes slow when it comes to loading large transparent table like CE11000. I followed steps mentioned in blog " How to improve the initial load" written by Tobias and started replication with below parameters-

My replication remains in initial load process with calculation showing 'S for more than 3hrs and then it started showing status 'P' when 1LT/MWB_CALCSACP_001_Z_CE11000 started running.

in table IUUC_PRECALC_OBJ my entries are -

conv object- CE11000

NUMREC 145000000

in table IUUC_PERF_OPTION my entries are -

tabname - CE11000

PARALLEL JOBS- 5

SEQNUM - 20

READING TYPE 5

and my SLT Server configuration has below count of jobs -

No of Data Transfer jobs - 4

Initial load jobs - 7

No of calculation jobs -2

If I do a table health check in its calculation phase ( status P), using tcode- LTRC-> Expert Functions-

Checks for table CE11000 (mass transfer ID 001) started

Last action is R-Load tables and start replication, status is O-Obtained (Scheduled); "Is Last" = Yes (from 26.11.2013 05:28:54)

Current action is R-Load tables and start replication; status is O-Obtained (Scheduled)

Log table /1CADMC/00000212 created in sender system, and has 0 records

The following triggers are defined for table CE11000:

- /1LT/00000212INS

Trigger CE11000; status is 2-Activated

Latency data for table CE11000 is not available

Migration object =Z_CE11000_001;Defined? = Yes, Generated? = Yes (X = Yes, blank = No)

No access plan found for table CE11000

Access plan set as 'Not Calculated' in table DMC_MT_TABLES for table CE11000

Checks  for table CE11000 finished (mass transfer ID 001)

The troubleshooting activity has detected critical issues

Currently I can see number of records getting updated in the job log of 1LT/MWB_CALCSACP_001_Z_CE11000 but I cannot see any ACC_PLAN_CALC_001_0 jobs.

Please suggest your opinion on below points to accelerate initial load-

1. options that we can check for monitoring if something in replication is failing apart from above steps

2. parameters that need to be added additionally incase required

3. any correction notes required to be installed for our mentioned configuration.

Regards,

Saritha

[Saritha_K · Accepted solution]

Hi All,

To add to my above question, the replication failed for job /1LT/MWB_CALCSACP_001_Z_CE11000_ / 08463900 after running for about 4hrs(which means my replication in initial load  ran for status S-3hrs and status P- 4hrs) with below error- Internal session terminated with a runtime error DBIF_RTAB_SQL_ERROR

ST22 messages- short text

SQL error 1691 when accessing table "DMC_INDXCL".

Database error text

Error Text of the Database: "ORA-01691: unable to extend lob segment

SAPSR3.SYS_LOB0000327172C00006$$ by 128 in tablespace PSAPSR3"

Please suggest some steps on how to resolve it.

Regards,

Saritha

[Former Member · Accepted solution]

Hi Saritha,

two remarks:

1. Once the status has switched from 'S' to 'P' that means that the parallelization is defined. You can now start, as shown in the blog, additional access plan calculation jobs. This will result in a considerably shorter time to finish this activity.
2. Concerning the shortdump, you need to keep in mind that in the course of the access plan calculation activity, the data to be transferred to HANA are first stored in the source system in an INDX-like table named DMC_INDXCL. You need to make sure that enough space is available in the corresponding tablespace, which here is PSAPSR3. We normally assume a compression factor of 1:10, so if you want to select 300 GB of data for loading to HANA, you need to provide at least 30 GB, possibly a bit more, of tablespace to make sure that the above problem will not occur

Kind regards,

Guenter

[Saritha_K · Accepted solution]

Hi Guenter,

-Thanks for your reply, we have data of around 485 GB, and we have increased it by 60GB but I have a query now- from this step, if I have to restart my failed replication, how should I do it?

My /1LT/MWB_CALCSACP_001_Z_CE11000_ / 08463900 had read 86,000,000 records and as per our estimate it should have reached 145, 000,000.

Is there a way to resume by setting any indicator in the expert options of tcode- mwbmon?

Regards,

Saritha

[Former Member · Accepted solution]

I do not know if there is restart capability after an error. We've been having short dumps in our parallel processing. To start from the beginning we have successfully used the steps in this document. We have not had to use the ABAP steps.

http://maben.homeip.net/static/computers/sap/HANA/starter/0410%20-%20Data%20Provisioning%20-%20SLT%2...

[Saritha_K · Accepted solution]

Hi Pitirim Tan,

Thank you for the pdf, I was able to retrigger the job /1LT/MWB_CALCSACP_001_Z_CE11000_ which had got cancelled and now I have a new problem.

My failed job started with access plan 2 which triggered plan 3, 4 in sequence. As per Guenter's suggestion when I set the additional access plan setting, it triggered a new job for plan 5 as well. Now the status is as below-

/1LT/MWB_CALCSACP_001_Z_CE11000_2 instances are running parallel.

Is there way to restart the job only for access_plan 1 which is missing now?

Another query that I have is, whether on acp jobs completion, will my load run successfully?

Regards,

Saritha

[Former Member · Accepted solution]

Hi Saritha,
unfortunately a job for access plan calculation that has run in the source system and got cancelled needs to start again from scratch. But the parallelization should alleviate this problem, as the job only processes a subset and not all the data of the respective table.

Kind regards,

Guenter

[Saritha_K · Accepted solution]

Hi Guenter,

Sorry I couldn't interpret your answer.

Do you mean to say - that the loading will start automatically after these 2 instances get over and load tables into HANA with whatever rows in present in DMC_INDXCL table?

Within 2-3hrs I expect my access plans/1LT/MWB_CALCSACP_001_Z_CE11000 jobs to get over.

If I go to tcode- mwbmon-steps -> calculate access plan - > execute function and give below parameters- mass transfer id - 0001 tablename - CE11000 Number of jobs to be scheduled- 5 and also mark the RESTART option this time, alongwith setting of do not recalculate precalculations and access plans that have been calculated                                                                                                                                                                                                          what will be the impact- will it restart with my missing access plan and ignore the remaining ones as it has been calculated?                                                                                                                                                                                                                                                             or                                    

I also know to trigger the cancelled job again from SM37  by passing variants at runtime

or

should I ignore the access plan 1 data for now, as DMC_INDXL will have some 86, 000,000 rows that were stored before job got cancelled,  so let it proceed with loading after above jobs get  completed?                                                                                                                                                                                                                                             Please give some overview on which option  I should go for and why  ..

Regards,

Saritha

[Former Member · Accepted solution]

Hi Saritha,

there is no need to directly start the jobs from SM37, but you can do it from MWBMON the way you described. Just keep one thing in mind: If you mark the restart option while 2 jobs are already running, and you want to start another 2 jobs, you need to specify 4 as the number of jobs. The restart option means that the number of jobs you specify is the TOTAL number of jobs that should run, so we calculate the difference between the number of currently running jobs and the number of jobs that you specify, and schedule this number of jobs. As soon as all access plans are calculated, the overall status of the table will switch to calculated = 'X', and the initial load will start.

Kind regards,

Guenter

[Saritha_K · Accepted solution]

Hi Guenter,

Yesterday we tried out with the restart option, but it didn't work out due to some reason, the calculation process remainined in 'P' status as a result , the load process never got triggered. Finally we had to clean up the tables after replication error and have started a fresh start. Hope this gets completed .

Currently I have set 2 additional calculation plans to run parallely.

Just  one more query from my end, after these access plans get completed, would the load jobs also run parallely or do i need to set it those jobs as well parallely after the load jobs 1st instance starts running from mwbmon - steps - load data and any particular field that I need to enter as such.

Regards,

Saritha.

[Former Member · Accepted solution]

Hi Saritha,
this is strange, in such a case this should rather be handled with a OSS message.

Concerning the parallelization of the initial load, that will work without any forther manual activities. You only need to keep in mind that by default, we don't allow more than three jobs running in parallel for a certain table. If you need more, the ideal case is that you have defined the degree of parallelization in transaction iuuc_repl_content before starting the replication of that table. Otherwise you can define it in transaction MWBMON.

Kind regards,

Guenter

[Saritha_K · Accepted solution]

Hi Guenter,

This time the access plans were calculated without any interruption:) , and I have 4 loads running in parallel currently  as in my LTR configuration I have "initial load jobs count =4"

From iuuc_repl_content, how do I set this parallelization? frm mwbmon, I believe its the same ways as I stated above right..

Regards,

Saritha

[Former Member · Accepted solution]

Hi Saritha,
when you have selected the configuration in iuuc_repl_content, you choose the tab "IUUC_PERF_OPTION". There, you create an entry for the table in question, and maintain the field "No of Parallel Jobs". But this needs to be done before you initiate the replication of the respective table.

Kind regards,

Guenter

[Saritha_K · Accepted solution]

Hi Experts,

The steps documented for parallel replication of transparent table CE11000 worked out fine for me. I tried the same steps for cluster table BSEG with reading type '4' this time and it started out well with creation of 2 access calculation plans, 001 and 002. My job with access plan 002 got over after fetching around 24million records but there has been no update in job log 001 for more than 19hrs.

Is there some additional setting that needs to be done when we try with replicating of large cluster tables?

Why is it that replication of transparent table is faster than cluster table replication?

I also saw another approach where in we give selection criteria's based on primary key for multiple access plans, but here we do not have an idea of how the data is widely distributed across year, BELNR(Document Number) etc in order to judge the selection criteria.

Please suggest some technique which can be used for faster access of cluster tables into HANA.

Regards,

Saritha

[Former Member · Accepted solution]

Hi Saritha,

defining selection criteria based on the primary key is exactly what we do with reading type 4. Concerning the problem with an access plan not being processed, it is necessary to analyze on DB level why there is no progress. Among others, possible problems might be:

• no space left in the tablespace to which table DMC_INDXCL is assigned
• in case of Oracle, in the course of time, it becomes more and more difficult to return the data as they were at the time the selection began. This might finally (but often only after some time with DB inactivity has elapsed) result in an ORA-01555 "snapshot too old" shortdump

Cluster processing is always more time consuming because the data need to be declustered (converted from the internal storage format to the format of the logical cluster table). Also note that the number of records you specify, for the purpose of parallelization, in table IUUC_PRECALC_OBJ is in this case the number of RFBLG records, not the number of BSEG records. I would recommend a higher degree of parallelization (for example, 5 to 10 access plans, rather than only 2) in order to achieve a shorter time for this access plan calculation processing step, and (in case of Oracle) to minimize the risk of an ORA-01555 cancellation.
Kind regards,
Günter

[Saritha_K · Accepted solution]

Hi Guenter,

It wasnt due to tablespace issue for sure, but it seems my numrec for IUUC_PRECALC_OBJ that was not correct, because I was not able to get the number of records thru both backgroun and foreground processing.Will try again with different selection parameters. Thank you for the valuable inputs.

Regards,

Saritha

[Saritha_K · Accepted solution]

Hi Experts,

Need your help to replicate BSEG with reading type 4.

I was referring to SLT – Reading Type 4 Replication PDF and in IUUC_REPL_CONTENT I have set partition by GJAHR to 5 parts this time,

I have a count of BSEG table now which is around 176779953 records and I plan to split in 9 access plans with slt configuration set to below set of jobs-

data transfer jobs - 5

initial load jobs -

calculation jobs -5    

Log tables etc got created after it was replicated but later on it got stuck meaning the table appeared 'in process' state" table overview" tab but it just didn't appear in "Data transfer monitor" tab later on.

Can you please list out basic steps to carry out smooth replication of cluster table BSEG?

Note- HANA server version is upgraded to SP07

Regards,

Saritha

[Saritha_K · Accepted solution]

Hi Experts,

With the latest version of SLT(DMIS_2011_SP6), are there any changes in the steps for parallel replication of transparent/cluster tables?

Would there be any additions required in table DMC_ACSPL_SELECT(a new table now available)?

Thanks,

Saritha K

[Former Member · Accepted solution]

Hi Saritha,

Indeed: By means of table DMC_ACSPL_SELECT you now can configure the parallelization of the replication of a certain table (regardless if transparent or cluster). For details, please refer to the online documentation of this table (SE11 - view table DMC_ACSPL_SELECT - choose "Goto - Documentation" from the menu). One important point needs to be added: In this table, you will define selection criteria for at least one primary key field of the corresponding table. You need to make sure that the secondary index of the logging table is defined in such a way that this selection is well supported. Example: You want to replicate BKPF (or RFBLG / BSEG) in multiple parallel threads, and your only criterion to define the subsets is the document number. Then the secondary index of the respective logging table must not have the default field order:

IUUC_PROCESSED
MANDT
BUKRS

BELNR

...
But instead:
IUUC_PROCESSED
BELNR
....
The ideal case is that you predefine the parallelization before you even select the corresponding table for replication. That means, you create a corresponding record in table IUUC_PERF_OPTION, where you would, in the example above, specify BELNR as the "parallel fieldname" (which means, the field for which we define boundaries to subdivide the records in disjoint subsets). In this case, the field sequence mentioned above would automatically be used. Otherwise, you would need to redefine the secondary index of the logging table after it already has been created, which is more difficult.
Kind regards,
Guenter

[Former Member]

Hello All ,

i just observed there are lot of ( Nearly 40 jobs ) /1LT/MWB_LOAD_ACP_<MTID>_00001 jobs running in SLT system . Which are generated from the option MWBMON --> Load Data --> Execute function .

What these jobs do in the system and is there any impact with these jobs in the system ?

Regards,

Pavan P

[Former Member]

Saritha,

I would always suggest to do the Initilisation with SAP Data Services for large tables.Defintely you need to have DS in your landscape for that.

[tobias_koebler]

Hi Joy,

can you give some figures on the performance improvement DS vs. SLT for the initial load? Our experience shows that most customer use SLT also for initial load.

Best,

Tobias

[Former Member]

Tobias -- Frankly speaking I have'nt tried that.

For large tables we have always initialised through DS & there is not a single issue till date..Touchwood!!

Surely I would like to test those large tables through SLT in future.

[tobias_koebler]

Okey, yeah I would be pretty interessting. I think especially for really large BSEG (some SLT customers have 50TB+) tables, etc. I would love to see DS figures to give further recommendations.

Especially with the new SLT+DS integration this could lead to new use cases.

Best,

Tobias

[Saritha_K]

Hi Tobias,

I would like to know more about the SLT capability when we have a landscape consisting ECC, BW to be replicated into BW on HANA.

I have some queries around this architecture.

Can we please discuss this offline if possible?

Thanks,

Saritha

[tobias_koebler]

Yes, just drop me a mail.

Best,

Tobias

[Former Member]

Hi Tobias,

I am facing an issue while doing selective data load by using DMC_ACSPL_SELECT. Actually I need to do selective data load day wise. Now challenge is when I'm executing it Im getting data in only that particular selection and rest of the data is getting deleted.

Regards,

Biswabrata