Splitting of tables in Unicode conversion

[Former Member]

I have few questions, mentioned below, regarding splitting of tables in export/ import of large several terabyte database.

1- How do you create a file for split predefined tables

2- Can anyone provide a sample split file for predefined tables

3- How do you make export/import faster

4- What should the package unload order, be defined as?

5- What is different between package splitter and distribution monitor?

Points to be awarded for any kind of help.

Thanks

[Former Member]

In the sapinst, there is the option SPLIT, so you can use it with the default values.

After this, you can do other tests trying to modify the values of the SPLIT.

In the DB02 you can see the biggest transactions.

Otherwise you can use the distribution monitor/migration monitor if you want to do the export/import in paralell.

Regards,

Alfredo.

[Former Member]

Hi,

Run DBstats before unicode process from db13 this will increse the speed of the export/import process.

Get the latest R3ldctl, R3load, R3szchk ,Tp & R3trans.

The package order can be defined as alphabetic or depend on size.

Thanks,

Subhash

Edited by: subhash gadde on May 18, 2008 8:48 PM

[Former Member]

Can I define the packages as A* (for all tables beggining with A) to C* (For all tables begginning with C) in SPUMG?

[Former Member]

Hi Abdul,

at first I would like to answer your second question, because it is more easy to answer. Yes you can run SPUMG in packages.

Now I try to answer your other questions:

to 1) You may use R3ta to create Where files (*.WHR) necessary to split tables. You have to run R3ta for all tables you would like to split. Look into the documentation of R3ta for (or call it with -h) to check for options. You may also create the WHR files manually, but this could be difficult because you have to choose the where conditions to result in disjoint sets which have to have the original table as the union of all sets.

to 2) Just run R3ta for a small table

to 3) This is a very complex problem. You could run the export without sorting if the target database is not MAXDB (and not SQL Server - this may change). This saves usually lots of time exporting data. Then you should run the export and import as parallel as possible. Usually 2 export and import jobs per CPU are used. I would reduce the number of jobs to 1.5 if you run the R3load processes on the target database server. The next thing is to run export and import in parallel. You could also run the export and import at the same time if you're really in a hurry. But this feature implies a stable network because you have to restart the export of a table in case of a problem (this could happen after some hours, which is not really funny - it could also happen when you export to disk, but in this case only one system needs to be available compared to two systems plus the network).

to 4) The package order should be chosen by the duration of export / import. You should start those packages first which took most time for export and import together. You may supply only the most important packages in your configuration files, Migmon (or Distmon) will choose the other packages in alphabetical order by default (afaik).

to 5) They have nothing in common. Package splitter just splits bigger packages (STR files) into smaller ones using size informations in the EXT files and command line options. Check documentation. Distmon is a tool to spread R3load processes on multiple application servers to speed up the export / import process.

Best regards

Ralph Ganszky

[Former Member]

The SPLIT is a very good solution if you select the biggest tables.

Regards,

Alfredo.

[Former Member]

Thanks for the answers. Based on them, I have following queries.

1- On what basis, should I use the option FASTLOAD, to make make export/import faster ? ( I mean should I use alphabetical order of it should be based on sizes)

2a- If I use alphabetical order, will be sort based on size.

Or

2b- If I want the sorting based on sizes, I should use another option?

3a- Also please let me know whether I can start MIGMON through SAPINST. (or)

3b- MIGMON and SAPINST should be started seperately.

4a- Should I start MIGMON, after all settings in SAPINST have been placed & configured and SAPINST is about to begin parallel exp/imp on source & target system at the same time?

4b- Can I start MIGMON, before SAPINST? Is there any advantage?

Points to be rewarded for any kind of help.

[Former Member]

Hi Alfredo,

I would disagree and would state, that SPLIT is good for the most time consuming tables. This are probably big tables, but not necessarily the biggest tables.

Regards

Ralph

[Former Member]

Hi Abdul,

