Unicode export:Table-splitting and package splitting

[Former Member]

Hi SAP experts,

I know there are lot of forums related to this topic, but I have some new questions and hence posting a new thread.

We are in the process of doing unicode conversion in our landscape(CRM 7.0 system based on NW 7.01) and we are running on AIX 6.1 and DB2 9.5. The database size is around 1.5 TB and hence, we want to go in for optimization for export and import in order to reduce the downtime.As a part of the process, we have tried to do table-splitting and parallel export-import to reduce the downtime.

However, we are having some doubts whether this table-splitting has actually worked in our scenario,as the export has exeucted for nearly 28 hours.

The steps followed by us :

1.) Doing the export preparation using SAPINST

2.) Doing table splitting preparation, by creating a table input file having entries in the format <tablename>%<No.of splits>.Also, we have used the latest R3ta file and the dbdb6slib.o(belonging to version 7.20 even though our system is on 7.01) using SAPINST.

3.) Starting with the export using SAPINST.

some observations and questions:

1.) After completion of tablesplitting preparation, there were .WHR files that were generated for each of the tables in DATA directory of export location. However, how many .WHR files should be created and on what basis are they created?

2.) I will take an example of a table PRCD_CLUST(cluster table) in our environment, which we had split. We had 29 *.WHR files that were created for this particular table. The number of splits given for this table was 36 and the table size is around 72 GB.Also, we noticed that the first 28 .WHR files for this table, had lots of records but the last 29th .WHR file, had only 1 record. But we also noticed that, the packages/splits for the 1st 28 splits were created quite fast but the last one,29th one took a long time(serveral hours) to get completed.Also,lots of packages were generated(around 56) of size 1 GB each for this 29th split. Also, there was only one R3load which was running for this 29th split, and was generating packages one by one.

3.) Also,Our question here is that is there any thumb rule for deciding on the number of splits for a table.Also, during the export, are there any things that need to be specified, while giving the inputs when we use table splitting,in the screen?

4.) Also, what exactly is the difference between table-splitting and package-splitting? Are they both effective together?

If you have any questions and or need any clarifications and further inputs, please let me know.

It would be great, if we could get any insights on this whole procedure, as we know a lot of things are taken care by SAPINST itself in the background, but we just want to be certain that we have done the right thing and this is the way it should work.

Regards,

Santosh Bhat

[former_member709110]

For 1.2TB distribution monitor is defienately a fesible option considering the complexity you add to your over all process. You might have realised that by looking at the export timing you didn't gain also much.

Haven't done any oracle to maxdb migration. But taking clue from your comment that most of the R3load is sleeping, i would ask have you checked your OS setting and Maxdb setting as per standard SAP recommendation on the target.

Though not directly related , but you might want to look at the following thread

Regards,

Neel

[Former Member]

Hi Neelabha,

Thanks for your reply.

I realize that distribution monitor is much more complex to use.

Also, the SAP note 855772 says that do not use distribution monitor if you want to use different hosts for export and import(parallel export and import), which is the case in our scenario.

We have already done unicode conversion in our landscape for the sandbox and development systems, where the downtime was not of too much concern.

We would not like to change our approach now as it might not be a good idea.

However, my question here is that we would need some more clarity on the table-splitting and package-splitting aspects(as asked in my earlier post), in order to make sure that we get effective results for the optimization methods that we have used.

Regards,

Santosh

[former_member709110]

When you do a package spliting you basically split the standard packages (which goes by TABART) into multiple package, so that you can have more that one R3load working on your one original package. example say 1000 tables of APPL1, if you split it the standard package SAPAPPL1 into say 10 packages (obviously package spliting is goverend by size not count ) , now all your 1000 tables are split across 10 SAPAPPL1_# packages. So now 10 R3load can simultaneously work of the tables pertaining to you APPL1, instead of 1 earlier. Thus you save time and make it faster. What package spliting doesn't do is it doesn't spit any tables. So it you have 200GB table and you have choosen package size to be 1GB or 10GB. It will not split the 200 GB table. This table will still exist as a seperate package.

