export/import using Distribution Monitor

Optimize export/import using Distribution Monitor

1. Introduction to Distribution Monitor 
2. Basis of optimization

1. Introduction to Distribution Monitor 

Distribution Monitor is one of the most preferred tools to perform export/import for Unicode conversion or OSDB migration for large database with very limited downtime window. You should consider using Distribution Monitor, when you do not have options like “zero downtime migration”, service offered  by SAP or proprietary storage migration tools from storage vendor like EMC. The biggest advantage of using Distribution Monitor is parallel export/import which will reduce your total export/import time drastically and transfer the load from source/target to external node, so that export/import run can use the processing power of external systems (called node or Application Server). However configuration of Distribution Monitor takes a lot of time and effort. Distribution Monitor itself is not an executable or binary, it is consisting of set of scripts and parameter files; which is internally based on SAP tools – R3load, R3szchk, R3ldctl and R3ta. You can download Distribution Monitor from SMP. In the following section I will explain how Distribution Monitor works:

While configuring Distribution Monitor, you will configure more than one “Nodes”, also called “Application Server”. In this example – I am showing four nodes. Each node has been configured with some local storage or SAN storage attached to the node, both export and import process has read/write access to this storage location. Typically, export process in each node will dump (write) data from source system  into the local storage of the node and import process will read data and write back to Target system database.  Parallel export/import processes will run simultaneously across all four nodes and each node will handle different set of “packages” or tables.

2. Basis of optimization

In the next section, I will explain the basic technique to optimize export/import using Distribution Monitor; the first question is what to distribute?  In a simple language, you can say – distribute load among various nodes.

Starting point of load distribution is the list of biggest database tables and SAP packages. So find out the list of first biggest ( say hundred) tables from the database. For very large (SAP) database, it is a general trend that first 50 -100 biggest tables represent 60 – 80 % size of the total database. In the following example.  I found 70 biggest tables (with total size of 2.5 TB ) represent 84% of the total database ( 3.0 TB) size.

Next question will be,  how to distribute these tables and SAP standard packages. You need to gather all facts and figures. I have outlined my finding as follows:

a)    There are  four nodes available, so my target is to distribute approximately 750 GB load per node (as my total DB size is 3.0 TB = 4 x 750 GB).

b)    The biggest 18 tables representing the half of the database size will influence the entire export/import.

c)    Split all tables more than 20 GB size. In this example, there are 18 transaction tables and 3 cluster tables – bigger than 20 GB size. So each split chunk will be max 20 GB of size.

d)    There are 52 tables bigger than 2 GB and less than 20 GB  size and their total size is  750 GB.

e)    List down all SAP standard packages and their sizes (excluding, 70 biggest tables from step b and d).

Summary of the distribution:

Based on all facts and figures, I have the following distribution :

i.         Distribute the biggest 18 tables between first two nodes ( Node1 and Node2) – split all tables with max chunk size of 20 GB, use incremental index creation option for each chunk.

ii.        All tables smaller than 20 GB and bigger than 2 GB, put into “Node-3” . These are non-split tables and use “loadprocedure fast” option during import. This is a faster way of importing data then usual insert statement.

iii.       Do not mix up spilt tables and non-split tables, as most of the database does not support incremental index creation for non-split tables, moreover direct load option using “loadprocedure fast”  is different for split and non-split tables for most of the databases.

iv.       Direct load using “loadprocedure fast LOAD” has some overheads. It needs to perform some pre and post work. If you use direct load option for all standard SAP packages, consisting of all other tables (except 18 split tables + 52 direct load tables), it will result in large amount of overheads. To avoid this situation, do not use direct load option “loadprocedure fast LOAD” for standard packages. So put all SAP standard packages into a single node ( Node 4 ) and import will perform through insert operation.

v.          Along with the SAP standard package on “Node -4”, put all split cluster tables as most of the database does not support direct load import for cluster tables.

I have given you a particular example with some basic guide lines. Every migration is different, so gather all facts and figures to set your distribution accordingly. I will publish the “Part -II : export/import configuration using Distribution Monitor”.

1. Architecture of Distribution Monitor ( Files and Directory structures)

Distribution Monitor itself is not an executable or binary, it is consisting of set of scripts and parameter files; which is internally based on SAP tools – R3load, R3szchk, R3ldctl and R3ta. When you UNCAR “Distribution.SAR” file, it contains some scripts and configurable files with default configuration. I will not cover the configuration part in this blog, I will explain more about file and directory structures – which is the backbone of the Distribution Monitor.

For overview of Distribution Monitor, please check the following SCN blog :

http://scn.sap.com/docs/DOC-34138

I am going to use the same example as I have mentioned in part-I blog, that means I will use four nodes for optimization.

Fig1 : Distribution Monitor with four nodes

I will explain all important files and directory structures in this blog.

Directory

Uses

Common directory

/<path>/commDir Common directory or common file system is mounted to both source, target and to all nodes so that each of these systems can perform read/write activities to common directory. It can be local to source or target system or NAS file system. Common Directory contains various sub directories and it is important to understand each of these sub-directories. Following are the important sub-directories : R3ldctl : It contains *.STR files for all SAP standard packages and *.TPL ( database specific template) files. R3ta : *.WHR (where files for all split tables– condition for splitting  ) R3szchk : *.EXT files (computes space requirements for tables/indexes) for all standard SAP packages. ExportCtrl : Control file is created while export is running for a package or table split. EXT4Split : *.EXT files for split tables. ImportCtrl : Control file is created while running import for a standard package or table spilt Info : Contains export / import property files and distribution.txt file – that determine distribution of tables and packages amongst various nodes. PkgSplit : It contains *.STR files for all split tables and non-split tables those are moving out of standard packages. SapinstImport : You need to specify as “export directory” in SAPINST. It contains LABEL.ASC file and ‘DB’ and “Data’ directory. Sapview : *.STR file for views STR4Split : It contains *.STR files for all split tables Host1 :  Each host-directory contains a lot of information for export and import run. It has the following sub-directories :   netExchangeDir – Control file for parallel export/import   exportInstallDirCopy – Copy of exportInstallation directory   importInstallDirCopy – Copy of importInstallation directory    Files : ddl mapping files for export and import. Host2 ……… Host4 ( a sub-directory for each node  by its hostname ). Sub-directory structure under common file system is generated by Distribution Monitor’s “preparation run”. Export/import process read and generates various info into these sub-directories.

Fig2 : commDir directory structure

Directory

Uses

Local directory structure on each node

File and directory structure on each Distribution Monitor node : Export Directory : Local export directory for each Distribution Monitor node. Export directory size should be calculated as { 10 GB + ( 1/10 * total size of the data distribution for the node )}. Export Installation Directory :  Log files for export run. Import Installation Directory :  Log files for import run. Work Directory : Distribution Monitor executable directory for each node. Best practice is to create the work directory under - /usr/sap/<SID> file system.

Example of Local directory structure:

|-/export/node1         (export directory)

|-/usr/sap/<SID>|-DMWKDir      ( Work directory)

                            |-InstDirExp     (Export Installation directory )

                            |-InstDirImp    (Import Installation directory )

Content of Work directory ( or executable directory ).

Fig3 : Content of Work Directory

Configuration of Distribution Monitor centers around configuration of work directory’s configuration files