Advanced Run Options

Advanced Run Options provide you with additional options for resuming and restarting tasks.

To use Advanced Run Options, click the triangle next to the Run button and select Advanced Run Options.

The Advanced Run Options dialog box opens.

The Advanced Run Options dialog box lets you do the following:

• **Restart task and start processing changes from current time: This starts the Apply Changes replication task from the beginning (as if the task has not run before).

  **Only available for Apply Changes replication tasks.

• Tables are already loaded. Start processing changes from:

  Information noteMetadata changes performed on the source tables while a task is stopped (for example, DROP COLUMN) will not be captured when the task is resumed from an earlier time or position (SCN/LSN). In such a case, the metadata that exists when the task is resumed will be captured.

  • Date and Time: Select the date and time from where you want to Replicate to start processing changes.

    Information note

     

    • When logs are deleted from the database (e.g. due to a purge policy), a log matching the specified date and time may not exist. In this case, Replicate will resume the task from the earliest point possible, after the specified date and time.
    • With the IBM DB2 for LUW source endpoint, this option cannot be used to start Apply Changes tasks from a point before the original start time of the Apply Changes task. Or, to put it another way, it can only be used to start tasks from any time after the original start time of the Apply Changes task.
    • The timestamp uses the local time of the browser machine.
    • This option is not relevant for the File Source endpoint.

  • Source change position (e.g. SCN or LSN): Specify the position in the log from where to resume change processing. The source change position format differs according to your source endpoint. For more information, see How to Find the Source Change Position.

    Information note

    The Source change position option is supported with the following source endpoints only:

    • Oracle
    • Microsoft SQL Server
    • MySQL
    • PostgreSQL
    • IBM DB2 for z/OS
    • IBM Informix
    • IBM DB2 for LUW

Metadata Only:

The "Metadata only" options described below allow you to:

• Create empty tables on the target and then manually edit them.
• Create tables during a task.

Enabling the options will also ensure that supplemental logging is set up correctly on the source tables before starting the actual replication task.

• Recreate all tables and stop: Select this option to recreate the target tables as defined in the Full Load Settings tab. When "Store Changes" is enabled, the Change tables/Audit table will be created as defined in the Store Changes Settings tab. To use this option, stop the existing task, run the task with this option enabled (the task will stop automatically) and finally, resume the task.

• Create missing tables and stop: Select this option to create missing target tables including Change Tables. You can use this option to create Change Tables on the target after enabling the "Store Changes" option (in the Store Changes Settings tab) for an existing task. To use this option, stop the existing task, run the task with this option enabled (the task will stop automatically) and finally, resume the task.

The table below shows which tables are created in the target database when the Metadata only option is enabled for a unidirectional task. As the table shows, when certain task options are enabled, Control tables and the Audit table will not be created on the target.

Recovery:

• Recover using locally stored checkpoint: Use this option if recovery is not possible using the Resume Processing or Start process changes from options (due to corrupt swap files, for example). When this option is selected, Replicate uses the checkpoint data stored in <Data_Folder_Path>\data\tasks\<task_name>\StateManager to recover the task.

  Information note

  When using this option, the following limitations apply:

  • The following source endpoints are supported only:
    • Oracle
    • Microsoft SQL Server

    • MySQL

    • PostgreSQL

    • IBM DB2 for z/OS

    • SAP HANA

  • Tasks can only be recovered during Change Processing (i.e. after Full Load Completes)
  • With the exception of the File Channel endpoint, all target endpoints are supported. The following limitations apply:
    • In Transactional apply Change Processing mode: All target endpoints that support transactions are supported.
    • In Batch optimized apply Change Processing mode: Oracle target endpoint only is supported. Also requires the Preserve transactional integrity option to be enabled.
    • For all other target endpoints or Change Processing modes, recovery is supported, but may cause duplicates on the target.

• Recover using checkpoint stored on target: Select to recover a task using the CHECKPOINT value from the attrep_txn_state table (created in the target database).

  Information note

  When using this option, the following limitations apply:

  • Only the following source and target endpoints are supported:
    • Oracle
    • Microsoft SQL Server
  • Tasks can only be recovered during Change Processing (i.e. after Full Load Completes)

  • The task Change Processing mode must be set to either:

    Batch optimized apply with the Preserve transactional integrity option enabled. Note that this mode is only supported with the Oracle target endpoint.

    OR

    Transactional apply

    For information about setting the Change Processing mode, see Changes Processing Tuning.

  This option will only be available if the Store task recovery data in target database option was enabled in the Task Settings' Change Processing Tuning tab before Change Processing completed.

  Select this option (as opposed to the Recover using locally stored checkpoint option) if the files in the Data folder are corrupted or if the storage device containing the Data folder has failed.

  For a detailed explanation of how to set up and implement recovery using the attrep_txn_state table, see Recovering from data folder loss or corruption.

How to Find the Source Change Position

The following topic explains how to locate the source change position for each of the supported databases.

MySQL

OPTION 1:

1. Execute:

   SHOW BINARY LOGS;

2. Choose a binlog file (e.g. log.000123).

3. Execute:

   SHOW BINLOG EVENTS IN 'binlog_filename';

   Example:

   SHOW BINLOG EVENTS IN 'log.000123';

4. Locate the position of a BEGIN or DDL statement (e.g. 1777888).

   Information note

   MySQL does not support a "start from position" located in the middle of a transaction, which is why you must select either BEGIN or DDL.

5. Set the internal parameter:

   StartFromPosition = binlog_filename:begin_or_ddl_position

   Example:

   StartFromPosition = log.000123:1777888

OPTION 2:

1. Execute:

   SHOW MASTER STATUS;

2. Set the "Start from position" as:

   firstColumnValue:secondColumnValue

   Example:

   mysql.007472:775

Microsoft SQL Server

Execute:

SELECT MAX([Current LSN]) FROM fn_dblog(null,null);

Example: 00002717:00000e08:0003

Oracle

Execute:

SELECT current_scn FROM V$DATABASE;

Example: 1471212002

PostgreSQL

Note that the command differs according to your PostgreSQL version.

PostgreSQL 9.6

Execute:

SELECT * FROM pg_current_xlog_location();

PostgreSQL 10 and later

Execute:

SELECT * FROM pg_current_wal_lsn();

IBM DB2 for z/OS

Specify the LSN, which is the RBA in a non data sharing environment, and the LRSN in a data sharing environment

IBM DB2 for LUW

1. Execute db2pd to find the correct log name.

2. Execute db2flsn with the returned log name and use the current LRI value.

   See also: Resuming or starting a task from LRI in a pureScale environment