to 1) FASTLOAD is only available for import. But you could use is in any case, it will be faster or at the same speed as without the option. On DB6 R3load decides on table structure and table size if it is used. On Oracle I don't know. If you are unsure, test it in a test migration.

to 2) You should export the slowest tables first, therefore you need a file with the slowest package names in it. Those tables should be sorted by runtime of the test migration, slowest first. All other packages will be exported in alphabetical order if you use the file order option. For import I would use the order by size option because if I don't use the file order option again.

to 3) SAPinst normally starts MIGMON internally. But you could stop SAPinst before it starts MIGMON to start it manually with special options.This would be the recommended scenario for big Migrations. This is also described in the DISTMON documentation, if I'm not wrong.

to 4) same as to 3)

Regards

Ralph

[Former Member]

OK,..........

MIGMON using "Paralell export and import" using the user in the same domain

Regards,

Alfredo.

[Former Member]

Thanks Ralph for your advices.

As per your statement "For import I would use the order by size option", where do I have to provide this option.

1- Is there a seperate parameter in the file "import_monitor_cmd.properties". If there is, what is the parameter settings.

2- Is there a parameter in keydb.xml to set the order by size option for import monitor.

3- Is there a parameter in control.xml to set the order by size option?

Thanks once again everyone for the help.

[Former Member]

Hi Abdul,

yes the order by size option could be configured in the file import_monitor_cmd.properties. The parameter is called orderBy and it should be set to "size".

Regards

Ralph

[Former Member]

Hello,

Please let me know, if the following statements are true.

1- Table splitting preparation (R3ta) -> is used for splitting small tables (which generates WHR files).

2- Database and Central Instance export (specify input file) -> This input file is used for splitting large tables (which generates splitted STR files). Example: A large table CDCLS if specified as CDCLS%2 will have CDCLS1.STR and CDCLS2.STR ( two split files for 2 R3load processes).

The .WHR (created in step 1 for small tables) and .STR files (created in step 2 for large tables) are residing in the DATA directory.

3- How do I include the .WHR files, in my order_by file clause. What is the file where I should specifiy this parameter. Is it export_monitor_cmd.properties or is it import_monitor_cmd.properties?

4- What is the file R3ta_hints.txt used for (to be used in step 1, seen above).

5- How do I create temporary indexes on tables to be splitted during unicode conversion/ exp & imp processes.

I very much appreciate your responses.

Thanks,

Abdul

[Former Member]

Hi Abdul,

to 1-)

No R3ta is not for small tables, it is for big tables or at least for tables which took very long to export. I mentioned small tables only for the case, that you want to try the usage of R3ta. Small tables makes sense therefore, because it could run hours on huge tables.

to 2-)

You can only export the database content, there is no content of the central instance. It consists only of the application programs which needs the database content to work with. STR files gets generated by R3ldctl and you could split them with SPLIT tool. But the smallest chunk is a single table. Tables will be splitted into smaller pieces by using WHR files. And yes you're wright. STR and WHR files reside in DATA directory.

to 3-)

The where files could be also splitted. You could place your table chunks into the order by file by using CDCLS-1, CDCLS-2 and so forth into the file.

to 4-)

I don't know for sure, but it looks like that it contains the preferred columns for some well known tables.

to 5-)

R3ta create STR files which could be used for import in the source system. When I'm remember well.

That regarding your questions. But I would recommend that you either take the training class about Migrations and get certified or try to get someone certified onsite to help you with your project. It will help much more than SDN could help you.

Best regards

Ralph

Edited by: Ralph Ganszky on Jul 24, 2008 11:18 PM

[Former Member]

Thanks Ralph for the update.

I have the last queries. to 1-)

Since R3ta runs for several hours on huge tables. In our environment, the R3ta utility ran for 26 hours on tables CDCLS and EDI40.

1- What is the possible way to minimize/ reduce the runtime of R3ta from 26 hours to few hours on large/huge tables with millions of records?