So to overcome the above table spliting comes to play. Through table spliting the table can be split into small part ( that is how you get WHR). But remember when we do table split the export can happen in parallel for all the split (as its only read during export), but you actually cannot import all the split of a table in parallel. So even if you have 10 R3load importing 10 of the split for the same table, actually 1 would be importing and 9 would be waiting. So it is very important that you come up with a proper import sequence otherwise your table splits (WHR) from one table might hold on to all the R3load process and other packages will just wait, other packages wont be picked for import as no R3load is available. Which will hit the overall timings.

Regards,

Neel

[Former Member]

Hi Neel,

Thanks for your prompt response again.

A have a few more questions now:

1.) Can package splitting and table-splitting be used together? If yes or no, what exactly is the procedure to be followed. As I observed that, the packages also have entries of the tables that we decided to split. So, does package splitting or table-splitting override the other, and only one of the two can be effective at a time?

2.) If you are well versed with table splitting procedure, could you describe maybe in brief the exact procedure?

3.) Also, I have mentioned about the version of R3ta and library file in my original post. Is this likely to be an issue?Also, is there a thumb rule to decide on the no.of splits for a table.

Regards,

Santosh

[former_member709110]

Hi,

First of all please ignore my very first response ... i have accidentally posted a response to some other thread...sorry for that

Now coming you your questions...

> 1.) Can package splitting and table-splitting be used together? If yes or no, what exactly is the procedure to be followed. As I observed that, the packages also have entries of the tables that we decided to split. So, does package splitting or table-splitting override the other, and only one of the two can be effective at a time?

Package splitting and table splitting works together, because both serve a different purpose

My way of doing is ...

When i do package split i choose packageLimit 1000 and also split out the tables (which i selected for table split) into seperate package (one package per table). I do it because that helps me to track those table.

Once the above is done i follow it up with the R3ta and wheresplitter for those tables.

Followed by manual migration monitor to do export/import , as mentioned in the previous reply above you need to ensure you sequenced you package properly ... large tables are exported first , use sections in the package list file , etc

> 2.) If you are well versed with table splitting procedure, could you describe maybe in brief the exact procedure?

Well i would say run R3ta (it will create multiple select queries) followed by wheresplitter (which will just split each of the select into multiple WHR files) ...

Best would go thought some document on table spliting and let me know if you have specific query. Dont miss the role of hints file.

> 3.) Also, I have mentioned about the version of R3ta and library file in my original post. Is this likely to be an issue?Also, is there a thumb rule to decide on the no.of splits for a table.

Rule is use executable of the kernel version supported by your system version. I am not well versed with 7.01 and 7.2 support ... to give you an example i should not use 700 R3ta on 640 system , although it works.

>1.) After completion of tablesplitting preparation, there were .WHR files that were generated for each of the tables in DATA directory of export location. However, how many .WHR files should be created and on what basis are they created?

If you ask for 10 split .... you will get 10 splits or in some case 11 also, the reason might be the field it is using to split the table (the where clause). But not 100% sure about it.

> 2) I will take an example of a table PRCD_CLUST(cluster table) in our environment, which we had split. We had 29 *.WHR files that were created for this particular table. The number of splits given for this table was 36 and the table size is around 72 GB.Also, we noticed that the first 28 .WHR files for this table, had lots of records but the last 29th .WHR file, had only 1 record. But we also noticed that, the packages/splits for the 1st 28 splits were created quite fast but the last one,29th one took a long time(serveral hours) to get completed.Also,lots of packages were generated(around 56) of size 1 GB each for this 29th plit. Also, there was only one R3load which was running for this 29th split, and was generating packages one by one.

Not sure why you got 29 split when you asked for 36, one reason might be the field (key) used for split didn't have more than 28 unique records. I dont know how is PRCD_CLUST split , you need to check the hints file for "key". One example can be suppose my table is split using company code, i have 10 company codes so even if i ask for 20 splits i will get only 10 splits (WHR's).

