Use Case
Use Case: Filter Initial Load
You don't want to load the entire source table but a filter on the SLT is not the optimal solution as most of the data is transferred to the SLT system and skipped here. In case you filter a significant part of a table it's better to already limit the reader module of the initial load accordingly
Ensure that you use a reading type 4/5. Without it will not work.
Use Case: Parallelize Replication
You need to parallelize the replication of a table as there are high change rates and one parallel task can not catch up delta data within expected latency time.
Use Case: Parallelize Initial Load
In order to parallelize the initial load (more precisely,the access plan calculation which is required before the real data transfer can be done - the actual transfer will be automatically parallelized), there is another option availabe, which automatically determines the subsets to be processed by each of the parallel-running jobs. For details, see http://scn.sap.com/community/replication-server/blog/2013/09/26/how-to-improve-the-initial-load-by-r...
However, if you can define such subsets yourself, this might be more efficient, firstly because you save the first step to determine the subsets, and secondly because the access might be more efficient, if you define the subsets by only giving values and / or intervals for the first key fields rather than for all the key fields of the table.
Process
With DMIS 2011 SP6 a new table is available where Filter for the Initial Load and / or settings to parallelize the replication can be defined. The eintries in table DMC_ACSPL_SELECT have to be defined on the SLT systems
Fieldname | Description |
|---|
MT_ID | Mass Transfer ID |
MIGR_OBJ_ALIAS | Table Name
Attention: in case of cluster tables, you need to specify:
- the name of the logical table(s) in case of the initial load
- the name of the physical table in case of the replication
So you need to maintain two records if the setting should be valid
for both initial load and replication in case of clusters! |
VALIDITY | Validity of this setting
1 = use for initial load and replication (filter load and parallelize replication)
2 = use for initial load only (filter load)
3 = use for replication only (parallelize replication) |
ACS_PLAN_ID | Access Plan ID
Use ID 00000 for filter criteria of initial load and / or replication
use access plan ID 00001 - 99999 for parallelization settings |
FIELD | Fieldname |
CURRNUM | Line Number (0001 - 9999) to define multiple records for the same access plan ID |
LOGICAL_OPERATOR | Logical Operator (allowed in Open SQL where clause) |
VALUE | Filter / parallelization value for current field |
GATING_OPERATOR | required if several records for the same field are defined
multiple fields are concatenated with AND automatically |
The value has to be defined properly based on the internal format of the corresponding data type. Character-like field values have to be defined with single quotes. NUMC fields have to be defined with single quotes and leading zeros.
Process: Filter Initial Load & Replication
You can enter filter criteria for any field of the source table. If you enter mutliple filter criteria for one field you have to define a gating operator (AND / OR). If you define filter on multiple fields the filter from the indiviual fields will be concatenated with gating operator AND.
If you define a filter for the replication also a filter on the DB trigger is recommended as otherwise the changes that don't fit to the filter criteria will not be handled and remain in the logging table.
Example 1: Filter on one field for a single value
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where GJAHR is greater or equal 2013. The filter criteria shall be relevant for initial load and replication
MT_ID | MIGR_OBJ_ALIAS | VALIDITY | ACS_PLAN_ID | FIELD | CURRNUM | LOGICAL_OPERATOR | VALUE | GATING_OPERATOR |
|---|
001 | BKPF | 1 | 00000 | GJAHR | 0001 | >= | 2013 | |
The following where clause will be used for the select:
SELECT FROM BKPF WHERE GJAHR >= 2013.
|
However, this won't work for the initial load if you are using reading type 3 ("primary key order"). Also it does not work for replication unless you also parallelize the replication (see below: "Parallelize Replication"). Note that filters for replication should rather be set using corresponding trigger filters.
Example 2: Filter on one fieldfor mutliple values
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where BUKRS is equal 0010 or between 1000 and 3000. The filter criteria shall be relevant for the initial load only
MT_ID | MIGR_OBJ_ALIAS | VALIDITY | ACS_PLAN_ID | FIELD | CURRNUM | LOGICAL_OPERATOR | VALUE | GATING_OPERATOR |
|---|
001 | BKPF | 2 | 00000 | BUKRS | 0001 | = | '0010' | OR |
001 | BKPF | 2 | 00000 | BUKRS | 0002 | >= | '1000' | AND |
001 | BKPF | 2 | 00000 | BUKRS | 0003 | <= | '3000' | |
The following where clause will be used for the select:
SELECT FROM BKPF WHERE BUKRS = '0010' OR BUKRS >= '1000' AND BUKRS <= '3000'.
|
Example 3: Filter on multiple fields
Define filter criteria for table BKPF in mass transfer ID 001. Only those records should be loaded where GKAHR is greater or equal 2013 and where BUKRS is equal 0010 or between 1000 and 3000. The filter criteria shall be relevant for the initial load only
MT_ID | MIGR_OBJ_ALIAS | VALIDITY | ACS_PLAN_ID | FIELD | CURRNUM | LOGICAL_OPERATOR | VALUE | GATING_OPERATOR |
|---|
001 | BKPF | 2 | 00000 | GJAHR | 0001 | >= | 2013 | |
001 | BKPF | 2 | 00000 | BUKRS | 0002 | = | '0010' | OR |
001 | BKPF | 2 | 00000 | BUKRS | 0003 | >= | '1000' | AND |
001 | BKPF | 2 | 00000 | BUKRS | 0004 | <= | '3000' | |
The following where clause will be used for the select:
SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS = '0010' OR BUKRS >= '1000' AND BUKRS <= '3000'.
|
Process: Parallelize Replication
You can enter ranges to split the replication into parallel tasks. The number of defined ranges also defines the maximum parallel processing of the current table. How many jobs will finally procssing a table is defined by the overall number of jobs and the maximum number of parllel jobs defined for a table (can be defined in LTRC -> Data Transfer Monitor). If you define range criteria for the parallelization of the replication you need to define disjount subsets of the data to be processed by each of the replication jobs.
Example 1: Parallelize Replication
Split replication of table BKPF in mass transfer ID 001 into 4 ranges. The ranges are defined based on field:
Range 1: 0000 -> 2000
Range 2: 2000 -> 5000
Range 3: 5000 -> 8000
Range 4: 8000 -> 9999
MT_ID | MIGR_OBJ_ALIAS | VALIDITY | ACS_PLAN_ID | FIELD | CURRNUM | LOGICAL_OPERATOR | VALUE | GATING_OPERATOR |
|---|
001 | BKPF | 3 | 00001 | BUKRS | 0001 | <= | '2000' | |
001 | BKPF | 3 | 00002 | BUKRS | 0001 | > | '2000' | AND |
001 | BKPF | 3 | 00002 | BUKRS | 0002 | <= | '5000' | |
001 | BKPF | 3 | 00003 | BUKRS | 0001 | > | '5000' | AND |
001 | BKPF | 3 | 00003 | BUKRS | 0002 | <= | '8000' | |
001 | BKPF | 3 | 00004 | BUKRS | 0001 | > | '8000' | |
The following where clause will be used for the select:
Range 1:
SELECT FROM BKPF WHERE BUKRS <= '2000'.
Range 2:
SELECT FROM BKPF WHERE BUKRS > '2000' AND BUKRS <= '5000'.
Range 3:
SELECT FROM BKPF WHERE BUKRS > '5000' AND BUKRS <= '8000'.
Range 4:
SELECT FROM BKPF WHERE BUKRS > '8000'.
Example 2: Parallelize Replication & Filter
Split replication of table BKPF in mass transfer ID 001 into 4 ranges as defined in example 1. In addition only those records should be loaded where GJAHR is greater or equal 2013.
MT_ID | MIGR_OBJ_ALIAS | VALIDITY | ACS_PLAN_ID | FIELD | CURRNUM | LOGICAL_OPERATOR | VALUE | GATING_OPERATOR |
|---|
001 | BKPF | 1 | 00000 | GJAHR | 0001 | >= | 2013 | |
001 | BKPF | 3 | 00001 | BUKRS | 0001 | <= | '2000' | |
001 | BKPF | 3 | 00002 | BUKRS | 0001 | > | '2000' | AND |
001 | BKPF | 3 | 00002 | BUKRS | 0002 | <= | '5000' | |
001 | BKPF | 3 | 00003 | BUKRS | 0001 | > | '5000' | AND |
001 | BKPF | 3 | 00003 | BUKRS | 0002 | <= | '8000' | |
001 | BKPF | 3 | 00004 | BUKRS | 0001 | > | '8000' | |
The following where clause will be used for the select:
Range 1:
SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS <= '2000'.
Range 2:
SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '2000' AND BUKRS <= '5000'.
Range 3:
SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '5000' AND BUKRS <= '8000'.
Range 4:
SELECT FROM BKPF WHERE GJAHR >= 2013 AND BUKRS > '8000'.
Enjoy this new features :smile:
Best,
Tobias