I assume, by default database analysis searches all indexes to select the optimal one.

2- Does the hints directory allow us to tell it to only use a single primary index.

Also During splitting of tables with R3ta, I identified hidden split hints directory.

3- Is there any way to force analysis to only use 1 index

I appreciate your response.

Thanks,

Abdul.

[Former Member]

Hi Abdul,

to 1-)

I think it is not possible to shorten the runtime dramatically because R3ta reads the table several times. You may achieve shorter runtime by investing into new hardware, but that probably not what you want to hear.

to 2- & 3-)

I think R3ta does not work on indexes, it tries to find a suitable where clause to split the table in equally sized chunks. If an index will be necessary it creates it, but it does not read all indexes afaik. If you look into R3ta_hints.txt file you also find column names instead of index names.

But I'm not an expert on R3ta, I used it once and for me it does its job not good enough. Therefore I've created my WHR files manually. But creating WHR files manually is a very complex task which goes far beyond a forums message. It is very easy to make mistakes which could lead to data loss. R3ta is not perfect, but if you don't want to take all responsibility for data consistency, you better use it.

Kind regards

Ralph

[Former Member]

Thanks Ralph for your excellent feedback.

[Former Member]

Hi Abdul,

It is better you go for data archiving and table reorganization of large tables rather than depending too much on R3ta.

[Former Member]

Hi,

Use R3ta_hint.txt file for split predefined tables

You have a option FASTLOAD make use of this optiion to make export/import faster

It depends on size and aphabetical order

if you need more clarity post message

Regards

Prithviraj Yadav

[Former Member]

Hi Abdul,

I suggest you to use table splitting with appropraite where clause with the intelligent combination of columns. This needs a lot of reasearch on your data to identify which columns needs to be considered in where clause (Fields in primary key are manadatory) tehn create the index on the columns you used in where clause which improves the export performance. But needs a lot of research. For more information you use Distribution monitor (Use as many systems as possible) with the help of PAckage and table splitting.

Thanks,

Kiran.

[Former Member]

Hi Kiran,

Please let me know if the following points are true.

Identify which columns needs to be considered in where clause

1- What are the steps involved in identifying and then creating where clauses for specific columns?

2- Is there any document or steps available to investigate the columns for those tables involved?

This needs a lot of reasearch on your data to identify which columns needs to be considered in where clause (Fields in primary key are manadatory)

3- If a table has 5 columns (1st column contains primary key) and 3 column is being selected in the where clause, then it would become mandatory to have 1 & 3 column in the where clause (since 1st column contains primary key and where clause on it becomes mandatory).

I suggest you to use table splitting with appropraite where clause with the intelligent combination of columns.

4- Can I do table splitting with R3ta on db host and different apps servers (both at the same time)?

Then create the index on the columns you used in where clause which improves the export performance.

5- How do I create the index on those above selected columns (1st primary key column and 3 column). Is it the same syntax used in Oracle SQL "create index <index_name> on <table_name>(<column_name>)?

But needs a lot of research.

6- Are there any links or docs available for this research?

7- What are the ( structured ) steps recommended, to be followed, for doing the research?

For more information you use Distribution monitor (Use as many systems as possible) with the help of PAckage and table splitting.

8- Can I use distribution monitor in parallel with table splitting (through R3ta), with both at the same time?

9- Can I use distribution monitor in parallel with package splitting, with both happening at the same time?

It is better you go for data archiving and table reorganization of large tables rather than depending too much on R3ta

10- I understand data archiving. But what is the best practice to be followed for table reorganization of large tables? (Should I use BR tools or is there any other specific recommended procedure).

I very much appreciate your response.

Thanks,

Abdul

Edited by: abdul haleem on Aug 4, 2008 11:28 PM

Edited by: abdul haleem on Aug 4, 2008 11:29 PM

[Former Member]

I would appreciate if anyone answers, to the above 10 queries.

Thanks once again in advance.

Regards,

Abdul