Yes the 29th file will always have less records, if you open the 29th WHR you will see that it has the "greater than clause". The 1st and the last WHR file has the "less than" and "greater than" clause , kind of a safety which allows you to prepare for the split even before you have downtime has started. This 2 WHR's ensures that no record gets missed, though you might have prepared your WHR files week before the actual migration.

> 3) Also,Our question here is that is there any thumb rule for deciding on the number of splits for a table.Also, during the export, are there any things that need to be specified, while giving the inputs when we use table splitting,in the screen?

Not aware any thumb rule. First iteration you might choose something like 10 for 50 GB , 20 for 100 GB. If any of the tables overshoots the window. They you can give a try by increase or decrease the number of splits for the table. For me couple of times the total export/import time have improved by reducing the splits of some tables (i suppose i was oversplitting those tables).

Regards,

Neel

Edited by: Neelabha Banerjee on Nov 30, 2011 11:12 PM

[Former Member]

Hi Neelabha,

First of all, thank you very much for your detailed reply.

A few questions again:

1.) You say:

My way of doing is ...

When i do package split i choose packageLimit 1000 and also split out the tables (which i selected for table split) into seperate package (one package per table). I do it because that helps me to track those table.

Once the above is done i follow it up with the R3ta and wheresplitter for those tables.

Followed by manual migration monitor to do export/import , as mentioned in the previous reply above you need to ensure you sequenced you package properly ... large tables are exported first , use sections in the package list file , etc

- Which of the actions of the above need to be performed manually?Does wheresplitter need to be executed manually?I am ware that R3ta part is handled by table-splitting preparation step of SAPINST. I know about manual migration monitor, but doesn't SAPINST take care of that in the latest versions?

2.) You say:

Well i would say run R3ta (it will create multiple select queries) followed by wheresplitter (which will just split each of the select into multiple WHR files) ...

Best would go thought some document on table spliting and let me know if you have specific query. Dont miss the role of hints file.

- Well, I have gone through the system copy guide of SAP, and it does not explain much really about table-splitting.The redbooks etc give too much of detail to be very honest.

I do know that the R3ta_hints.txt file plays and important role, We hade this file updated for our purpose, and also had copied it in the installation media, so that SAPinst uses our version of R3ta_hints.txt file. We have also verified that it has indeed used our file.

3.)You say:Rule is use executable of the kernel version supported by your system version. I am not well versed with 7.01 and 7.2 support ... to give you an example i should not use 700 R3ta on 640 system , although it works.

- We are still a bit unsure on this one. We have used 7.20 version file, but the *.WHR files were created successfully.So,may be it works?But the question is does it really work the way it should?:)

4.)You say:Not aware any thumb rule. First iteration you might choose something like 10 for 50 GB , 20 for 100 GB. If any of the tables overshoots the window. They you can give a try by increase or decrease the number of splits for the table. For me couple of times the total export/import time have improved by reducing the splits of some tables (i suppose i was oversplitting those tables).

-This one continues to be a mystery actually:)

One more last basic question:

Is it mandatory to use migration monitor manually for export and import when we use table-splitting/package splitting?

Regards,

Santosh

[former_member709110]

> - Which of the actions of the above need to be performed manually?Does wheresplitter need to be executed manually?I am ware that R3ta part is handled by table-splitting preparation step of SAPINST. I know about manual migration monitor, but doesn't SAPINST take care of that in the latest versions?

yes SAPINST can do all the three steps.

> - We are still a bit unsure on this one. We have used 7.20 version file, but the *.WHR files were created successfully.So,may be it works?But the question is does it really work the way it should?:)

You are telling me you dont know which version of kernel is supported by your system ?

> -This one continues to be a mystery actually:)

its a mystery because you haven't tried it out, after 1 or 2 iteration you would be able to figure it out.

> One more last basic question:

> Is it mandatory to use migration monitor manually for export and import when we use table-splitting/package splitting?

NOT AT ALL ... its upto your choice and comfort

Regards,

Neel

[Former Member]

Hi Neel,

Thanks again.

You got my point wrong here. I am not saying that I do not know which kernel version is supported by my system.

My question was, does using a R3ta file and the related library file belonging to version 7.20 being used on a 7.01 based-system cause the table-splitting to be ineffective?

so, its not mandatory to use migration monitor manually for the export or the import, we can conclude here.

So, lets take a case from here now.

We want to do a unicode conversion for a system and use table-splitting and parallel export-import as optimization techniques.

So, how would be go about it in brief?

Regards,

Santosh

[markus_doehr2]

Splitting up a table into several pieces is done via the file R3ta_hints.txt. In this file you put the key fields of the table which criteria is used to split up the table.

Because your system is running while R3ta runs and new data may be inserted while the table is being split, there is one WHR file which contains a WHERE condition with all the data but the ones created separately.

If now there are no fields defined in R3ta_hints.txt the system uses the primary key which may then result in one big package and N small (or even empty) packages.

I'm just in the process of exporting a 3.3 TB MaxDB system using table splitting and splitting up in packages. I have 92 tables which are being split (in max. 8 packages); both options can be used together. In my case this results in an export dump with roughly 450 packages (which is good).

MaxDB doesn't use R3ta_hints.txt, on that database the primary key of the table is used. For all other databases you need to make sure, there's an entry in R3ta_hints.txt for the table you plant to split.

Markus

[Former Member]

Hi Markus,

We are in the process of unicode conversion in our landscape for a system based on NW 7.01.

Our scenario is as follows:

1.) Database Size: 1.1 TB

2.) Database type: DB2 9.7

3.) Table-splitting preparation

4.) Parallel export-import in order to reduce downtime.

So, could you please help us with the best approach for this?

We continue to have a few doubts regarding table splitting:

1.) Does R3ta and library file belonging to version to 7.20, work fine on version 7.01 for table-splitting?

2.) Can we add multiple fields for a table to be split in the R3ta_hints.txt file?If yes,Does it have a positive impact always?or sometimes it can be negative as well?

3.) I will come back to this again, is there a thumb rule for deciding the no.of splits for a table?

Please guide us so that we can have the best possible optimization.

Regards,

Santosh

[Former Member]

Hello Markus/Neel,

I believe you both are probably busy and pre-occupied with other things.

But, it would be great if either of you could guide us in our activity,as it would be very helpful for us in better planning and execution for our activities.

Regards,

Santosh

[markus_doehr2]

> 1.) Does R3ta and library file belonging to version to 7.20, work fine on version 7.01 for table-splitting?

If you use a 7.20 kerne on the source system (Note 1636252 - Installing a 7.20 kernel in SAP Web AS 7.00/7.01/7.10/7.11) then use it also for the import.

> 2.) Can we add multiple fields for a table to be split in the R3ta_hints.txt file?If yes,Does it have a positive impact always?or sometimes it can be negative as well?

I would use a key field to split the table (or multiple) to improve performance. If you add a field that's no part of the primary key, R3ta will then generate an SQL file to create an index for that field.

> 3.) I will come back to this again, is there a thumb rule for deciding the no.of splits for a table?

No, I'd no go for more than 8 packages.

Markus

[Former Member]

Hi Markus,

So a few more things:

1.) I am not using the entire 7.20 kernel on the source. I used just the R3ta and library file belonging to 7.20 on the source.

However, I will go through the note mentioned by you.

2.) If I got you correctly, basically it is ok to add more than one key fields in R3ta_hints.txt file?

3.) So, irrespective of the size of the table size, it does not make sense to have more than 8 splits per table?

So, the sequence for export-import would be:

1.) Export preparation on source.

2.) Table-splitting preparation.

3.) Actual export on the source.

4.) Start Installation/Import on the target system.

Please let me know your thoughts on the same.

Regards,

Santosh