The behavior and scheduling of each data movement services component
can be configured to meet the different needs of a development, test, and
production environment. Changing the configuration of one component can have
a direct impact on the behavior of other components that are dependent on
that component.
In general, there are two dependencies:
- The Capture component periodically invokes the Source Life Cycle component.
If the Capture component is not running, no source life cycle enforcement
is performed. The delay between life cycle component invocations is configurable.
In the above figure, the Source Life Cycle
component is invoked every 3 time units, performs some activity, and then
returns control to the Capture component, which continues processing.
- The ETL component and the Target Life Cycle component are invoked by the
Apply component, after data has been successfully moved from the source database
to the target database. The ETL and the Target Life Cycle components are only
invoked when the Apply component is running.
Because the dependent components need to operate on different schedules
than the component they are dependent on, an invocation does not necessarily
result in execution. Instead, each dependent component checks its schedule
upon invocation and returns control to the calling component if it is not
yet time to perform any tasks. In the above example, ETL and Target Llife
Cycle components might only be executed twice if the schedule for both components
restricts them from being invoked more than once every 5 time units.

The ETL component (and Target
Life Cycle component) is invoked and executed at T2 (T3 respectively). The
next invocation occurs at approximately T6. Because fewer than 5 time units
have passed since they last executed, control is immediately returned to the
Apply component. Subsequent invocations at approximately T8 (T9 respectively)
result in execution because more than 5 time units have passed. Each component
is implemented by one or more component instances. You can configure each
one of the instances separately to allow for more granular control.
Anmerkung: If
changes are made, they take effect immediately, unless noted otherwise.
You can modify the default configuration for the Capture and Apply components
by changing the appropriate control tables or by overriding them using command-line
parameters in the start scripts. You can configure the ETL and life cycle
enforcement components by updating one of the control tables.
You perform the following steps to customize data movement service components
to meet the requirements of development, test, and production environments.
Configuring the (Source) Capture component instances
A Capture component instance is equivalent to a DB2 Capture replication utility. By default,
this utility is configured to continuously capture changes to the source tables
and record the changes in internal work tables. In general, you do not need
to change the default configuration for the Capture component instances.
- Identifying Capture component instances.
Multiple Capture component
instances (that is, DB2 Capture utilities) are used to capture data associated
with a
Business Measures-Modell. To determine
which Capture utilities have been assigned to provide services for a
Business Measures-Modell, you must:
- Identify the data movement service for which you want to change the Capture
utility configuration.
- Inspect the WBIRMADM.RMMETADATA metadata table in the State
database (for the State to Runtime data movement service) or in the Runtime
database (for the Runtime to Historical data movement service), and identify
all Capture utility names (column SRC_RM_CAP_SVR_NAME).
Example: The
query "SELECT OM_NAME, SRC_TAB_NAME, SERVICE_NAME, SRC_RM_CAP_SVR_NAME FROM
WBIRMADM.RMMETADATA WHERE SERVICE_NAME='State to Runtime' " may result in
the following:
In the above example, the Capture utility CAPTURE_1 has been assigned
to capture all changes made to the two source tables associated with business
measures model STEW_S in the State database.
- Changing the pruning interval of the Capture work table.
Capture
utilities automatically prune their work tables every 300 seconds (the default
for the prune_interval parameter) if auto pruning is enabled (autoprune parameter
equals "y"). Each pruning activity automatically results in an invocation
of a Source Life Cycle component instance, which is implemented by a database
trigger. Changing the pruning interval parameter for a Capture utility has
a direct impact on how often the source tables are pruned by the Source Life
Cycle component. The following illustrates how changing the pruning interval
for Capture can affect the invocation of the Source Life Cycle component instance.
Increasing the Capture instance prune_interval parameter
from 2 time units (for example, 300 seconds) to 3 time units (for example,
450 seconds) causes:
- Rows in the Capture work tables, which are eligible for removal, to remain
longer in the work table, thus increasing the potential space requirements.
Work tables will grow larger, but the system load and the risk of contingencies
can be lower.
- Rows in the source tables, which can be removed based on the life cycle
retention policy, to remain longer than expected in the source table.
In general, if the Capture prune_interval parameter is set to a value
larger than the prune_interval parameter for the life cycle component, the
Capture parameter setting takes precedence. If a Capture utility is not running
or if its auto pruning feature is disabled, no source life cycle enforcement
will be performed.
Configuring the Source Life Cycle component
Multiple life cycle component instances are being used in each source database
(State and Runtime databases). Each instance, which is implemented by a trigger,
enforces the retention policies as defined in the control table WBIRMADM.RMPRUNECTRL
located on the source database for that data movement service. Life cycle
retention policies are specified on a per table basis. Thus, one row in WBIRMADM.RMPRUNECTRL
corresponds to one table requiring pruning.
- Identifying Source Life Cycle component instances.
To determine
which triggers have been assigned to enforce retention policies for a given
Business Measures-Modell you must:
- Identify the data movement service for which you want to modify the ETL
configuration.
- Inspect the WBIRMADM.RMMETADATA table in the State database (for the State
to Runtime data movement service) or the Runtime database (for the Runtime
to Historical data movement service), and look up the associated trigger names
in column SRC_RM_PRUNE_TRG_NAME.
Example: The query "SELECT OM_NAME,
SRC_TAB_NAME, SERVICE_NAME, SRC_RM_PRUNE_TRG_NAME FROM WBIRMADM.RMMETADATA
WHERE SERVICE_NAME='State to Runtime'" may result in the following:
In this
example, two triggers (WBIRMADM.MCPruneTrig_8 and WBIRMADM.MCPruneTrig_9)
are enforcing the life cycle retention policy for the
Business Measures-Modell STEW_S
source tables in the State database. Because retention policies are defined
by table and not by the life cycle component instance names, keep track of
column SRC_TAB_NAME when planning to alter the life cycle enforcement behavior.
- Modifying Source Life Cycle component instance configurations.
- Enabling and disabling life cycle component instances:
Pruning can greatly
affect the performance of your system. When pruning is enabled, it reduce
the amount of information that transaction servers (State) and reporting servers
(Runtime) have to contend with. It also adds a small additional load on those
same tables during every invocation according to the parameters of the Life
Cycle component. When pruning is disabled, the source tables will grow over
time, which can also degrade performance.
By default, source tables
are automatically pruned according to their life cycle retention policy. To
temporarily disable pruning, modify the corresponding WBIRMADM.RMPRUNECTRL
entries: set column PRUNE_ENABLED to 1 to enable pruning, or, to disable pruning,
set any other numeric value (preferably zero).
Rows will be purged from
source table wbi.CTX_TQ4MUFT42JOT5F6R3KSDQDE2UI, but no rows will be purged
from table wbi.AI_BVSOYAP1DRWFD5HNQJR5HFQQQE if the following configuration
is used. The query: "SELECT TABLE_NAME, PRUNE_ENABLED FROM WBIRMADM.RMPRUNECTRL"
may result in the following:
- Changing the retention policy:
Retention time policies can be changed
for source tables located in the Runtime database only. For all tables located
in the State database, a retention period of 0 is enforced, regardless of
the settings in WBIRMADM.RMPRUNECTRL. A retention period is defined as the
minimum time that a row must be retained in a source table until it can be
removed, if it meets two criteria. Of the two criteria, only one is customizable
through the control table: the retention time specified in minutes. Any row
that has been marked as ready for deletion and has remained in the source
table for at least RETENTION_IN_MINUTES becomes eligible for removal.
Using
the default configuration for source tables in the Runtime database, rows
that have been marked as ready for deletion by the server need to be kept
for one day (1440 minutes) before they can be removed. The query: "SELECT
TABLE_NAME, RETENTION_IN_MINUTES FROM WBIRMADM.RMPRUNECTRL" may result in
the following:
Changes to the WBIRMADM.RMPRUNECTRL control-table entries will be picked
up each time the Source Life Cycle component is invoked.
- Scheduling source data pruning:
There is a dependency between the Capture
work table pruning interval and the Source Life Cycle component invocation.
An invocation will not result in execution if not enough time has elapsed
between Source Life Cycle component instance invocations, as shown in the
following figure.
Assuming that
the Source Life Cycle component is scheduled to execute every 4 time units,
but the Capture component is configured to prune its work tables every 2 time
units, invocation at time T4 will not result in execution.
To change
the default schedule, locate the appropriate entries in WBIRMADM.RMPRUNECTRL,
and alter the column value for PRUNE_INTERVAL, which represents the minimum
delay in minutes between executions.
Increasing the value results in less frequent
executions (but the number of invocations stays the same). Each execution
determines which source table rows are eligible for removal and removes them.
Regularly monitor your source databases to identify and eliminate potential
performance problems that are a result of locks, resulting from these deletions.
Configuring the (Target) APPLY Component
An instance of an Apply component is a DB2 Apply replication utility. Changes that
have been captured by Capture utilities are continuously applied to staging
tables in the target database by default. The default utility configuration
parameters should be sufficient for most environments and should not be changed.
- Identifying Apply component instances.
Multiple Apply component
instances (DB2 Apply
utilities) are used to apply any data changes to internal staging tables associated
with a
Business Measures-Modell. To determine
which Apply utilities have been assigned to provide services for a
Business Measures-Modell:
- Identify the data movement service for which you want to change the Apply
utility configuration
- Inspect the WBIRMADM.RMMETADATA metadata table in the Runtime database
(for the State to Runtime data movement service) or in the Historical database
(for the Runtime to Historical data movement service), and identify all Apply
utility names (column TGT_RM_APP_SVR_NAME). The query: "SELECT OM_NAME, SRC_TAB_NAME,
SERVICE_NAME, TGT_RM_APP_SVR_NAME FROM WBIRMADM.RMMETADATA WHERE SERVICE_NAME='State
to Runtime'" may result in the following:
In this example, any data changes for the Business Measures-Modell STEW_S that have been captured
in the State database will be applied to staging tables in the Runtime database
by Apply utility APPLY_4.
Each time the Apply component finishes processing all (committed) changes
that have been recorded by the Capture utility, one or more ETL component
instances and Target Life Cycle component instances are invoked.
Configuring the ETL Component
ETL components have been implemented in WebSphere Business Monitor as
database stored procedures. These stored procedures always reside on the target
database for any given data movement service. Therefore, all ETL stored procedures
assigned to the State to Runtime data movement service are located in the
Runtime database, and ETL stored procedures assigned to the Runtime to Historical
data movement service reside in the Historical database.
- Identifying ETL component instances.
Multiple ETL component
instances are set to process any data that has been added to the internal
staging tables associated with a
Business Measures-Modell.
To determine which stored procedures have been assigned to provide services
for a given
Business Measures-Modell:
- Identify the data movement service for which you want to modify the ETL
configuration.
- Inspect the WBIRMADM.RMMETADATA metadata table in the Runtime database
(for the State to Runtime data movement service) or in the Historical database
(for the Runtime to Historical data movement service), and identify all ETL
stored procedure names (column TGT_RM_SPETL_NAME). The following query:
"SELECT OM_NAME, SRC_TAB_NAME, TGT_TAB_NAME, SERVICE_NAME, TGT_RM_SPETL_NAME
FROM WBIRMADM.RMMETADATA WHERE SERVICE_NAME='State to Runtime'" may result
in the following:
In this example, any data changes for the Business Measures-Modell STEW_S
that have been captured in the State database and applied to staging tables
in the Runtime database, will be processed by stored procedures named WBIRMADM.WBIRMSP_10
and WBIRMADM.WBIRMSP_14. Successfully processed data will be stored in the
target tables (identified by column TGT_TAB_NAME) in the Runtime database.
- Modifying ETL component instance configurations.
ETL component
instance configurations are stored in the WBIRMADM.RMCONTROL control table.
Instances that have been assigned to the State to Runtime data movement service
maintain their configuration in the Runtime database; all others, in the Historical
database. Any changes that are made to a configuration will be picked up by
the stored procedures upon the next startup. Three options can be configured
through the control table:
- Minimum elapsed time between two ETL executions (ETLSCHEDMETHOD, ETL_0_MINUTES)
- Granularity of logging output (LOGLEVEL)
- Transaction durations (COMMITINTERVAL)
Each row in this table corresponds to one ETL component instance, which
corresponds to exactly one target table that needs to be populated. The following
example configuration illustrates how changes to the configuration affect
the instances behavior.
- Changing the ETL schedule.
ETL component instances are invoked
each time an Apply component instance has finished processing a subscription
set. Upon invocation, an ETL instance checks its schedule and either starts
processing or returns control immediately to the Apply component instance.
It uses information stored in control table WBIRMADM.RMCONTROL to determine
whether it needs to execute. The figure below shows the differences between
invocation and execution: the first and third time, the ETL component instance
is being executed, according to schedule. The second invocation occurred outside
the schedule and does not result in any processing activity.
Various factors determine
how often ETL component instances should run in the State to Runtime data
movement service and the Runtime to Historical Data movement service:
- Availability: How soon should data be accessible in the target tables.
Choosing a lower interval causes data to be made available earlier, but it
also increases system load.
- Data volume: The replication utilities are continuously (or as configured)
feeding data into the staging tables, whether or not it is being processed
by ETL component instances. The more database resources are used, the more
data needs to be processed. Processing data more often can reduce the maximum
resource usage.
- Processing time: ETL processing takes less time for data in the Runtime
database than processing of data in the Historical database. Plan the schedules
accordingly. Using a small delay between executions will not give better performance
if an execution lasts longer than the scheduled delay. For example, if it
takes an ETL component instance 60 seconds to finish processing, a schedule
interval of 30 seconds becomes effectively a schedule interval of at least
60 seconds because ETL component instances execute sequentially.
Two scheduling modes are currently supported:
- Changing the logging level.
Two levels of logging are supported
by the stored procedures: minimum (0) and maximum (1). To modify the default
setting of minimum, change the value of column LOGLEVEL in WBIRMADM.CONTROL
for the stored procedures (TGT_RM_SPETL_NAME), whose logging level needs to
be changed. All logging output is appended to WBIRMADM.RMLOG. The two example
stored procedures WBIRMADM.WBIRMSP_10 and WBIRMADM.WBIRMSP_14 both perform
minimal logging:
Because the log table is not automatically pruned, it needs
to be monitored regularly by the DBA. Keep logging output to a minimum unless
instructed otherwise.
- Changing transaction durations.
Data that has been successfully
processed by the stored procedure is written to the target tables immediately.
However, depending on the commit interval setting (column COMMITINTERVAL in
WBIRMADM.RMCONTROL), any updates to the target table are not made permanent
until the specified number of rows has been processed or until no more rows
are left to be processed. Increasing the value of COMMITINTERVAL (for example,
to 1500) will force the stored procedure to process more data before committing
any changes. Locks on the target table will be held longer and may have a
negative impact on other components that are trying to access the same table.
Decreasing the duration (for example, to 500) lowers the number of rows that
need to be processed before they are made available in the target table and
releases locks earlier.
Configuring the Target Life Cycle component.
ETL work tables continuously grow as long as new or updated data is applied
by Apply component instances. One Target life Cycle component instance, implemented
by a stored procedure, is assigned to one work table in each target (Runtime
and Historical) database. Each instance enforces the internal retention policies
as defined in control table WBIRMADM.RMPRUNECTRL. As in the source tables,
life cycle retention policies for ETL work tables are specified on a per table
basis. Thus, one row in WBIRMADM.RMPRUNECTRL corresponds to one table that
requires pruning.
Summary of the configuration parameters of data movement services
The following table summarizes the most commonly
used parameters provided for each of the data movement services components.
For more information about configuration parameters, refer to DB2 Replication
guide.
Component |
Parameter name |
Default values |
Valid values |
Parameter location |
Capture |
autoprune |
Y |
|
|
Capture |
prune_interval (seconds) |
300 |
|
|
Source Life Cycle |
PRUNE_ENABLED |
1 |
0 - Disabled
1 - Enabled
|
Data movement service Source DB: WBIRMADM.RMPRUNECTRL
|
Source Life Cycle |
RETENTION_IN_MINUTES |
0 - State to Runtime
1440 - Runtime to Historical
|
0 to DB2 limit for BIGINT |
Data movement service Source DB: WBIRMADM.RMPRUNECTRL
|
Source Life Cycle |
PRUNE_INTERVAL (minutes) |
5 |
0 to DB2 limit for BIGINT |
Data movement service Source DB: WBIRMADM.RMPRUNECTRL
|
ETL |
ETLSCHEDMETHOD |
1 |
0 - Flexible scheduling
1 - Strict interval
scheduling
Other - Disables ETL
|
Data movement service Target DB: WBIRMADM.RMCONTROL
|
ETL |
ETL_0_MINUTES |
5 - State to Runtime
1440 - Runtime to Historical
|
0 to DB2 limit for INTEGER |
Data movement service Target DB: WBIRMADM.RMCONTROL
|
ETL |
LOGLEVEL |
0 |
0 - For normal logging
1 - For trace logging
|
Data movement service Target DB: WBIRMADM.RMCONTROL
|
ETL |
COMMITINTERVAL (number of records.) |
1000 |
0 - Disable commits until the end
1 - Commit
every record.
n - DB2 Limit for BIGINT
|
Data movement service Target DB: WBIRMADM.RMCONTROL
|
Target Life Cycle |
PRUNE_ENABLED |
1 |
0 - Disabled
1 - Enabled
|
Data movement service Target DB: WBIRMADM.RMPRUNECTRL
|
Target Life Cycle |
RETENTION_IN_MINUTES |
0 |
0 to DB2 limit for BIGINT |
Data movement service Target DB: WBIRMADM.RMPRUNECTRL
|
Target Life Cycle |
PRUNE_INTERVAL (minutes) |
1440 |
0 to DB2 limit for BIGINT |
Data movement service Target DB: WBIRMADM.RMPRUNECTRL
|
Anmerkung: IBM reserves
the right to make changes to the database tables and columns referenced above.
As such, some tables and columns may be altered, removed, or added from release
to release. Any reliance on the content or structure referenced in the information
from release to release is done at the consumer's own risk. IBM will document
any such changes as they occur.